G'day
First post here.
Trying to use INDEX/MATCH to retrieve data, whilst using INDIRECT to incorporate the unique sheet numbers (approx 30 sheets) & unique project numbers (approx. 120 in total)
Please see attached for example with 2 sheets: 1) Summary sheet & 2)
The source data needs to be retrieved from Sheet 7553 below onto the Summary sheet. I need to prepare retrieve formulas for cells C2 & C5 (FTE data) and D2, D3, D5 & D6 (Budget $)
(Note)
1. the reconciliation has the Budget FTE (Full time equivalent - staff numbers) on the X-axis & the response provided has the Budget on the Y-axis. Hoping this does not pose a problem)
2. I only need to retrieve data where there is where there is a project number populated in Row 2. However, the formula needs to be intuitive enough to capture up to ten (10) projects that need to be retrieved.
Solution?
I started out thinking I could use INDEX/MATCH, however I can't seen to crack the issue of having the varying volume of unique projects to retrieve onto the Summary sheet.
I was thinking INDIRECT would also be handy, however; not sure how to incorporate this in a dynamic range of unique project numbers. Perhaps a completely different approach is required? I'm all ears!
I've got myself into a tangle & grateful for any suggestions, including overhauling the approach to the reconciliation on the summary sheet.
Thanks in advance legends!
Bookmarks