Match formula explained

Hi Everyone,

I am sure one of the experts can help me, well I am hoping so anyway.

I have three Sheets

Options - Which is column form and hold Options 1-18

Actual - which hold columnar financial data for each of the 18 options

Report Tab - This takes the financial data held on the Actual tab and presents individual option chosen. It does this using DATA VALIDATION drop down and the the MATCH and OFFSET function.

The two formulas are

=IF(ISNA(MATCH(Report!$D$1,Months,0))=1,0,IF(MATCH($D$1,Months,0)<=20,MATCH($D$1,Months,0),IF(MATCH($D$1,Months,0)>24,MATCH($D$1,Months,0)-24,MATCH($D$1,Months,0)-20)))

This formula allows the report user to select the option 1-18 via a drop down.


=IF(ISNA(MATCH(Report!$D$1,Months,0))=1,0,IF(MATCH(Report!$D$1,Months,0)<=12,OFFSET(Actual!$B$16,0,MATCH(Report!$D$1,Months,0),1,1),IF(MATCH(Report!$D$1,Months,0)>=15,OFFSET(Actual!$B$16,0,MATCH(Report!$D$1,Months,0)+2,1,1),OFFSET(Actual!$B$16,0,MATCH(Report!$D$1,Months,0)+1,1,1))))

This formula looks at option chosen and then pulls data from the Actual TAB based on the criteria


My problems

The report was built a few years ago when I was a lot better with excel - It was built to allow 12 options in the Report TAB and Actual TAB - I have now extended to 18 columns so an extra 6 columns of data from the Actual tab


Thank you for reviewing It is much appreciated

Let me know if I can provide any further detail

Thank you

David

Hi Tim

Tried to upload but not working, can I email you

Thank you

David

did you follow the instructions? the paperclip does not work if you tried that way, hence the instructions