asked on row reference range formula
Hi,
I'm looking for a time saving formula where I don't need to enter all the ranges in for each sheet every time.
Example
=COUNTIF(AV14:AV6520,">0")
I would simply enter the following
A1 14 B1 6520
and all formulas for same row would reference the range for col AV
Many thanks
Ian
I'm looking for a time saving formula where I don't need to enter all the ranges in for each sheet every time.
Example
=COUNTIF(AV14:AV6520,">0")
I would simply enter the following
A1 14 B1 6520
and all formulas for same row would reference the range for col AV
Many thanks
Ian
Microsoft OfficeMicrosoft ExcelMicrosoft ApplicationsSpreadsheets
Last Comment
Ian Bell
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@Shums I wouldn't use your formula, we don't know what's above row 14 and if there are blank cells in the range
ASKER
Great one Rgonzo..... Thanks ........ it works a treat.
Ian
Ian
ASKER
I used a sum using same formula but didn't work.... any ideas ?
got a "VALUE" error
=SUM("AV"&A1&":AV"&A2)
got a "VALUE" error
=SUM("AV"&A1&":AV"&A2)
=SUM(indirect("AV"&A1&":AV"&A2))
ASKER
ok thanks again........ I need the Indirect clause
Open in new window