I am trying to write a formula that, based on a cell on the current sheet,
references a cell from another sheet. I am getting a #NAME? error and I
cannot figure out how to correct it.

Example: =SUM(Sheet1!F162:Sheet1!F(B2))*Sheet1!E233

The B2 cell is supposed to determine which Sheet1 cell to use. I am using
Excel version 2000 9.0.7616 SP-3. Any suggestions would be greatly
appreciated.

Use this

=SUM(Sheet1!F162:INDIRECT("Sheet1!F" &B2))

See also
http://www.cpearson.com/excel/indirect.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Paul T" wrote in message ...
I am trying to write a formula that, based on a cell on the current sheet,
references a cell from another sheet. I am getting a #NAME? error and I
cannot figure out how to correct it.

Example: =SUM(Sheet1!F162:Sheet1!F(B2))*Sheet1!E233

The B2 cell is supposed to determine which Sheet1 cell to use. I am using
Excel version 2000 9.0.7616 SP-3. Any suggestions would be greatly
appreciated.

It works! Thanks Ron for the quick response.

"Ron de Bruin" wrote:

Use this

=SUM(Sheet1!F162:INDIRECT("Sheet1!F" &B2))

See also
http://www.cpearson.com/excel/indirect.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Paul T" wrote in message ...
I am trying to write a formula that, based on a cell on the current sheet,
references a cell from another sheet. I am getting a #NAME? error and I
cannot figure out how to correct it.

Example: =SUM(Sheet1!F162:Sheet1!F(B2))*Sheet1!E233

The B2 cell is supposed to determine which Sheet1 cell to use. I am using
Excel version 2000 9.0.7616 SP-3. Any suggestions would be greatly
appreciated.

This post is particularly timely. I am today struggling with a similar
dynamic referencing issue.

I need to reference a table on another sheet. I actually want to
mirror pivot tables that are on each monthly sheet.
Currently I try to reference them as =jan!F204 thru =jan!L221
Unfortunately the row references might also change as rows are
added/deleted. As the sheet reference changes over time it would be
good if it continued to flow with the changes.
I have tried a suggestion in the lit. to present a pivot table of the
pivot table. Far too complicated!

Help...


--
Ken B
------------------------------------------------------------------------
Ken B's Profile: http://www.excelforum.com/member.php...o&userid=31274
View this thread: http://www.excelforum.com/showthread...hreadid=509375

Hi Ken

If I understand you correct see
http://www.contextures.com/xlNames01.html#Dynamic


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ken B" wrote in message
...

This post is particularly timely. I am today struggling with a similar
dynamic referencing issue.

I need to reference a table on another sheet. I actually want to
mirror pivot tables that are on each monthly sheet.
Currently I try to reference them as =jan!F204 thru =jan!L221
Unfortunately the row references might also change as rows are
added/deleted. As the sheet reference changes over time it would be
good if it continued to flow with the changes.
I have tried a suggestion in the lit. to present a pivot table of the
pivot table. Far too complicated!

Help...


--
Ken B
------------------------------------------------------------------------
Ken B's Profile: http://www.excelforum.com/member.php...o&userid=31274
View this thread: http://www.excelforum.com/showthread...hreadid=509375