Help with indirect function...I am rusty...lol!

PittViper1965

New Member
Joined
Mar 15, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Very simple question, but I indeed do forget how to do this. I have a Worksheet with month names in A1:A12. It is called Sheetnames.

I need to pull the month name from the sheet, and extract various cell data for a summary sheet.

Currently, this is the horrible mess of a formula I have, which does NOT work, btw....

=sum(indirect("'"&Sheetnames&"'!A1:A12)&"'!"B60))

Obviously, I am trying to sum cell B60 in all sheets January through December. I am going to feel like a jerk when the first guy comes along and shows me exactly what I am doing wrong. Old foggy mind here...

I am very sure I am mostly likely messing up the syntax, and probably the " and ' as well, as my eyes are old and bad!

So, any help is appreciated.

Also, if Osvaldo P is on this board, I have lost your email address, as I had a hd malfunction, and lost all email contacts. So, if you read this, it's Jason, so, please contact me!

Thanks for the help all...

Jason
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi & welcome to MrExcel.
How about
Excel Formula:
=SUM(January:December!B60)
 
Upvote 0
Welcome to the MrExcel forum!

If they really are just the month names, and the sheets are adjacent, you can use a 3-D formula:

Excel Formula:
=SUM(January:December!B60)

If you want to use the A1:A12 list then maybe:

Excel Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A12&"'!B60"),"<>0"))
 
Upvote 0
Welcome to the MrExcel forum!

If they really are just the month names, and the sheets are adjacent, you can use a 3-D formula:

Excel Formula:
=SUM(January:December!B60)

If you want to use the A1:A12 list then maybe:

Excel Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A12&"'!B60"),"<>0"))
I like the excel formula using indirect, although I know the previous one would do the same. But, where to in insert the sheet called "Sheetnames" in the formula, so that A1:A12 has a reference sheet?

Thanks,
Jason
 
Upvote 0
Try:

Excel Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetnames!A1:A12&"'!B60"),"<>0"))
 
Upvote 0
Try:

Excel Formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetnames!A1:A12&"'!B60"),"<>0"))
Ty,

Tried the code. For some reason, I could not simply copy/paste it from Firefox to Excel, so I typed it it, I'm pretty sure exactly.
I no no longer get the #REF error, but the total comes out to 0. Granted, there is only data for January thru March at this point, but the total should be 122. Somehow, it is NOT picking up the data cell and summing it.

Any ideas?

Thanks,
Jason
 
Upvote 0
Ty,

Tried the code. For some reason, I could not simply copy/paste it from Firefox to Excel, so I typed it it, I'm pretty sure exactly.
I no no longer get the #REF error, but the total comes out to 0. Granted, there is only data for January thru March at this point, but the total should be 122. Somehow, it is NOT picking up the data cell and summing it.

Any ideas?

Thanks,
Jason
And actually, just tried this manually, like I have been doing explicitly using the following: =January!B60+February!B60+March!B50. And, for some reason THAT comes out to 0, which I can see by the cell values that that is obviously incorrect. So, my guess is your formula is right, but something else is going on. And at the moment, I am clueless as to what it is?
 
Upvote 0
And actually, just tried this manually, like I have been doing explicitly using the following: =January!B60+February!B60+March!B50. And, for some reason THAT comes out to 0, which I can see by the cell values that that is obviously incorrect. So, my guess is your formula is right, but something else is going on. And at the moment, I am clueless as to what it is?
I noticed that cell B60 just is =B46. So, using the formula, either way, the result came out to 0. So, I tried my hard coded way, using B46 instead of B60, and it worked. But, using your "indirect" formula, and replacing B60 with B467, it evaluates to FALSE. I think we're almost there....
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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