MATCH function - using COUNT to return a row#

[vba_madness]

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,

Please Login or Register  to view this content.

I would have hoped I could use:

Please Login or Register  to view this content.

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!

Please Login or Register  to view this content.

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!

[XOR LX]

Hi,

Slightly difficult without seeing an actual workbook, but in general you need a construction like:

=MATCH(D28,Database!$A4:INDEX(Database!$A:$A,COUNT(Database!$A:$A)+2),0)

which is preferable to similar constructions involving INDIRECT due to its being non-volatile.

Regards

[vba_madness]

Sorry,

Here is a slimmed down version of what I'm trying to achieve...

[vba_madness]

Thanks XOR LX.

I've seen quite a few people praise the 'INDIRECT' function.... Could you possibly elaborate on why it is volatile?

I am still not sure which formulae (like COUNT, MATCH, OFFSET etc.) which allow you to form a cell or range reference using quotes & numbers e.g.

Please Login or Register  to view this content.

could be

Please Login or Register  to view this content.

In the end I changed the formula set up to:

Please Login or Register  to view this content.

[XOR LX]

Did you attempt to adapt the construction with INDEX that I posted to match your situation?

"Praise" INDIRECT? Of course, it has its uses. But, when constructing these dynamic, offset ranges, given a choice between INDIRECT, OFFSET (which are both volatile) and INDEX, I know which I'd prefer.

Regards

[vba_madness]

XOR LX,
Thank you, INDEX did the job - thank you! I keep forgetting to use that & tie myself in knots thinking of different ways of doing hte same thing. It will now hopefully stick in my mind for next time!
As you can see from my previous reply, I changed the way I used OFFSET slightly so as to avoid having to construct it that way.

I guess that experience will be the way that I find out when something has gone awry when using INDIRECT and OFFSET. I will, where possible use INDEX in place of these - thanks!

Now to close this thread!

[gak67]

Match, Offset and Index functions use numbers, not column headers, so you can't use them to get an A1 type reference.

I believe this as your formula for the dynamic named range is what you want:

Please Login or Register  to view this content.

[vba_madness]

Thanks gak67,

I was trying to avoid the possibility of rows 1-3 being counted in working out how many operations there were in case I wrote in an activity into there which caused an extra count. I'm not sure if I've actually managed to avoid this scenario however!