# Thread: Compositing a Cell Reference from Another Cell

1. ## Compositing a Cell Reference from Another Cell

Ok, I don't exactly know what to call this and I've spent hours and hours trying to find it on my own.

I have a bunch of cells that all reference the same row and need to be updated each month. For instance, changing each one from "13" to "14" then next month to "15". So A13, B13, C13, D13 etc. all need to be updated to A14, B14, C14, D14, and so on. I planned to use another cell elsewhere with that number in it, then each month I can update the one cell that says "13" to "14" etc. How can I do this? Or is there a better way?

Also, if it makes a difference, these will also be referring to another sheet, but I figured it wouldn't make a difference and I could hopefully adapt it as such.

Thank you so much in advance!
David

2. What the data look a like? describe it your table and your expected results in sample workbook, and try to upload at the forum, click "Go Advanced" and find paperclip button to attach the file

3. Originally Posted by Rizky
What the data look a like? describe it your table and your expected results in sample workbook, and try to upload at the forum, click "Go Advanced" and find paperclip button to attach the file
The data is just a date in one cell and dollar amounts in the others. It's just a simple monthly financial statement.

For example I want cell A1 to show the date from another sheets table which has each month listed in it's own row. So right now I happen to be referencing A13 from a different sheet, but next month I'll need to change it to reference the next row down, being A14. I've tried many variations of "=A(z1)" where z1 is the current number needed, i.e. 13, trying to get a result of "A13" that updates whenever I update the number in z1.

Seems like it should be simple, but I haven't found it yet and it's quite frustrating!

4. Try this formula in Sheet1!A2, copied across the row. It will show the corresponding cell values in a selected row in Sheet2, by
entering the (Sheet2) row number in Sheet1!A1. (e.g 13 would mean that Sheet1!A2 would report the value in Sheet2!A13:

=INDIRECT("Sheet2!"&LOOKUP(COLUMN(), Table)&\$A\$1)

It requires a named range ("Table") to be set up somewhere in the workbook, consisting of two columns to reference the columns
used in Sheet2 for the source data as below:
Col Number Col Name
1========= A
2 =========B
..========= ..
..========= ..
26======== Z
27======== AA
.. =========..
..========= ..
52======== AZ
and so on.

HTH

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•