Abbreviated indirect??

  • Thread starter Thread starter Dave R.
  • Start date Start date
D

Dave R.

I have a formula now like SUMPRODUCT((A1:A100=2)*(B1:B100=3)). B1:B100 is
part of a range that's linked to a query, so it expands, which screws up the
formula after rows are added because A1:A100 doesn't expand with it.

Is there a way to use INDIRECT and specify *only* the ROW as fixed?
E.g.
SUMPRODUCT((A1:INDIRECT("A100") works, but I have to specify the column. Can
I somehow use it like (I know this doesn't work):
SUMPRODUCT((A1:A&INDIRECT(row100))

?

I ask because I have many columns in that query-linked range, and if I can
simply update the ROW part it would save me much time over search/replacing
for the indidual column entries.
 
Hi
i would suggest that you use named ranges for that. You can also make
them dynamic:
goto 'Insert - Name - Define'
and use the defined name in your formulas

To make them dynamic you may use something like
=OFFSET($A$1,0,0,COUNTA(A:A))
 
Hi Dave,

Try it like this

=SUMPRODUCT((INDIRECT("A1:A"&C1)=2)*(INDIRECT("B1:B"&C1)=3)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Frank and Bob;

I was really hoping there was a way to specify only a row part and just
leave the column part already in there, it would make updating the formulas
a lot easier.
Thanks for your suggestions.
 
Ahh! I just played around with 'data range properties' and I can avoid the
need for this by checking "overwrite and clear" instead of "add new
rows"....
 
Back
Top