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


    and


    http://www.ozgrid.com/forum/sh….php?p=381690&postcount=1


    Thanks,
    Ger

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


    Kevin

Participate now!

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

Register Yourself Login