Indirect with Named ranges - Control cell

Hi folks, new to the site. Looks pretty good, I'll give this a go.

I have a named range that skips around, ie. "Foundations" is a1:14,a20:a30
I have another named range like "Carpentry" is a31:a40,a50:a60

On my printout page, I want to be able to type in "Foundations" in cell b1 and have all the relevant data show up. Data for the printout mostly includes summing all the "foundations" data from below into one consolidated sheet.

I have found that if I use =sum(indirect(b1)) it evaluates to a #ref.
If I modify my range so that it does not skip around, it evaluates properly.

Does anyone know how to make this work?
I could easily write a macro to do it, but that would tie me to this file for the rest of my life, as I am the only one around who can program.

Hi,

Indirect deals with a text arguemeent, so you'll have to use quotes around your cell reference.

eg

=sum(indirect("b1"))

hth

dave

Cell B1 contains the name of a named range which I want the formula to evaluate. if I put quotes around it (=indirect("b1")) then it just displays the name of the named range... but I want to actually USE the named range in the formula.

The point is so that I can go to B1 and put in my criteria for the report that i'm going to display, and all the below info updates automatically. The criteria I put into B1 also will be the names of named ranges.

Note: (=sum(indirect(b1))) works the way I want, as long as the name of the range in b1 refers to a range that is a continuous block of cells. This fails when the range referred to is separate blocks of cells.

Maybe the attached file will help to demonstrate the issue.

There are 4 named ranges here:
first, second, third, and multiple

I have a theory, but I don't know how to adjust to prove/disprove it.

when you have a range that is split, you identify it with a comma...
eg. a1:a20,a30:a40

So, when the different functions evaluate it the range, the comma moves the function to the next argument of the function, but then the data doesn't line up or make sense to excel.

any ideas for a workaround?