Cell Reference in Formulas

[rretzko]

I want to use a formula like: =Count(A1:A3155) where the
value of 3155 is located in a named cell ("MaxRow"). I've
tried a string formula: =Count("A1:A"&MaxRow), but that
doesn't work.

All suggestions are appreciated!

Rick

 

[Rick C]

=count(indirect("A1:A"&MaxRow))

 

[Rick C]

Ooh, so close...
Try:
=count(indirect("'C:\user\newdirectory\[download.xls]'!$A$3:$A$"&MaxRow))

Rick C - Yup, got that (after I posted the message).
Here's the rub, I'm referencing cells in another xls. The
full formula would look something like:
=count('C:\user\newdirectory\[download.xls]'!
$A$3:$A$3155).

I tried =count('C:\user\newdirectory\[download.xls]'!
indirect(B2) where B2 contained: =("$A$3:$A$"&MaxRow), but
got an 'The formula you typed contained an error.' message
box. Can you help get me to this next step?

Thanks - Rick

-----Original Message-----
=count(indirect("A1:A"&MaxRow))




.

 

[Peo Sjoblom]

Indirect does not work on closed workbooks

--

Regards,

Peo Sjoblom

Ooh, so close...
Try:
=count(indirect("'C:\user\newdirectory\[download.xls]'!$A$3:$A$"&MaxRow))

Rick C - Yup, got that (after I posted the message).
Here's the rub, I'm referencing cells in another xls. The
full formula would look something like:
=count('C:\user\newdirectory\[download.xls]'!
$A$3:$A$3155).

I tried =count('C:\user\newdirectory\[download.xls]'!
indirect(B2) where B2 contained: =("$A$3:$A$"&MaxRow), but
got an 'The formula you typed contained an error.' message
box. Can you help get me to this next step?

Thanks - Rick

-----Original Message-----
=count(indirect("A1:A"&MaxRow))

I want to use a formula like: =Count(A1:A3155) where the
value of 3155 is located in a named cell ("MaxRow"). I've
tried a string formula: =Count("A1:A"&MaxRow), but that
doesn't work.

All suggestions are appreciated!

Rick


.