Helper value in cell to help pivot formating

[Manosh]

Hello everyone!
I have a table with multiple rows below a 'header' row.
What i'd like to achieve is that an adjoining cell receive the correct header row value so that i can later use this column to help organise a pivot table.
Multiple rows may get added below the particular header and the cell should automatically reflect this.
I was thinking of using the numbering in Col 1 to indicate a trigger of which cell value to copy but couldnt think of a match index formula that may work.
Hope i have been able to explain this properly, the excel sheet is provided.

 

[Luke M]

I'd suggest continuing with your idea of putting a number in col A to indicate which rows are actually continents. Then, the formula in G2 is:
=IF(ISNUMBER(A2),"",LOOKUP(9E+99,$A$2:B2))

Copy down as needed.

 

[Manosh]

I'd suggest continuing with your idea of putting a number in col A to indicate which rows are actually continents. Then, the formula in G2 is:
=IF(ISNUMBER(A2),"",LOOKUP(9E+99,$A$2:B2))

Copy down as needed.

Brilliant - thanks a tonne!

 

[Manosh]

Brilliant - thanks a tonne!

Luke another question - what happens if there is a column/ columns between the number and the title?
The formula stops working... and gives blanks.
Any suggestions- i am not sure at this point how many columns there may be and unfortunately i need to keep them between the number column and the characteristics column...

 

[Somendra Misra]

Hi Manosh,

Try below formula in J2 and copy down:

=IF(ISNUMBER(A2),"",LOOKUP(9E+99,$A$2:A2,D$2:D2))

Note: this is modified formula of @Luke M Only.

Regards,

 

[Luke M]

Somendra hit it on the head.