Excel source data lookup based on variable
Posted on 2012-03-15
I hope somebody can help with this question.
I am trying to create a set of financial reports for a customer based upon data imported into Excel using an ODBC data query. The data is imported into a worksheet called 'Source Data' and contains 12 columns, one for each financial period 1 through to 12.
The reporting worksheet, 'Report', uses a link, =ABS('Source Data'!$R$4) for period 1, =ABS('Source Data'!$S$4) for period 2, etc. 'Report' has a variable called 'Period' which will accept a user entered value between 1 and 12, corresponding to the financial period.
Is there a way of updating the link source to update the column $R, $S, etc. based upon the value of 'Period', rather than using a set of nested IF statements? So if 'Period' = 1 then the link would be "=ABS('Source Data'!$R$4)", if 'Period' = 2 then the link would be "=ABS('Source Data'!$S$4)", and so on. As the report has around 50 rows it would save a lot of typing and potential errors.