Insights

Excel: List All Worksheets in a Workbook on One Tab

  • Peter Lynch

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.

Continue Reading

Excel: Reference the tab name in a cell.

  • Peter Lynch

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

Continue Reading

Excel: Summing cells across multiple worksheets with one formula.

  • Peter Lynch

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.

Continue Reading

Referencing Data on a Specific Worksheet with =INDIRECT

  • Peter Lynch

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.

Continue Reading

Excel: Dynamic Column Reference in a Financial Model

  • Peter Lynch

This video explains how to use SUMIF and the INDEX + MATCH function combination in Excel to dynamically reference columns in a financial model. This has been particularly useful to me when I am organizing a company’s data by location or product category.

Continue Reading