I want to be able to have a list of cells, say a1 to a5, with the exact names of my worksheets that I can then refer to in other formulas, say in b1 to b5.

For example, I was hoping to be able to type into b1 something like =a1&"!"&$w$1 to give me a constant formula that I could drag down and give me the result of what is in cell w1 on each of my worksheets without having to hand type/find each and every cell.

I would be so useful for summary sheets of worksheets that have the same layout.

Or is there any way of just giving the result as the name of a worksheet.

Hi Louise,


Your request has a number of options. Excel has a number of features to generate summary information on a worksheet by selecting data from other worksheets in the same Workbook.

1. Name Ranges are good way of identifying specific ranges of cells in differing worksheets to be used in calculations

2. Excel has routine called 3D Referencing that allows calculations to be performed across multiple worksheets and summarised in another

3. Excel also has routine called Consolidate that allows calculations to be performed across multiple worksheets and summarised in another

These are all useful for creating summary sheets for worksheets that have the same layout

Regards