LinkBack URL
About LinkBacks
Morning,
I was hoping someone could help with a formula - I'll try to explain the best I can but if there is somewhere I can upload a sample spreadsheet to so you can see then that would be more distinct in what I need.
I have 3 tabs - "User" "1 June 2009 - REGIONS" "1 June 2009 - LONDON"
In "User" I have 3 cells which need populating - they are "B3" "B5" and "H5".
Cells "B5" and "H5" are Data validated by List. The List entries in "B5" are "London UK, Regions UK, Dubai, Asia Pacific, Singapore". The list entries in "H5" are the same but also with "America, Euro, CNY"
Cell "B3" is also derived from a Data Validation List and has 30 or 40 items. The value in here (IE Status rankings from Partner through to Trainee) is what the formula will use to Lookup the data values I need to return. Cells "B5" and "H5" will decide which Tabs are searched.
Tabs "1 June 2009 - REGIONS" and "1 June 2009 - LONDON" are identical in layout and presentation. The only difference being that LONDON tab has different data values than REGIONS (These data values are manually entered and are purposefully different) but Column A through to AD are the same layout on both tabs.
The Headers of the columns Start in A6 and go through to AD6
The actual Data does not begin though until a few rows later from A10 to AD56
Each column in that range has a different numerical value but the structure remains the same throughout both Tabs.
In both tabs then, in range B10 to B56 are the text strings which can be selected in Cell "B3" on the User Tab. /// I can hear you all screaming "USE VLOOKUP" \\\
I have done a table of the logic I need to apply to the formula, but I can't for the life of me find one that works without exceeding the 7 nested IF statements - someone please help?
I have attached a screen shot of the table I created to assist you in understanding what it is I need.
If you need more detail please do let me know - I would like to get this finished by tomorrow so I can impress the boss at appraisal! I've done all the hard work, this is to finish it off.
Thanks very much
Jamie.
Register To Reply
Bookmarks