Ian Bell

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

@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