Below is my formula. Several of Aladdin's and Domenic's posts inspired this.
What I was trying to accomplish:
Create a grid formula that would allow me to find a specific value at a given point in time based on user defined inputs.... but across multiple sheets.
In my case, I've written a macro that downloads daily settlements for four exchange traded energy commodities and then parses the data out on to four separate sheets. I then wanted a central page where people in our organization could select the commodity, select the date (or dates and choose to graph certain ones or others - but that's a separate issue) and layer in appropriate data accordingly for whatever analysis, etc. Granted Bloomberg can do this, but our business can't afford that system. We have Reuters - however, it does not have the ability to look back at a forward curve at a given point in history - neither can eSignal's Futuresource. So I set about creating this in excel.
What I did:
- Insert a combo box, create the appropriate list on a separate page, and using that cell link (Prices!$J$2), use the Choose function to select the appropriate array within the Index function and then Match as appropriate as seen below find the appropriate price for a given date.
- I also named the arrays on each sheet, 3 per sheet, 4 sheets, so 12 in all (Ctrl+F3)... row referencing array, column referencing array, and of course the main referencing array.
This wasn't elaborate and doesn't match up anywhere to some of the elaborate solutions some have come up with here on this website, but I'm happy with my results and wanted to share.
What I was trying to accomplish:
Create a grid formula that would allow me to find a specific value at a given point in time based on user defined inputs.... but across multiple sheets.
In my case, I've written a macro that downloads daily settlements for four exchange traded energy commodities and then parses the data out on to four separate sheets. I then wanted a central page where people in our organization could select the commodity, select the date (or dates and choose to graph certain ones or others - but that's a separate issue) and layer in appropriate data accordingly for whatever analysis, etc. Granted Bloomberg can do this, but our business can't afford that system. We have Reuters - however, it does not have the ability to look back at a forward curve at a given point in history - neither can eSignal's Futuresource. So I set about creating this in excel.
What I did:
- Insert a combo box, create the appropriate list on a separate page, and using that cell link (Prices!$J$2), use the Choose function to select the appropriate array within the Index function and then Match as appropriate as seen below find the appropriate price for a given date.
- I also named the arrays on each sheet, 3 per sheet, 4 sheets, so 12 in all (Ctrl+F3)... row referencing array, column referencing array, and of course the main referencing array.
This wasn't elaborate and doesn't match up anywhere to some of the elaborate solutions some have come up with here on this website, but I'm happy with my results and wanted to share.
