Sort of Lookup… (Win XP pro SP2, XL2003 UK)

Topic

New Reply

Hi,

Need a sort of a lookup. Have done it before but forgotten how… – so please refresh my mind / ;o)

Got two sheets:
1. Data entry (DE)
2. Data out (DO)

DE is a traditional accounting workbook without formulas – pure data entry.
DE has data grouped in columns. Column C=2006, D=2005, E=2004 etc…
DE has items grouped in rows R7 = Item1, R8 = Item2

On DO I have a single cell at the top say cell B6, it contains the accounting year to look at, eg. 2006 (defined name is: ‘YR_lookup’)
Starting at row 10, I then have a number of formulas aclculating all sorts of ratios.

I would like the formulas to work in such a way that they lookup values in the DE sheet based on the YR_lookup.
This means that if I want to look at data for 2004 instead of 2006, all I need to do is to change the value in YR_lookup.

How do I achieve this?

Reply | Quote

Replies

Are the years listed in a row at the top of the DE sheet, e.g. in row 1?

Reply | Quote

Years are listed as a header row starting from row 9
Data values start from row 10 downwards.

Reply | Quote

You should be able to modify Steve (sdckapr)’s suggestion to suit your needs.

Reply | Quote

Beat me to it again, you did

sign. Yoda

Reply | Quote

Does something like the attached help you apply it to your spreadsheet?

Steve

Reply | Quote

Hi Steve,

This looks as if it is very close to what I need.
One question though for clarification. Used ‘trace dependents’ and saw that DO!A10 is a precedent to C10.
However looking at the formula in A10, I must admit that I’m not 100% certain what it really does? (except that it works!)

Please enlighten me

Reply | Quote

The formula in DO!A10 compares the value of B10 to the values in column A of the DE sheet, and returns the index (i.e. row number) of the cell where the value is found, minus 1. This value is then used in the formula in C10 as a row offset.

Reply | Quote

Understood, but…

Formula in C then…
Thought it would be a relative formula that would refer to a range, but as the formula is copied down it extends its length by adding a single row all the time.
This will only work if I don’t have blank rows in between. Otherwise it will be error prone as well and will require manual checking too (which is what I was hoping to avoid)

Are there other ways of doing the last lookup?

Reply | Quote

Sorry, I don’t understand.

Reply | Quote

Not sure I fully understand either. I’ll do some experimenting and get back to you if the problem turns out to persist.

Thanks both of you for your quick replies so far.

…Hans – got it figured out.

Hadn’t noted in the first place that the $A10 followed a ‘ ; ‘ and therefore wasn’t an end range, but the row offset value…

Reply | Quote