Solved: Named Range Across Sheets

[RECrerar]

Hi,

Is it possible to have a named range that extends across several worksheets and if so how do I define it?

I'm just starting a new project where I have to try and make a workbook as automatically updating as possible when new data/worksheets are added, but as much as possible just using worksheet formulas and the like - not VBA, which is why I am looking at using named ranges, but I don't really have much knowledge of them yet.

The defining of the named range can use VBA but doesn't have to

[rory]

Yes, it is possible. You can just point and click whilst in the RefersTo box to define it (either Ctrl+click, or Shift click multiple sheets and then select a cell). You are still subject to the same 3D reference restrictions in formulas as if you were entering the full reference yourself.

[RECrerar]

Thanks.

What do you mean by 3D reference restrictions?

[rory]

Formulas like SUMIF, for example, don't work across multiple sheets - you can't do:
=SUMIF(Sheet1:Sheet3!A1:A10,"test",Sheet1:Sheet3!B1:B10)
I wasn't sure if you were hoping to use named ranges across multiple sheets to try and get round that (it won't work).

[RECrerar]

Oh, that's quite annoying. So do any of the formulas work across multiple sheets, like for example the simple ones such as =Sum(Test)?

Basically I'm doing error calculations, so I weill have errors from lots of different equipments that will combine to the overall error. The plan is to have each equipment on a seperate sheet (all will be the same format) and then the data from these sheets feed into calculations that combine the indevidual errors to the total and display it on the summary sheet. All that I think I can do, but what I would like is to be able to add a new equipment (sheet) and all the equations automatically update to include the new information. Do you know of a way to do this without using much VBA or should I just give up on that idea?

[rory]

Oh yes - simple ones like SUM work fine! Others you just have to calculate each one separately and then combine the results.
I would have a Start and Finish sheet and make sure all new sheets are added in between. Then any references like Start:Finish!A1:A10 will automatically include any new sheets.

[Shazam]

Post a sample of your workbook including the expected results.

[RECrerar]

I don't have a sample of the workbook yet, at least not one with any calculations in yet, very much at the conceptual stage of the work, I'm just trying to figure out what's possible and what isn't.

I like the idea of using hidden Start and End sheets though. I had a related but different question in Ozgrid and this that is what they selected there as well, So I think I will try that.

Thanks for the advice, I will no doubt be back when I'm putting teh workbook together and eralise it's a lot more complex than at first glance, which is always the way.

Thanks, Robyn