Hi Guys & Gals,

I'm hoping you can help - I spent a good chunk of yesterday afternoon trying to figure this out and I now need some help today, or failing that, therapy tomorrow!! :op

My problem/question concerns the breaking up of a cell reference using the MATCH command so that it's row & column. The purpose is to use the #of non blank cells to calculate the range, rather than use an arbitrary value like A4:A1000.

At the moment, I have, 
MATCH(D28,Database!$A4:$A1000,0)
I would have hoped I could use:
MATCH(D28,"Database!$A4:$A" & COUNT($A:$A)+2 ,0)
but this doesn't seem to work. I was also thinking about using the R1C1 format, but all the other formulae in the sheet are in A1 format so I don't want to change the worksheet format and I've been faffing trying to find a way to convert one to the other...


For the background, I'm trying to create a dropdown list based on another dropdown list. So the first dropdown list would be to select an activity e.g. "Wash Car"; "Cook Dinner"; "Use Computer" and the second list would be to provide a dropdown list of relevant activities e.g. if Wash Car were selected, then in the column to the right, the dropdown list would show, "Hose car"; "Sponge wash"; "hoover inside"; "Shammy Dry". I've ended up using the 'List' option in Data Validation with the following formula, which seems to work ok! 
=OFFSET(INDIRECT("Database!r" & MATCH(D28,Database!$A4:$A1000,0)+3 & "c1",FALSE),0,1,COUNTIF(Database!$A:$A,D27),1)
I would really appreciate if someone could clarify if some formulae can not be split in this way (i.e. break up a range into "A" & 5 , and some can, or whether I'm making a fundamental mistake! I used it for the INDIRECT function, so I'm struggling to see why it's not working for the MATCH function (other than a stupid mistake on my part which is a likely option!)

Secondly, if there is a better way of achieving my objective, I would also be grateful to see this!

Thank you for reading!