# INDIRECT REF TO DEFINED NAME

#### srizki

##### Well-known Member
=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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Thanks

[No message]

#### Andrew Poulsom

##### MrExcel MVP
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

##### Well-known Member
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?

and congratulations for your 20,000 posts, I missed the forum for a while.

#### srizki

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
Try:

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

Replies
1
Views
111
Replies
6
Views
259
Replies
0
Views
235
Replies
2
Views
666
Replies
3
Views
209

1,191,054
Messages
5,984,381
Members
439,883
Latest member
onions44

### 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.

### Which adblocker are you using?

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

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