• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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.
0
Steve Brown
Asked:
Steve Brown
  • 2
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
"=ABS('Source Data'!" & Cells(4, 17 + period).Address & ")"
0
 
dlmilleCommented:
@ssaqibh - would INDIRECT be required here?

@AztechMicros - are you looking for macro code or a smart formula?

Dave
0
 
Saqib Husain, SyedEngineerCommented:
Hi, Dave, I went by

has a variable called 'Period' which

thinking that it was for VBA but maybe variable means range name

So in that case it should be

=ABS(INDIRECT(ADDRESS(4,17+period,,,"Source Data")))
0
 
Steve BrownTechnical DirectorAuthor Commented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now