I have spent all day in MS Excel.
I receive excel spreadsheets in a “fairly” fixed format.
I need to get this data into Business Objects Xcelsius via another spreadsheet that stores historical records and keeps the data in a format optimised for display.
“Normally” I would be using Data integrator (DI) to move the data from the source, store the data in a database table or two, use query as a web service (QAAWS) or Live Office to pull the data from the database directly into Xcelsius.
Here we do not have access to any of these luxuries.
I have written a translation spreadsheet that I can point to a specific source and it will re-shape the data so that it can fit my target spreadsheet. Then it’s just a case of copy and then paste values….
To make this process easier I am using the “INDIRECT” function in excel.
=INDIRECT("[" & $H$1 & "]Data!" & AU$1 & $C17) * 100
Here the current cell will grab the vaue from the workbook named in H1, go to the “Data” spreadsheet then grab the row and column attributes from AU1 and C17 then multiply the number by 100.
This means that next month I just need to change the name of my source spreadsheet and it will all just work. If a row or column “moves” in the source, I just update the metadata (AU1 or C17) in this case.
It takes a long time to set up and to check but next month it should be a breeze.