Getting value from next-highest 'header' row

Hi all,

I have a worksheet that contains a number of rows. [Thank you, Captain Obvious!] These rows are logically grouped to represent a series of "items", with each item having one or more rows (with a "header" row at the top). The rows for a particular item represent different types of information about that item, and therefore make use of different formulas based on the row type. Due to the relative complexity of the formulas in these rows, I am creating a series of row "templates" from which I can copy and paste every time I want to create a new item or add rows to an item. As such, I am trying to avoid direct cell references to different rows, as these would easily get messed up during cut and paste. Instead, I am trying to limit myself to various lookup functions (e.g. OFFSET, MATCH, etc...) that will work regardless of where a row is added.

In order to accomplish this I am using a "key" column to group all rows for a particular item together. Until now I have had to type in the key value for every row for a particular item. What I would like to do is to only have to type the key value in the header row, and have a formula populate the key value in all subsequent rows ... until a new header row is reached, at which point the new key value will be used. Take a look at the following example:

a) Item Header row containing key value in column A and a header "flag" in column B
b) Item Detail row containing key lookup formula in column A
b) Item Detail row containing key lookup formula in column A
b) Item Detail row containing key lookup formula in column A
> Blank separator row
b) Item Detail row containing key lookup formula in column A
b) Item Detail row containing key lookup formula in column A
> Blank separator row
b) Item Detail row containing key lookup formula in column A
b) Item Detail row containing key lookup formula in column A
> Blank separator row
c) Item Header row containing key value in column A and a header "flag" in column B
d) Item Detail row containing key lookup formula in column A
d) Item Detail row containing key lookup formula in column A
> Blank separator row
d) Item Detail row containing key lookup formula in column A
> Blank separator row
e) Item Header row containing key value in column A and a header "flag" in column B
f) Item Detail row containing key lookup formula in column A
f) Item Detail row containing key lookup formula in column A

In the above:

- column A in all "b" rows should lookup the key value in column A of row "a".
- column A in all "d" rows should lookup the key value in column A of row "c".
- column A in all "f" rows should lookup the key value in column A of row "e".

Can anyone suggest how I can accomplish this? I don't want to use direct references between rows. I would like some formula that will fetch the value from column A in the first row above the current row that contains the "flag" in column B.

I appreciate any help you can offer!

Thanks,
Greg

It'd be easier if we see an actual sample sheet attached here.

Are you trying to automatically populate the titles in Column A where the change occurs? Not following?

If you are manually typing those in, then you can use this formula in A3 copied down to A9, then re-inserted in A11 and copied down, etc:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear

Then my formula as prescribed above should do that. Give it a try and let me know.

NBVC, I don't think you actually need an array here... the following would work:

Please Login or Register  to view this content.

True.... where there is a will there are many ways...

Originally Posted by NBVC

where there is a will there are many ways...

...and the one without the array is invariably quicker

LOOKUP(2,1/($A$1:$A2<>""),$A$1:$A2)

That Lookup() formula will find the last entry that matches the condition in the denominator of the Lookup Array argument (ie. the 2nd argument of the function).

If you select the cell with the formula and go to Tools|Formula Auditing|Evaluate Formula and click Evaluate to step through the way the formula is evaluated, you will see what is going on.

The denominator, when evaluated, creates an array of TRUEs and FALSEs depending on if the condition is met or not in each element of the array. The 1/denominator will then create an array of 1's or #DIV/0 errros because 1/TRUE is the same as 1/1 which =1 and 1/FALSE is the same as 1/0 which results in an error....

Now the next thing for the Lookup() function to do is look for a 2 in that resultant array... It can't find one and the function looks for the last entry in the array that is smaller than or equal to the 2.. ie. the last 1 in the array is targeted and that postion is returned... then the corresponding item from the lookup vector (the 3rd argument) is extracted.

I would add the following...

Please Login or Register  to view this content.

LOOKUP will look for items of the same data type as the lookup_value (aka criteria)... so if your lookup vector is evaluated as follows:

Please Login or Register  to view this content.

and your criteria is 2 the #DIV/0! are ignored as they are not numbers... this is why LOOKUP is so handy.... more often that not formulae referencing error values in turn generate an error result... not so LOOKUP... so by coercing the values you want to ignore to become an Error (#DIV/0!) and the ones you want to keep to a number (1) you filter your records....

Given the MAX value you can generate in your array of values is going to be 1 by setting the lookup_value to 2 you will always find the last instance of 1 in the range.... it could be 1.000001 (ie any value > 1) but 2 obviously is easier to follow so is the standard.

I'm not an expert on this so I'll happily stand corrected.