Is it possible to build column references in a cell and use it in a formula?

[RayMWu]

I save off data extracts every quarter into a worksheet called aptly (i.e. Q1, Q2, Q3, Q4) and I need to populate a scorecard that does lookups in the current quarter's worksheet so I currently define a name in the name manager like: CurQItmNbr to be 'Q4'!$A:$A and then do +countifs(CurQItmNbr,$A2)

I thought it might be possible to either: 1) programmatically change the entries in Name Manager OR 2) to define the derivation in a worksheet like below:

• A1 has formula +"Q" & ROUNDUP(MONTH(TODAY())/3,0)
• A2 is: +A1 & "!$A:$A" which will then be "Q4!$A:$A" which I'm not sure if it is even usable

This is a small subset/example - I need to do it for the year, and myraid of columns for departments, person, group, etc. so any help / advice would be greatly appreciated!

 

[GFC]

+A1 & "!$A:$A" which will then be "Q4!$A:$A" which I'm not sure if it is even usable

May I suggest to have a look at
- the indirect function
http://office.microsoft.com/en-gb/excel-help/indirect-HP005209139.aspx?CTT=1

- the address function - seems nicer in this case as the sheet name is a parameter
http://office.microsoft.com/en-gb/excel-help/address-function-HP010062407.aspx

 

[Somendra Misra]

HiRayMWu,

Please have a look of attached file. I had used Indirect function. If it soot your need, you can modify it.

Regards!