INDIRECT REF TO DEFINED NAME

[srizki]

=INDIRECT("'" & A2 & "'!" & B2)
I am trying to use this formula to get to total of each month depending on A2. Cell A2 will have drop down of months names, that is Tabs names. I want B2 to have total of each month rather than cell reference, because Total may not be always in the same cell, we add rows if we add new account number or cost center.
So, Can I use name function instead of cell reference, for example April-total, May-total, June-total etc.

Thanks

 

[srizki]

Thanks

 

[srizki]

[No message]

 

[Andrew Poulsom]

If you have a range named April_total you can use:

INDIRECT("'"&A2&"'!April_total")

or:

INDIRECT("'"&A2&"'!"&B2&"_total")

if B2 contains April.

 

[srizki]

Thanks Andrew, Your second formua is the one I was looking for, the first one has April in it, so I think if I change month in A2 the formula will not change and it will still bring the total from April sheet, is that right?

Please let me know,
and congratulations for your 20,000 posts, I missed the forum for a while.

 

[srizki]

Oh Ok, so you are saying that if I have name range ...
yes you are right, my range is months not Total_..
Thanks Andrew.

 

[srizki]

I used the same formula on another workbook where total is at C19 on each sheet, but I am getting #Ref! error, is it because the months on tab are text while months and year on the cells are date formated, and how can I fix this issue.
Thanks

 

[Andrew Poulsom]

Say A2 contains the date 1 April 2007 formatted as mmmm to show April. If sheet April contains a range named Total you can use:

=INDIRECT("'"&TEXT(A2,"mmmm")&"'!Total")

 

[srizki]

A2 Contains month and year as January 2007, February 2007, March 2007 and formatted as mmmm yyyy, sheets also have same January 2007, February 2007 etc.
And my formula is =INDIRECT("'"&A2&"'!"&B2)
Where B2 contains C19.

Thanks Andrew

 

[Andrew Poulsom]

Try:

=INDIRECT("'"&TEXT(A2,"mmmm yyyy")&"'!"&B2)