+ Reply to Thread
Results 1 to 12 of 12

sheet2!A1 to reference sheet1!A1 at all times

  1. #1
    Registered User
    Join Date
    01-27-2007
    Posts
    5

    sheet2!A1 to reference sheet1!A1 at all times

    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.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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 !!!

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    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

  4. #4
    Registered User
    Join Date
    01-27-2007
    Posts
    5
    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....

  5. #5
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    =OFFSET(INDIRECT("Sheet1!A1"),0,0)

    VBA Noob

  6. #6
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by EddieLee1
    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....
    you should use named ranges
    give a name to cell A1
    here is the link about creating named ranges.

    http://www.contextures.com/xlNames01.html

  7. #7
    Registered User
    Join Date
    01-27-2007
    Posts
    5

    try try try again..!

    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....

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    I did test but I don't think you did

    See attached

    VBA Noob
    Attached Files Attached Files
    Last edited by VBA Noob; 01-27-2007 at 02:22 PM.

  9. #9
    Registered User
    Join Date
    01-27-2007
    Posts
    5

    Thanks for your help....

    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

  10. #10
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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

  11. #11
    Registered User
    Join Date
    01-27-2007
    Posts
    5

    Sorry for the hassle...

    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

  12. #12
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1