Prevent Formula Cell Referencing Updating

  • I have a series of worksheets, named, 'sheet1' 'sheet2' etc. through to 'sheet10'. A final worksheet, 'sheet11' has been produced that uses the formula: address=Sheet1!A1, address=Sheet2!A1, address =Sheet3!A1 etc.
    The same goes for other identical cells on each of the sheets.
    I am at the moment entering each cell on 'sheet 11' separately because if I copy the formula down it progressively changes the cell value and not the sheet number.

    How can I produce a formula whereby the cells remain identical but the sheets change progressively?

  • Re: Formula

    All i can say is "What??????" your post doesnt make much sense, why not upload a workbook to show us what you are trying to achieve!

    Look up Absolute cell referencing and Concatenation.

  • Re: Formula

    Kevin, you are welcome to the forum. However, you must abide by the rules you agreed to and part of that includes having a proper thread title "Formula" is not a proper thread title. I will change it this time for you, but in future, your post will be locked/deleted.

    Your thread title should reflect your actual problem, should never assume a solution and must always be concise/precise.

    Click on the Rules hyperlink in this this thread to review more thread title rules (which you agreed to when signing up to use this free service).



    There are 10 types of people in the world. Those that understand Binary and those that dont. :P

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)


  • Re: Prevent cell references from updating

    On your consolidation sheet enter "Sheet1" in cell A1.
    Enter the following formula in cell B1: =INDIRECT(A1 & "!A1")

    Copy those two cells down and it should increment the sheet name automatically and always give you the value of cell A1.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Prevent cell references from updating

    Brilliant: You are wonderful, I knew it must be simple but no one at the University I work for seemed to know how to solve this little dilemma.

    Thanks for your help.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!