Adding Indirect with ROW , Index formulas

Hi,

These things, particularly where INDIRECT is involved are always easier to answer if we have the workbook to work with. Please upload it.

This maybe?


=INDEX(INDIRECT("'"&A8&"'!A:A"),SMALL(IF(INDIRECT("'"&A8&"'!A:A")='Executive Summary'!$Z$6,ROW(INDIRECT("'"&A8&"'!A:A")),ROW(1:1))+1,1))

Hi,

It's still not clear (at least to me) what you're expecting to see and where. I can't see the formula you mention and it's not helped since you appear to have changed the sheet name from Executive Summary to Summary.

Where are you wanting to put the formula and would you manually add examples of the results you expect?

In addition and since this crops up a lot, can I suggest that you are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours is a case in point.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

If it were me I'd create a single sheet database that contains fields for all the types of information you currently record on your project sheets. Then instead of holding a separate sheet for each project you'd have a single Project template sheet which would be fed with data from the database in response to the input of a Project Reference.

In other words switch your thinking. What you currently call the Summary sheet should be the start of a database in which you record original data.