Automatically update formulas when sheet names change

[dsrt16]

I have all these formulas referencing closed workbooks, and they work just fine. But currently the worksheets are named A, B, C, D, E, F, G etc. but eventually they will be named student names.

The formula referencing closed workbooks:
=INDIRECT.EXT("'C:\Users\Katie\Desktop\AI Program\"&'Set-up Page'!E6&"\["&'Set-up Page'!F6&"]A'!A17")

Right now the formula works, and returns what is in cell A17 on the sheet page labeled A.

But eventually sheet A will be changed to a name: e.g. Katie.

When I change the name of the sheet to Katie in the referenced workbook, then that formula returns #REF because it is trying to reference sheet name A, which no longer exists.

I thought this automatically updated. I know in the past I have had formulas referencing sheets whose names may change, and I don't remember it being an issue....

 

[dsrt16]

I do have an idea, but I am not sure it would work.

The sheet names change according to what is typed into the set-up page.

So in the formula instead of having A'! I can have the cell that references the name for sheet A. Which is the set-up page B2. I already have the formula referencing cells to discover what folder and what file to copy, so I can reference the cell to figure out the sheet name, but I can't seem to get the syntax right for that.

How would I do that?

=INDIRECT.EXT("'C:\Users\Katie\Desktop\AI Program\"&'Set-up Page'!E6&"\["&'Set-up Page'!F6&"]"&'Set-up Page'!B2'!A17")is what I tried.

But ya I knew that would be wrong. I am just not sure the syntactical structure to use for referencing a sheet page name through a cell.

(FYI: Each teacher has their own workbook, and their folder names, workbook names, and student names (sheet names) will be different. That is why I need to just reference cells for the formula)

 

[Jon Peltier]

What's in Set-up Page!F6 is probably a static sheet name, right? How does Excel know you want it to be a sheet name, and not a letter grade or some other perfectly valid use of a letter?

If the workbooks are both opened, formulas in one will reflect changes in sheet names in the other, as well as address changes due to insertion or deletion of rows or columns. But if these addresses are stored as text and referenced in INDIRECT functions, they will not even know they have to update.

 

[dsrt16]

No, Set-up Page F6 is a formula referencing the name of the workbook.

= " "&$E$6&"Week1.xls" Where cell E6 is teacher's last name. So if I put Madsen in cell E6 then cell F6 says MadsenWeek1.xls.

It is not static it depends on the teacher's last name entered in E6. I am creating the template, and then teachers will each have their own workbook.

So my formula references cell F6 to get the workbook name to reference.

It all works just fine! It is accessing what I need it to access. The only problem is the sheet names change. So my formula currently has A'! and worksheet A will be named a students name. Each worksheet A on the various workbooks will be different student names.


I just need the syntax to reference cell B2 on the set-up page for the sheet name in my formula.

 

[Jon Peltier]

I wrote my first post before your second one came online. B2 now has the sheet name. Is this also being updated via formulas? Because any old text, such as the A in ["&'Set-up Page'!F6&"]A'!A17") in your first formula, has no way of knowing it is part of a link and should update.

 

[dsrt16]

I fixed it.

Sheet A will be named by whatever is in set-up page B2, but I could not get the syntax right to reference cell B2 on the set-up page.

However whatever is in cell B2 of the set-up page appears in cell C2 of the sheet that has the formula.

So I just changed it to
=INDIRECT.EXT("'C:\Users\Katie\Desktop\AI Program\"&'Set-up Page'!E6&"\["&'Set-up Page'!F6&"]"&C2&"'!A17") and now it works.

Cell C2 will be updated with the sheet name listed in Set-up Page B2, so it will automatically update now.

Sorry this is probably confusing, but I got it to work. A lot of playing around did the job!

 

[trunten]

---