The Orion's Arm Universe Project Forums





Excel Question
#1
I've found a way for a worksheet in Excel to read its own assigned name:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

However, I'd like to make a summary worksheet that reads the frequently-updated names of other worksheets in the file. For example, if I have a file with worksheets named Summary, Specimen A, Specimen B, and Specimen C, I'd like the Summary sheet to have an automatically-updating table listing rows for Specimens A to C. Then when my boss decides to rename them Specimens 1, 2, and 3, I go through and rename the worksheets and the Summary sheet updates the table itself.

The problem is, references to other worksheets appear to need the current name of the worksheet.

Does anyone know how to do that?
Mike Miller, Materials Engineer
----------------------

"Everbody's always in favor of saving Hitler's brain, but when you put it in the body of a great white shark, oh, suddenly you've gone too far." -- Professor Farnsworth, Futurama
Reply
#2
(10-14-2016, 04:09 AM)Cray Wrote: I've found a way for a worksheet in Excel to read its own assigned name:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

However, I'd like to make a summary worksheet that reads the frequently-updated names of other worksheets in the file. For example, if I have a file with worksheets named Summary, Specimen A, Specimen B, and Specimen C, I'd like the Summary sheet to have an automatically-updating table listing rows for Specimens A to C. Then when my boss decides to rename them Specimens 1, 2, and 3, I go through and rename the worksheets and the Summary sheet updates the table itself.

The problem is, references to other worksheets appear to need the current name of the worksheet.

Does anyone know how to do that?

I am not an expert in Excel or Visual Basic, so I can't provide the details, but...

Excel can invoke Visual Basic macros
(e.g. see https://support.office.com/en-us/article...e645fe3155 )

Visual Basic can get a listing of the files in a directory
(e.g. see https://msdn.microsoft.com/en-us/library/kf41fdf4.aspx )

So I believe that Excel should be able to use VB to get a current directory listing and insert those file names into a cell.
Selden
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)