• 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.

 



  • 076 08/21/2018

    Note: Template available for download.

    The =INDIRECT function permits referencing information in a workbook using strings of text. This can be a very handy tool when you want to create dynamic references in formulas without changing the formulas themselves. It is also a terrific way to reference data on different worksheets.

    To provide an example of how this function can be used, assume that you want to reference information on a different worksheet with tab name Sheet 1 in cell C6. On any other tab in the workbook you could input the following formula to retreive this information:

    =INDIRECT("'Sheet 1'!C6")

    But this is a very static approach. The =INDIRECT function can be made far more useful with dynamic references to text. For example, if all worksheets have the same name with the exception of a number (i.e. Sheet 1, Sheet 2, Sheet 3, etc.), then the worksheet name can be a cell reference:

    =INDIRECT("'Sheet"&[REFERENCE TO CELL WITH SHEET NUMBER]&"'!C6")

    Or as another alternative, you can input a function that will count up from one as you drag the formula down:

    =INDIRECT("'"&"Sheet"&ROW(A1)&"'!C6")

    Note: The ROW() function returns the row number. So =ROW(A1) returns the value 1. As you paste this formula down it will return a value that increases by 1 with each new row.

    This is the formula used in the image below in the array D38:D42 with the [REFERENCE TO CELL WITH SHEET NUMBER] linking to B38:B42. (The only difference being that the sheet name is Sheet (1) vs Sheet 1.)

    The biggest challenge is when all of the worksheets have unique names. For a solution to this please scroll to the bottom of this post.

    Download Template

    Reference Data on a Specific Excel Tab or Worksheet.

     

    Related Link: List All Worksheets in a Workbook on One Tab.

 




 



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.