I need to get data from a total cell in one worksheet into a cell in another
worksheet. Because the source worksheet will have lines added to it, I wanted
to reference the cell by its row and column label (e.g. "Jan Sales"), rather
than by an absolute cell reference. I tried several variations and keep
getting the #REF and #NAME errors.

Does anyone know how to do this?

Thanks.

Marc G wrote:
I need to get data from a total cell in one worksheet into a cell in another
worksheet. Because the source worksheet will have lines added to it, I wanted
to reference the cell by its row and column label (e.g. "Jan Sales"), rather
than by an absolute cell reference. I tried several variations and keep
getting the #REF and #NAME errors.

Does anyone know how to do this?

Thanks.
Well, let's first be sure of the facts. You refer to referencing a cell
by "its row and column label (e.g. 'Jan Sales')". I take it that in fact
Jan is not a row label but is a column label? And that you have an
unchanging number of columns, one for each of the 12 months?

Assuming that is so, consider a table with 3 rows, Sales, Costs and
PreTaxIncome in A1:M4 a sheet named Sheet1. Highlight A1:M4, click on
Insert|Name|Create, check Top row and Left Column, click OK. Now in a
cell on Sheet2 enter =Sheet1!Jan Sales for the value at the intersection
(and if you don't duplicate the row and column labels on any other sheet
you can omit the Sheet1! and simply use, e.g., =Jan Sales).

Then you can recreate the row and column labels each time a row is added

Alan Beban

Name the actual cell and then reference the cell's name.


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=332643