Ian Bell
asked on
modify formula for range changes
Hi,
I would like help to modify a formula to change according to number of rows in a sheet.
Cell A1
=COUNT(A8:AQ148240)
=COUNTA(V8:V4335)-COUNTIF( AV8:AV4335 ,"=0")
second formula to be changed to include contents of cell A1 eg range V8:V148240 AV8:AV148240
Many thanks
Ian
I would like help to modify a formula to change according to number of rows in a sheet.
Cell A1
=COUNT(A8:AQ148240)
=COUNTA(V8:V4335)-COUNTIF(
second formula to be changed to include contents of cell A1 eg range V8:V148240 AV8:AV148240
Many thanks
Ian
The one I use for making a range is...
=OFFSET($A$1,0,0,COUNTA($A :$A),10) 10 is the number of columns for me... you can change it to what ever you like.
=OFFSET($A$1,0,0,COUNTA($A
I did think about a dynamic range as Edward suggested, but you still have to count the entire column so why bother with that extra step?
ASKER
Could you please modify the formula I supplied so I can test
Thanks
Thanks
I did. Try the formula I posted above.
ASKER
Sorry I can't follow it.
I have attached a sample can you please modify to change automatically when cell A2 changes.
The cell needing modification is H3 in case of doubt.
Thanks
Ian
auto-range-change.xlsx
I have attached a sample can you please modify to change automatically when cell A2 changes.
The cell needing modification is H3 in case of doubt.
Thanks
Ian
auto-range-change.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Wayne,
That's perfect
Thank You
That's perfect
Thank You
=COUNTA(V:V)-COUNTA(V1:V7)