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

David Tannenbaum
Guest
Posts: n/a

 3rd Aug 2012
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?

David Tannenbaum
Guest
Posts: n/a

 3rd Aug 2012
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.

zvkmpw
Guest
Posts: n/a

 3rd Aug 2012
> 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.

David Tannenbaum
Guest
Posts: n/a

 3rd Aug 2012
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 setof 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.

zvkmpw
Guest
Posts: n/a

 3rd Aug 2012
> 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.

zvkmpw
Guest
Posts: n/a

 5th Aug 2012
> 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.

David Tannenbaum
Guest
Posts: n/a

 11th Aug 2012
Amazing, thank you!!!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Paul Microsoft Excel Programming 5 27th May 2009 01:00 PM jday Microsoft Excel Programming 1 25th Mar 2009 11:49 PM MikeB Microsoft Excel Discussion 6 5th Dec 2008 06:06 PM Art MacNeil Microsoft Excel Discussion 2 14th Jan 2006 02:16 AM =?Utf-8?B?UmljaCBK?= Microsoft Excel Programming 2 16th Feb 2005 12:33 AM

Features