INDIRECT REF TO DEFINED NAME

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
=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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If you have a range named April_total you can use:

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

or:

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

if B2 contains April.
 
Upvote 0
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.
 
Upvote 0
Oh Ok, so you are saying that if I have name range ...
yes you are right, my range is months not Total_..
Thanks Andrew.
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top