If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula referencing a changing range??
I have a workbook that has several tabs. I'm only working with 2 for the
problem that I'm having though. The first tab, which holds all my data, is called Marketing Letters. The 2nd tab is called Status. On the Status tab, I have a cell (A5) which looks to another cell (A1) on the same tab, then returns matching values (in multiple rows) from the Marketing Letters tab. Here is my formula: =INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) This works great, however...I am constantly adding data to the Marketing Letters tab. Currently there's 300 rows, but tomorrow it could be 305 or by the end of the week could be 350. Rather than changing my formula on the status tab each time to reflect how many rows have data on the Marketing Letters tab, is there a way to have it automatically determine the number of rows with data? I tried using the $B:$V, but my formula didn't work with that. Thanks for your help! |
#2
|
|||
|
|||
Formula referencing a changing range??
=INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing
Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) Create a dynamic range. Assuming the data is entered in a contiguous block. Goto the menu InsertNameDefine Name: Data Refers to: ='Marketing Letters'!$B$1:INDEX('Marketing Letters'!$B:$B,COUNTA('Marketing Letters'!$B:$B)) OK out Then your formula becomes (array entered): =INDEX('Marketing Letters'!C:C,SMALL(IF(Data=$A$1,ROW(Data)),ROWS(A$ 1:A1))) -- Biff Microsoft Excel MVP "Stacie2410" wrote in message ... I have a workbook that has several tabs. I'm only working with 2 for the problem that I'm having though. The first tab, which holds all my data, is called Marketing Letters. The 2nd tab is called Status. On the Status tab, I have a cell (A5) which looks to another cell (A1) on the same tab, then returns matching values (in multiple rows) from the Marketing Letters tab. Here is my formula: =INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) This works great, however...I am constantly adding data to the Marketing Letters tab. Currently there's 300 rows, but tomorrow it could be 305 or by the end of the week could be 350. Rather than changing my formula on the status tab each time to reflect how many rows have data on the Marketing Letters tab, is there a way to have it automatically determine the number of rows with data? I tried using the $B:$V, but my formula didn't work with that. Thanks for your help! |
#3
|
|||
|
|||
Formula referencing a changing range??
Wow, that's awesome, you're a genius, thank you so much!!!
"T. Valko" wrote: =INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) Create a dynamic range. Assuming the data is entered in a contiguous block. Goto the menu InsertNameDefine Name: Data Refers to: ='Marketing Letters'!$B$1:INDEX('Marketing Letters'!$B:$B,COUNTA('Marketing Letters'!$B:$B)) OK out Then your formula becomes (array entered): =INDEX('Marketing Letters'!C:C,SMALL(IF(Data=$A$1,ROW(Data)),ROWS(A$ 1:A1))) -- Biff Microsoft Excel MVP "Stacie2410" wrote in message ... I have a workbook that has several tabs. I'm only working with 2 for the problem that I'm having though. The first tab, which holds all my data, is called Marketing Letters. The 2nd tab is called Status. On the Status tab, I have a cell (A5) which looks to another cell (A1) on the same tab, then returns matching values (in multiple rows) from the Marketing Letters tab. Here is my formula: =INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) This works great, however...I am constantly adding data to the Marketing Letters tab. Currently there's 300 rows, but tomorrow it could be 305 or by the end of the week could be 350. Rather than changing my formula on the status tab each time to reflect how many rows have data on the Marketing Letters tab, is there a way to have it automatically determine the number of rows with data? I tried using the $B:$V, but my formula didn't work with that. Thanks for your help! . |
#4
|
|||
|
|||
Formula referencing a changing range??
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Stacie2410" wrote in message ... Wow, that's awesome, you're a genius, thank you so much!!! "T. Valko" wrote: =INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) Create a dynamic range. Assuming the data is entered in a contiguous block. Goto the menu InsertNameDefine Name: Data Refers to: ='Marketing Letters'!$B$1:INDEX('Marketing Letters'!$B:$B,COUNTA('Marketing Letters'!$B:$B)) OK out Then your formula becomes (array entered): =INDEX('Marketing Letters'!C:C,SMALL(IF(Data=$A$1,ROW(Data)),ROWS(A$ 1:A1))) -- Biff Microsoft Excel MVP "Stacie2410" wrote in message ... I have a workbook that has several tabs. I'm only working with 2 for the problem that I'm having though. The first tab, which holds all my data, is called Marketing Letters. The 2nd tab is called Status. On the Status tab, I have a cell (A5) which looks to another cell (A1) on the same tab, then returns matching values (in multiple rows) from the Marketing Letters tab. Here is my formula: =INDEX('Marketing Letters'!$B$1:$V$300,SMALL(IF('Marketing Letters'!$B$1:$B$300=$A$1,ROW('Marketing Letters'!$B$1:$B$300)),ROW(1:1)),2) This works great, however...I am constantly adding data to the Marketing Letters tab. Currently there's 300 rows, but tomorrow it could be 305 or by the end of the week could be 350. Rather than changing my formula on the status tab each time to reflect how many rows have data on the Marketing Letters tab, is there a way to have it automatically determine the number of rows with data? I tried using the $B:$V, but my formula didn't work with that. Thanks for your help! . |
Thread Tools | |
Display Modes | |
|
|