I have sheet1 and sheet2. I want sheet2!A1 to reference sheet1!A1 at all times even if a new row is inserted into sheet1!A1. I can do it with a macro etc but I realy need to find this answer, it looked so easy but......thanks for your help.
I have sheet1 and sheet2. I want sheet2!A1 to reference sheet1!A1 at all times even if a new row is inserted into sheet1!A1. I can do it with a macro etc but I realy need to find this answer, it looked so easy but......thanks for your help.
Hi,
Does this help
=Sheet1!$A$1
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
Take a look at this link also
http://www.cpearson.com/excel/relative.htm
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
these answeres are correct under normal conditions,BUT, if a new row is inserted at sheet1!A1 and some data is enteredinto cell A1 this would mean that the original data is now in cell sheet1!A2. Sheet2 cell A1 now contains a reference to cell sheet1!A2.....I would like it to still be getting its data from sheet1!A1.....a different problem I think....
Try
=OFFSET(INDIRECT("Sheet1!A1"),0,0)
VBA Noob
you should use named rangesOriginally Posted by EddieLee1
give a name to cell A1
here is the link about creating named ranges.
http://www.contextures.com/xlNames01.html
starguy
Tahir Aziz
PAKISTAN
https://www.facebook.com/businessexcel
__________________
Forum Rules (read before you post)
Links to the world of Excel
All good attempts but none work...they will all work up to the point before I enter a new row in sheet!A1 then all references refer to sheet!A2....try them yourselves and see....
I did test but I don't think you did
See attached
VBA Noob
Last edited by VBA Noob; 01-27-2007 at 02:22 PM.
Thanks for your help....now can you insert a row or two in sheet1 row1 then enter data into sheet1!A1 and try and readit in sheet2!A1 which is what I am after....again thanks for you help
So what cell you want to read A1 or A3.
Walk us through an example.
E.G
What's in Cell A1 before you insert rows
What do you enter in A1 after you insert rows
What should be shown in Sheet2 A1.
The formula I provided will always read cell A1 which is asked for. Have you change the requested ?
VBA Noob
Thanks for your help, yes you were right the offset worked correctly, I failed to add new data to the new cell at sheet1!A1 and so only saw a 0 in sheet2!A1. Sorry for the hassle...I'm new to this sort of thing.
I thought that this was such an easy request from a spreadsheet that it would be a simple case of a changing the way I reference the original cell.
Again thanks for your help
No problem
You could use just
=INDIRECT("Sheet1!A1")
However If you enter this in A1 and drag down or across it will return Sheet1 value for that cell
=OFFSET(INDIRECT("Sheet1!A1"),ROW()-1,COLUMN()-1)
If you don't want to see the zero then either turn off zero in options or use
=IF(OFFSET(INDIRECT("Sheet1!A1"),ROW()-1,COLUMN()-1)=0,"",OFFSET(INDIRECT("Sheet1!A1"),ROW()-1,COLUMN()-1))
VBA Noob
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks