troubleshooting Question

Excel source data lookup based on variable

Avatar of Steve Brown
Steve BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelSpreadsheets
4 Comments2 Solutions229 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Saqib Husain
Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros