Count With Address

[cadence]

a quicky

how can I re-write this expression to count from C2 to the address

EG A1=5 A3=10

=COUNTA(C2ADDRESS(A3+1,A1+2)))

i'm trying to count all cell with items in range C2:G25 up to where ever the address data goes to?

thanks

 

[cornflakegirl]

If all the ranges were in one column, you could use =COUNTA($C$2:INDEX(C:C,MATCH(A3,C:C,0))) . This assumes that the values in your ranges are unique - a value in Area2 won't be duplicated in Area3.

If they're not unique, then
=COUNTA($C$2:OFFSET(INDIRECT("Area"&$A$1),MATCH($A$3,INDIRECT("Area"&$A$1),0)-1,0,1,1))
should work - although this assumes no headers for the areas, so would need adjustment if you did include them.

Not actually that much nicer than the original answer - it just felt like it should be!

 

[cadence]

Thank you sooooo much this is the way I wanted to do it but did nlt know how to solve in a single column.

I've almost got it sorted but...

two questions
1. in your previous entry "=INDIRECT("Range"&$A$1)" where do I insert this. I tried to but in in the format control of the second Droplist but not very successful.

Currently the in Input range is 'Range' and the cell link is '$A$3' - What am i missing.

Range is currently defined as "=OFFSET(INDIRECT(Budget!$A$5),0,0,COUNTA(INDIRECT(Budget!$A$5)),1)" - if that's important

2. one weird thing is if DropList A is on Area 1 then it shows all option from Area 1-5 in DropList B, but any other selection, they fall into their proper range.

thanks, this will I can really expand into other parts of me work.

ricebubble

 

[cornflakegirl]

I've got a spreadsheet set up the way I think yours is - so if I detail my setup, we may be able to spot the differences that are causing problems...

A1 has got Data Validation - it's set to a List and the Source is 1,2,3,4,5 (yours might be referencing a spreadsheet range).

A3 has Data Validation - a List with Source =INDIRECT("Area"&$A$1) (I changed from Range to Area - I'm fickle like that!)

The items that were in cols C-G are now all in C, with a space between each range (just to check that it worked like that - you could stick them all together if you wanted).
C2:C15 is named Area1
C17:C35 is named Area2
C37:C48 is named Area3
C50:C66 is named Area4
C68:C89 is named Area5

Whichever number I select in A1, I get the right range to select from in A3.

What is different for you?

 

[cadence]

Hey, sorry to be a pain, I followed your steps to the letter on a clean document [twice] and cannot get the second droplist to take up the range. I get what its trying to do [cool] but can't get it to budge, it always just has no second option list.

just to recap what I did
1. column B1:B5 Area1-5 called Range
2.C2:C15 is named Area1 [NO gap between Area_1]
C17:C35 is named Area2
C37:C48 is named Area3
C50:C66 is named Area4
C68:C89 is named Area5
3. DropList 1 Input Range: Range
cell value $A$1
3. DropList 2 Input Range: =INDIRECT("Area"&$A$1)
cell value $A$3

What is weird is that when ever i go back into the format control of the second dropList the Input range is always blank even when i paste =INDIRECT("Area"&$A$1) in.

If that is exactly right, can I perhaps email you or vice versa a spread sheet otherwise I might waste the rest of your life helping a ludite.

thank you

 

[cornflakegirl]

To the letter - except that my inputs for DropList 1 are {1,2,3,4,5} and yours are {Area1,Area2,Area3,Area4,Area5}

Try making the input for Droplist 2 just INDIRECT($A$1)

 

[cadence]

I am going mad, I changed to {1,2,3,4,5} [the output is still 1-5 regardless, and i did try your suggestion before [plus all manner of combinations] and again, but it still vanishes if i go back in to the dropList, very weird. [even tried mac and pc environments

On trying INDIRECT($A$1) I get "reference is not valid".

I just can;t see what I'm missing?

 

[cornflakegirl]

Not sure if cadence is planning to post back here or not - but current issue resolved by email - the problem was that I interpreted "drop list" to mean data validation - but he was talking about a combo box. Assumption being the mother of all unresolved mrexcel threads...