Auto Changing lookup references

[spock1971]

Hi

I have a s/sheet for each of 4 Area Managers. They each have upto 13 managers working for them, and they in turn have upto 13 stores they are responsible for.

I've set up my lookup's to find all matching stores for one manager and then offset to the next manager when it reaches the end.

However, as some managers have more stores than others, I need to reset my lookup when the last entry is made. Currently I ,lookup u1&a2. U1 being the number 1 and a2 being the manager name. a2 is locked therefore the next row is lookup u2&$a$2 etc. When the name changes I need it to not lookup u11&$a$2 but u1&$a$12.

Any ideas

 

[shades]

Howdy. Not knowing your exact arrangement, I might suggest something that has worked well for me. That is to have another column added that is a concatenation of two cells, which allows a better/finer lookup.
________
BUY HERBALAIRE VAPORIZER

 

[spock1971]

I use concatenated fields with sumif functions all the time and can't see how this might help me here.

I have a seperate sheet for each area manager, then in column A I set the first area manager and in column B then lookup on a concatenated field of number 1-13 and area manager name. Then in each row i bring back each unique store number. At the end of the first 13 stores the lookup fails prompting column A to offset the area manager and bring the next in line back. However I have to manually change the formula in column B to go back to number 1.

Any Ideas - no worries if not.

D

 

[shades]

Can you post a small sample?
________
Bmw m2b15 history

 

[spock1971]

Sorted It

Thanks for your help. Rather than coding the concatenation, I've set up two different if(and)'s in other hidden columns and looked up on a concatenation of them instead.

Thanks again.