+- The Orion's Arm Universe Project Forums (https://www.orionsarm.com/forum)
+-- Forum: Offtopics and Extras; Other Cool Stuff (https://www.orionsarm.com/forum/forumdisplay.php?fid=2)
+--- Forum:
+--- Thread: Excel Question (/showthread.php?tid=2440)
Excel Question - Cray - 10-14-2016
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?
RE: Excel Question - selden - 10-15-2016
(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/Run-a-macro-5e855fd2-02d1-45f5-90a3-50e645fe3155 )
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.