Is there a way to name a range (such as an entire column, or set of columns) and have it mirrored on another worksheet?
For example, if I change something within the range on Sheet1, the change would be mirrored on sheet2.
Please no macros. Formulas please.
Thanks in advance!
This would not be related to a range name. All you need to do is reference the data on sheet 1 to sheet2....something =sheet1!A1For example, if I change something within the range on Sheet1, the change would be mirrored on sheet2.
(Thats IF I understand you correctly?)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Not quite. If I use that method than if I make a change in the future (i.e. add an extra row to the named range) than it will not be reflected in sheet 2. I would have to go through the cumbersome process of creating that reference every time I made a change.
Then use the INDIRECT() reference method...
=indirect("sheet1!A"&row(a1))
I'm not familiar with this method. Can you please elaborate on how it works? Thank you! An example with a named reference would be so helpful.
I did provide a sample formula.
INDIRECT() takes tedxt and converts in into something excel can use in a formula, and in effect produses an "absolute" reference, the difference being that it will not change if rows are inserted/deleted.
So on sheet1 A5, if you had =indirect("'sheet 2'!A5"), that would return the valuie of sheet 2 A5, no matter what you did on sheet 2
If you add a row after row 3, (so that row 4 is now row 5, it will reference what is in (the old) row 4
Thank you for an explained example. However, for my purposes this accomplishes that same thing as doing what you first suggested.
Let me reword. Is there a way to have sheet 1:columns A + B reflect everything (including changes) from sheet 2 columns A + B?
upload a sample workbook, showing examples of your data, and your expected outcome
Book1.xlsx
There you go
Based on your sample, try this in sheet1 A1, copied down...
=IF(INDIRECT("'sheet2'!A"&ROW(A1))=0,"",INDIRECT("'sheet2'!A"&ROW(A1)))
and for B...
=IF(INDIRECT("'sheet2'!B"&ROW(A1))=0,"",INDIRECT("'sheet2'!B"&ROW(A1)))
Perfect! Thanks!
ROW(A1) is vulnerable for row insertion. Use this instead ROWS(A$1:A1)Originally Posted by FDibbins
teethless...
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks