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

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.