Thread: Linking to last record in a spreadsheet

Doesn't populate the weight and length columns for the MAX year.... Thanks.

Or given they are all numbers and assuming there is no data beneath the last row on your Data sheet you could just use LOOKUP:

Year: =LOOKUP(9.9999E+307,Data!A:A)

and copy the above across to the right for Weight & Length (assuming Weight is in B and Length C on Data sheet)

still curious as to why you would advocate VLOOKUP & MAX rather than just LOOKUP ? More calcs & more committed to memory.

Veeeery nice!

If only all of my tables were this simple.

Attached is a slightly more complex table that is more generally like what I use in reports where historical data is listed as well as the previous 10-year average and other manipulations.

The only other problem is that while the data table grows as records are added, the referencing tables are static in numbers of records.

Any other ideas regarding a better way to skin this cat?

Thanks for the input and suggestions. Cheers!.

Originally Posted by martindwilson

donkyote its only that vlook up is often easier to use. look up requires ordered data
(then again i prefer index/match) but calcs+memory are not really an issue .
portsample
MAX(data!A:A)-10 whats the -10 all about?

The order of data in the context of the original question was (technically) not important as the OP was looking to return the "last value entered" -- using LOOKUP with BIGNUM would always give the correct result (ie last value) given Binary Search algorithm employed.

That said I concede that having seen the latest Q an INDEX/MATCH approach is the way to go... unless the table is 2 columns I see little reason for ever using VLOOKUP.

Portsample -- what is you're trying to simplify -- from what I could see of your file it was working, no ? You could switch to an INDEX/MATCH approach such that:

B2: =INDEX(data!B:B,MATCH($A2,data!$A:$A,0),1)
this can be applied across range B2:C11

And also change your formula in A such that:

A2: =$A13-10

Then change:

A3: =$A2+1
copy to A11

reduces calcs size a little ...

Or you could use

A2: =$A$13-(12-ROW())
and apply across A3:A11

Doing either of the above would reduce (to a negligible level) the size of the calc you're performing.

Very sweet.

One more minor question:

Some of the generated tables are historical and show previous values. Using the structures that we've been discussing there is only a fixed number of records in the table so that if I were to enter 2009 values in my data table (shown below),

year | weight | length | length/wt
2001 | 21 | 121 | 5.76
2002 | 25 | 132 | 5.28
2003 | 19 | 112 | 5.89
2004 | 31 | 135 | 4.35
2005 | 22 | 126 | 5.73
2006 | 27 | 145 | 5.37
2007 | 28 | 147 | 5.25
prev 5yr avg | 25.4 | 133 | 5.32
2008 | 56 | 301 | 5.38

...then the 2001 values will be bumped thusly:

year | weight | length | length/wt
2002 | 25 | 132 | 5.28
2003 | 19 | 112 | 5.89
2004 | 31 | 135 | 4.35
2005 | 22 | 126 | 5.73
2006 | 27 | 145 | 5.37
2007 | 28 | 147 | 5.25
2008 | 56 | 301 | 5.38
prev 5yr avg | 25.4 | 133 | 5.32
2009 | 19 | 112 | 5.89

Question: Is there a good way to configure the generated table so that records are added to it as they are also added to the data table? This would result in the following table w/2001 not being "bumped" when 2009 data is added.

year | weight | length | length/wt
2001 | 21 | 121 | 5.76
2002 | 25 | 132 | 5.28
2003 | 19 | 112 | 5.89
2004 | 31 | 135 | 4.35
2005 | 22 | 126 | 5.73
2006 | 27 | 145 | 5.37
2007 | 28 | 147 | 5.25
2008 | 56 | 301 | 5.38
prev 5yr avg | 25.4 | 133 | 5.32
2009 | 19 | 112 | 5.89

I've attached a copy of the table that we've been discussing that uses Index and Match to retrieve values from a reference table.

Thanks for insight and advice rendered.

reversing the order would make your life a lot easier... it's late so I've probably come up with an inefficient method to do this... but have provided 2 examples in either order... both make use of a helper cell or two (the first only one though the 2nd uses a few more).

In reality this task is only tricky in Asc order (as you no doubt know) because of the prior 5 year avg requirement in a table of varying length ... if you didn't have that (or it were in a fixed location) your life would be simpler still...

Needless to say there are ten different ways to skin this cat so there will be other solutions offered I'm sure and it's late so I've no doubt reinvented the wheel somewhere along the line...

(note: I switched the INDEX/MATCH to LOOKUP given your source data is sorted in asc order (saves typing!))