Excel source data lookup based on variable
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.
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.
Saqib Husain
"=ABS('Source Data'!" & Cells(4, 17 + period).Address & ")"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolutely bloody marvellous!! Thanks guys. Worked straight away with that solution, makes my reports a WHOLE lot easier.
I hope I have split the points fairly, and yes it was a smart formula I was using.
I hope I have split the points fairly, and yes it was a smart formula I was using.