Obtaining Data from Separate Tabs

**squidg** · 03-08-2011, 04:56 AM

What I want to do is have the 2nd tab get information from the first tab automatically and dynamically.

At the moment I've managed to get the information from Column P but since the information is added weekly if I add information for the next week it will still reflect the information in column P instead of the most recent Column.

Does anyone know how to make this update automatically from the most recent

Example:
http://www.roadbullettransport.com.au/report.xls

Note: I have highlighted the column in blue which I'm talking about.

Thanks in advance.

**6StringJazzer** · 03-08-2011, 08:53 AM

First, I have changed row 1 of your data to use actual dates. I always recommend using actual dates instead of text data when recording dates. Also I have broken the merged cells so there is one date in each cell. This makes the solution to your question easier and also makes potential changes in the future easier.

I didn't complete every cell in your results, just the blue ones. I'm sure you will get the idea and be able to extend it.

BTW, although your explanation was very clear, the thread title is not really related to your problem. Your problem is more like "reference last column as new columns are added."

**squidg** · 03-08-2011, 05:23 PM

Many Thanks for your support.

Can you please break this code down for me and tell me what it does?

=HLOOKUP(LARGE(TokenDates,2),TokenData,8,FALSE)

**6StringJazzer** · 03-08-2011, 06:40 PM

Originally Posted by squidg

Many Thanks for your support.

Can you please break this code down for me and tell me what it does?

=HLOOKUP(LARGE(TokenDates,2),TokenData,8,FALSE)

First let me suggest that you go into Excel Help and look up the functions HLOOKUP and LARGE and named ranges.

TokenDates and TokenData are named ranges. If you go to the address box (just above the A at the upper left corner of the grid) and click the dropdown, you will see those names. If you pick one it, the range it refers to will be highlighted.

LARGE finds the kth largest value in a range. In this case it is finding the second largest date, which takes us to the last column with data.

HLOOKUP looks across a row for the specified value, in this case the second largest date. When it finds it, it goes down the specified number of rows (8 in this example), and returns the data it finds there.

**squidg** · 03-08-2011, 11:10 PM

and what does false do?

Thank you so much for this by the way! i am sooooo happy!

**6StringJazzer** · 03-09-2011, 12:11 AM

Originally Posted by squidg

and what does false do?

Not to be snarky about it but it will be easier for both of us if you hit F1 and type in HLOOKUP. It will describe all the arguments and give examples.