Formula - Generate Range Reference from Lookup?
Posted by JAF on January 24, 2001 6:41 AM
Bit of a weird one this, but here goes.
A1:A4 contain the value Main
A5:A6 contain the value Finance
B1 = Main1, B2 = Main2, B3 = Main3, B4 = Main4, B5 = Fin1, B6 = Fin2
Column C contains the unique division names (C1 = Main, C2 = Finance)
What I would like to do is to create a formula (in cells D1 and D2) that returns the range reference of teh entries in Column B where the entry in Column A is equal to the value in Column C.
in other words D1 should return the result $B$1:$B$4 and D2 should return $B$5:$B$6 - those being the ranges of departments belonging to the each Division (as outlined in Column A) that match the values in cells C1 and C2.
There you go - clear as mud!
I'd prefer to do this in a formula rather than populating it by running a macro if at all possible. I have worked out some code that will do the job, but it's a bit messy and a formula seems the obvious way to go.
I've tried the CELL and ADDRESS functions but not got the results I needed.