I have a workbook with 10 sheets with data broken out by region in the US.
But, I'd like to take the data from those sheets and compile them on one
"master" sheet within the workbook, that would have cell references to the
data in each region's sheet. So the master sheet is for viewing only. All
editing of data is in the individual region sheets.

Here's my question(s):
- Is there code that can lookup cells from an individual sheet and place
them on the master as a reference?

- Can it be done with a named range of cells from the region sheet?

Sorry, I'm still a novice at VBA, and trying to figure it out, but have no
idea how to tackle this.

Thanks in advance.
Brad

Brad,

If you have each region sheet in exactly the same format, you wouldn't need
VBA. On the summary sheet just refer to the region sheets, like this

='Pacific Northwest'!A1

and other cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brad Zenner" wrote in message
...
I have a workbook with 10 sheets with data broken out by region in the US.
But, I'd like to take the data from those sheets and compile them on one
"master" sheet within the workbook, that would have cell references to the
data in each region's sheet. So the master sheet is for viewing only. All
editing of data is in the individual region sheets.

Here's my question(s):
- Is there code that can lookup cells from an individual sheet and place
them on the master as a reference?

- Can it be done with a named range of cells from the region sheet?

Sorry, I'm still a novice at VBA, and trying to figure it out, but have no
idea how to tackle this.

Thanks in advance.
Brad

Okay Bob, got that...Is it possible to do that with a named cell range of 5
rows by 6 columns, to have references for all of those 30 cells at once? Or
is the autofill my only alternative...
BZ



On 8/15/05 2:20 PM, in article , "Bob
Phillips" wrote:

Brad,

If you have each region sheet in exactly the same format, you wouldn't need
VBA. On the summary sheet just refer to the region sheets, like this

='Pacific Northwest'!A1

and other cells.

Drag and copy isn't so bad, if the cells on the region sheets are
contiguous, adding references to the summary would be simple.

I am not sure I understand the statement ... Is it possible to do that with
a named cell range of 5
rows by 6 columns, to have references for all of those 30 cells at once?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brad Zenner" wrote in message
...
Okay Bob, got that...Is it possible to do that with a named cell range of
5
rows by 6 columns, to have references for all of those 30 cells at once?
Or
is the autofill my only alternative...
BZ



On 8/15/05 2:20 PM, in article , "Bob
Phillips" wrote:

Brad,

If you have each region sheet in exactly the same format, you wouldn't
need
VBA. On the summary sheet just refer to the region sheets, like this

='Pacific Northwest'!A1

and other cells.

Sorry, the example was, I have data in a range of cells 6 columns wide by 5
rows (30 cells total), that I created a named range for, let's call that
range "numbers" for example.

Is there a formula that on the master sheet, instead of referring to a
specific cell, I could refer to a range of cells? For example, in cell A1,
could I write:

=Sheet2!"numbers" ? Would that then populate the 30 cells on the master
sheet?

Does that make sense?
Thanks again Bob, sorry I'm not explaining better
BZ



On 8/15/05 6:24 PM, in article , "Bob
Phillips" wrote:

Drag and copy isn't so bad, if the cells on the region sheets are
contiguous, adding references to the summary would be simple.

I am not sure I understand the statement ... Is it possible to do that with
a named cell range of 5
rows by 6 columns, to have references for all of those 30 cells at once?

I see.

If you select the same size array in the summary sheet, hit F2, type
=numbers in the formula bar and then commit with Ctrl-Shift-Enter (as an
array formula).

the range name applies to the whole workbook, not just the sheet, unless you
create it as a worksheet name by preceding with the sheet name in the names
definition.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brad Zenner" wrote in message
...
Sorry, the example was, I have data in a range of cells 6 columns wide by
5
rows (30 cells total), that I created a named range for, let's call that
range "numbers" for example.

Is there a formula that on the master sheet, instead of referring to a
specific cell, I could refer to a range of cells? For example, in cell A1,
could I write:

=Sheet2!"numbers" ? Would that then populate the 30 cells on the master
sheet?

Does that make sense?
Thanks again Bob, sorry I'm not explaining better
BZ



On 8/15/05 6:24 PM, in article , "Bob
Phillips" wrote:

Drag and copy isn't so bad, if the cells on the region sheets are
contiguous, adding references to the summary would be simple.

I am not sure I understand the statement ... Is it possible to do that
with
a named cell range of 5
rows by 6 columns, to have references for all of those 30 cells at
once?