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.

 Referring to a cell by using another cell for the row number
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Referring to a cell by using another cell for the row number

#1
August 3rd 12, 12:12 AM posted to microsoft.public.excel.worksheet.functions
 David Tannenbaum external usenet poster Posts: 7
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
August 3rd 12, 01:14 AM posted to microsoft.public.excel.worksheet.functions
 David Tannenbaum external usenet poster Posts: 7
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
August 3rd 12, 01:48 AM posted to microsoft.public.excel.worksheet.functions
 zvkmpw external usenet poster Posts: 114
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
August 3rd 12, 02:08 AM posted to microsoft.public.excel.worksheet.functions
 David Tannenbaum external usenet poster Posts: 7
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
August 3rd 12, 11:19 PM posted to microsoft.public.excel.worksheet.functions
 zvkmpw external usenet poster Posts: 114
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
August 5th 12, 08:54 PM posted to microsoft.public.excel.worksheet.functions
 zvkmpw external usenet poster Posts: 114
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
August 11th 12, 11:17 PM posted to microsoft.public.excel.worksheet.functions
 David Tannenbaum external usenet poster Posts: 7
Referring to a cell by using another cell for the row number

Amazing, thank you!!!

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Referring a cell Terry0928 via OfficeKB.com Excel Discussion (Misc queries) 3 May 21st 10 09:22 AM Question regarding referring to different cell Value Gunti Excel Discussion (Misc queries) 6 January 27th 09 12:00 AM Referring To A Cell In A Workbook Name Based On A Cell Value DINGO0Z Excel Discussion (Misc queries) 1 December 28th 07 03:48 AM HYPERLINK() referring value from a cell does not change when cell Hung Excel Worksheet Functions 1 June 8th 07 03:22 AM cell text referring to tab name Trilexist Excel Worksheet Functions 1 February 15th 05 04:16 PM

All times are GMT +1. The time now is 04:04 PM.