Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
On one tab I have data that looks like this:
8/1/2012 1 29381 2 2423 8/2/2012 5 23231 8 7542 On the second tab I would like to automatically pull this data depending on what today's date is. So, for example, I would like to do a VLOOKUP for "1" on 8/1/2012. So on the second tab, in A1, I have created a formula to tell me what row to start my VLOOKUP on: =MATCH(D3,'DT''s raw data'!A:A,0)+1 (Here "D3" is :"8/1/12"). This returns "2'. I have also created a formula, in A2, that tells me where to end my VLOOKUP for 8/1/12: =MATCH(E3,'DT''s raw data'!A:A,0)-1 This returns "3". Now I would like to write the VLOOKUP so it pulls from A2:B3 on the second tab. Something like this: =vlookup(1,FirstTab!A[A1]:B[A2],2,false) Obviously that "A[A1]" and "B[A2]" isn't working. Any way I can make it work? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
Here is a simpler way to ask this.
On TabOne I have the value "35" in A1. On TabTwo I have the value "A1" written into B1. On TabTwo I now want to call up the value in TabOne!A1 by referring to TabTwo!B1. So it would be something like this: TabOne!(TabTwo!B1) But that doesn't work, of course. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
On one tab I have data that looks like this:
8/1/2012 1 29381 2 2423 8/2/2012 5 23231 8 7542 On the second tab I would like to automatically pull this data depending on what today's date is. So, for example, I would like to do a VLOOKUP for "1" on 8/1/2012. So on the second tab, in A1, I have created a formula to tell me what row to start my VLOOKUP on. I have also created a formula, in A2, that tells me where to end my VLOOKUP Now I would like to write the VLOOKUP so it pulls from A2:B3 on the second tab. Maybe this would help get started: =VLOOKUP(1,OFFSET(FirstTab!$A$1,$A$1-1,0,$A$2-$A$1+1,2),1,FALSE) to pull column A, and =VLOOKUP(1,OFFSET(FirstTab!$A$1,$A$1-1,0,$A$2-$A$1+1,2),2,FALSE) to pull column B. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
zvkmpw, that's a helpful suggestion, but unfortunately it doesn't solve my problem, because every day I would like to pull values from a different set of rows. I can't predict in advance which rows those will be, but I do have a formula to calculate the rows. So I need a way to reference that formula.
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
zvkmpw, that's a helpful suggestion, but unfortunately it doesn't solve my
problem, because every day I would like to pull values from a different set of rows. I can't predict in advance which rows those will be, but I do have a formula to calculate the rows. So I need a way to reference that formula. OK, try this in a new tab, Sheet2 In Sheet2!A1, put a formula that returns the _FIRST_ row number of the range in FirstTab to be searched. The formula can take into account a date and/or other values. In Sheet2!A2, put a formula that returns the _LAST_ row number of the range in FirstTab to be searched. The formula can take into account a date and/or other values. In Sheet2!B1 put the value to be looked up in columnn A of FirstTab. In Sheet2!B2, put =VLOOKUP(B1,OFFSET(FirstTab!$A$1,$A$1-1,0,$A$2-$A$1+1,2),2,FALSE) Hopefully, this does the needed lookup. Explanation: The OFFSET(...) here returns a two-column sub-range inside FirstTab!A:B, limited by the row numbers computed in Sheet2!A1 and Sheet2!A2. Modify or expand as needed. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
Explanation: The OFFSET(...) here returns a two-column sub-range inside
FirstTab!A:B, limited by the row numbers computed in Sheet2!A1 and Sheet2!A2. I should add: By making the formulas in Sheet2 depend on TODAY(), the sub-range will depend on what today's date is. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a cell by using another cell for the row number
Amazing, thank you!!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring a cell | Excel Discussion (Misc queries) | |||
Question regarding referring to different cell Value | Excel Discussion (Misc queries) | |||
Referring To A Cell In A Workbook Name Based On A Cell Value | Excel Discussion (Misc queries) | |||
HYPERLINK() referring value from a cell does not change when cell | Excel Worksheet Functions | |||
cell text referring to tab name | Excel Worksheet Functions |