Creating a Master Sheet

You may consider using function INDIRECT, probably also ROW and concatenation (either by function or by concatenation operator &

for instance first column could be prepared with

Formula:

=INDIRECT("'Sheet " & ROW(A1) &"'!$C$30")

and copy down (by the way - with this method you could write C30 as relative, because it is copied down as text, so:

=INDIRECT("'Sheet " & ROW(A1) &"'!C30")

would be as good as previous version

And the second question addressed:

Formula:

=if(INDIRECT("'Sheet " & ROW(A1) &"'!C30")="","",INDIRECT("'Sheet " & ROW(A1) &"'!C30"))

solution provided by @kaper is more comprehensive and answers your question fully.

But addressing this particular issue only:

First, I would have to change the "Sheet #" by hand over 700 times unless there's a better way. What's the trick?!?

"the trick" is to simply select the relevant cells and use "Find and Replace"

Find and repace.jpg

Glad to hear that and thanks for reputation point. As that seems to take care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Well, I just found out that I need more functionality. The sheets need to have the ability to be renamed without breaking the master sheet's references. I'm assuming this means I need solid sheet references instead of the quotes/row method. Hoping this doesn't mean manually inputting all of those. Do you guys think the find/replace method may be the best bet?

Also, it would also be handy if some of the information could be input into the master sheet and be populated to the worksheet without breaking the original function. Is there a way to have it so info can be input to either the master sheet or a worksheet and have it populate on the other? In my head, if I have an INDIRECT referencing an INDIRECT back to itself, the computer will see circular logic and try to blow itself up or take over the world. Not sure which.

if you use KEV_s method (with Ctrl+H = Replace) further changes of sheet names will be reflected in formulas.
If you uuse INDIRECT, then changeeng sheet name calls for changing thexts inside INDIRECT callas (yes, you can do it with the same tool: Replace)