Lookup Problem

[taylors]

Hi folks, I need some help. I have been given a big spreadsheet which I need to summaries but cannot change the format.

In column A I have managers names as a title and in column B I have their team names and then the total title is back in column A.

EG Manager A Another is in cell A11, Team names in cell B12 to B22 and Total heading in A23. I need to look up the managers name in A11, find the total heading and then look up the relavant numbers in column C,D etc (team sizes are different and can range from 3 to 30 rows for each manager.

I am sure there is a nice simple solution but I am stuck on this today

Thanks as always for all your help

 

[Geoff Taylor]

Are cells A12 to A22 blank?

If so, you could try the following:-
In A12, enter the formula =+A11
Copy it.
Select all the way down the rest of the table
Press F5 (Goto command) - "select special" and then "blanks"
Paste

That should give you the managers name in every row with a team name.

Then do a search and replace (Ctrl+H)
Search for the exact description used in the totals in Col A
Replace with =INDIRECT("A"&ROW()-1)&" Total"

Have a go at that.

 

[taylors]

thanks for the advice. I will give it a go. Unfortunatley the manager name and total cells are merged (A:C) and not being able to modify the spreadsheet is causing problems. I will see if I can find a work around to your solution as it does work - when you are allowed to modify a report that is