up vote 1 down vote favorite
1
share [g+] share [fb]

I need to create a consolidate range consisting of ranges spread across multiple worksheets in Excel 2007. What I wish I could do is to use the VBA UNION function in a cell formula, but that doesn't seem to be an option.

I can handle VBA if necessary, but there's no telling if someone who would have to maintain this in the future would be able to.

My ultimate goal is to run functions such as MAX, MIN, AVERAGE, etc across the consolidated range.

Any help out there?

link|improve this question
feedback

2 Answers

up vote 1 down vote accepted

You can use a comma seperated list of ranges as a parameter to functions, eg

=MIN(Sheet1!A1:C10,Sheet2!B7:D11)

of using named ranges

=MIN(NameOnSheet1,NameOnSheet2)

or a 3D reference to refer to the same cells on a contiguous range of sheets, eg

=MIN(Sheet1:Sheet4!A1:C10)
link|improve this answer
That load scream you just heard was me being hit by the clue-by-four. Thanks for the help! – Jason Oct 26 '11 at 12:34
feedback
up vote 0 down vote

One way to do this is to use a named range.

If you highlight area 1 then while holding Ctrl highlight area 2.... both should appear to be selected.

now go to forumlas -> define name and give your new range a name (ie SuperRange)

Now to get the max you could enter

=max(SuperRange)

to get the result

link|improve this answer
-1 MIN, MAX, AVERAGE do not work with this type of range – chris neilsen Oct 26 '11 at 6:27
Yes they do, why do you think they don't – Pynner Oct 26 '11 at 14:02
It would work if SuperRange was defined as a contigious range (Sheet1:Sheet4!A1:C10). It would fail if it was defined as Sheet1!B1:D10,Sheet2!C7:E11 – brettdj Oct 27 '11 at 22:46
@brettdj The named range is defined as you decribed.... but I didn't get any error messages, and the calculations returned the correct answer. I tested this before posting my answer. For the sake of my sanity could you elaborate what and how this solution doesn't work? – Pynner Oct 28 '11 at 1:23
@Pynner, I tried both examples in xl03 and xl10, the contiguous range worked, the later didn't. Which didn't surprise me, xl is often flakey in 3D. Is the later approach working for you? The later example does of course work if MIN is in the range name itself, ie SuperUser is defined as =MIN(Sheet1!G9:I18,Sheet2!H15:J19) – brettdj Oct 28 '11 at 2:16
show 2 more comments
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.