Creating a formula which will work if the worksheet name is changed
Hi,
I have a spreadsheet which will be used as a template and when new students arrive the worksheets will be updated to say the students name.
How can I create formulas which VLookup into specific worksheets that won't need updating as soon as the worksheet name is changed?
TIA
Once the formula is in place, you can rename the sheet as you desire and the formula will update with the new name.
Thank you. I didn't realise it did it automatically. That's great
You're welcome.
As an additional question, I have been searching all over the web for a formula which will pick up the worksheet name so when the worksheets are renamed they will automatically pick up the new name.
I keep seeing this formula: =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]", CELL("filename",A1))-FIND("[",CELL("filename",A1))-1) but I'm unsure how I need to amend it to work in my spreadsheet. Currently I have 30 worksheets named #1, #2, #3 etc but eventually they will be renamed to student's names?
TIA
From your comments I'm assuming you're creating some sort of database from which you'll need to generate reports or other analyses.
If that's the case then I'd urge you to rethink your strategy.
Whenever you feel the need to create separate sheets for stuff like staff names, departments, weeks, products...or in fact anything where individual sheets contain essentially the same information in the same layout with just one unique identifier then think again.
Almost without exception you should capture this stuff in a normalised two dimensional table on a single sheet with an extra column to hold the unique identifier.
Once that's in place then any analysis or summarisation is easily achieved with a Pivot Table.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
For example in the attached image......the worksheet name is currently #1 but will be renamed to a student's name i.e. Bob. When this changes I want the worksheet name 'Bob' to appear in the 'student name' cell.
Hi, It's a very complicated spreadsheet tracking a lot of data and I am simply helping them set up a few additional features. It's not in my remit to restructure the spreadsheet and it works for the simple analysis we do for now. Thanks for your advice though
Do you never want to summarise information from across all students, e.g. number of hours per lesson, number of lessons per teacher, number of teachers per lesson, variance between target and actual hours per lesson...etc.. I guess you get the point.Originally Posted by jo3810
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks