• 079 08/26/2018

    If you are working with dozens of worksheets that are formatted in identical fashion (think of a company that has multiple locations with the same line items), then the ability to list all of these worksheets on one tab can be extremely helpful. As you will see in the template available for download, this permits retreiving data with the =INDIRECT function from each individual worksheet.

    To create a list of tab names in your workbook follow these two steps:

    Step 1:

    Name the following formula "Sheets"

    =(GET.WORKBOOK(1))&T(NOW())

    Note: If you are not familiar with this process select Formulas > Name Manager > New and then input the formula.

    Step 2:

    Then use this formula to retreive all worksheet names:

    =INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROW(A1))

     

    Download Template

     

    List all Worksheets in a Workbook.

     

    For more information on the =INDIRECT function: Referencing Data on a Specific Worksheet with =INDIRECT.

 



  • 078 08/26/2018

    Occassionally when you are working with a lot of tabs it helps to have a reference to the tab name on the worksheet. An example might be a workbook containing financials for 100 restaurants.

    To understand how this works first input the following in any worksheet.

    =CELL("filename",A1) 

    This formula will retrieve the file path for your workbook. Since we are only interested in the name of the worksheet, we can modify this formula with the =MID function to retrieve only that portion of the file path.

    Expand upon the previous formula with the following:

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

    Note: 255 characters is the maximum length for a directory path. 

    In the image below you will see that the formula in cell D18 (which is visible in cell D17) returns "Consolidated."

    Download Template

    Reference Tab Name in Cell in Excel.

 



  • 077 08/26/2018

    Tips for working with data across multiple worksheets in the same Excel workbook.

    The single biggest requirement to make maximum use of these tricks is to have all of your data laid out in identical format across tabs. 

    Options:

    =SUM('*'!A1)

    This will sum all A1 cells on every worksheet in the workbook.

    =SUM(START:END!A1)

    Put all of the worksheets in order and then add one empty sheet before the collection of worksheets you want to work with and one after the collection of worksheets you want to work with. Name the first tab START and the second tab END. This formula will sum all A1 cells that are inbetween these two sheets.

    Both of these approaches will work with other common functions (ie =AVERAGE, =COUNT, =SUMIF, etc).

    Excel File Available for Download: Download Template

    Sum Across Multiple Excel Worksheets.

 




 



Models are:
 
A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

 

 


*Answers a, b, c, d, e, f and g are all correct.