Formula Referencing data on multiple worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisPrather - 21 Aug 2006 18:11 GMT

Does anyone know how to reference data on multiple worksheets or all of
the worksheets in the same workbook? I know you can reference all data
in a given column by using A:A for example. How can you do the same
thing with worksheets?

Signature

ChrisPrather

Reply to this Message

Dave F - 21 Aug 2006 18:27 GMT

enter the formula as you normally would, and when you have to reference
another worksheet, simply select that worksheet, navigate to the cell, select
it, and then go back to your formula.

Signature

Brevity is the soul of wit.

> Does anyone know how to reference data on multiple worksheets or all of
> the worksheets in the same workbook? I know you can reference all data
> in a given column by using A:A for example. How can you do the same
> thing with worksheets?

Reply to this Message

ChrisPrather - 21 Aug 2006 19:53 GMT

=IF(ISNUMBER(MATCH(A2,sheet2!A:A!sheet3!A:A,0)),"Same","New To The
Report")

I don't think you can simply click on the worksheet and proceed as you
indicated. When I do that (not the results above), the formula becomes
invalid due to a syntax error. So, how do you correctly reference
multiple worksheets in the same formula without causing a syntax error?

Signature

ChrisPrather

Reply to this Message

Biff - 21 Aug 2006 21:42 GMT

*EXACTLY* how many sheets do want to include in this?

What are some of their names? Are you *REALLY* using the default sheet
names, Sheet2,Sheet3 ?

do you *REALLY* need to reference the entire column, sheet2!A:A ?

You'll more than likely need a completely different formula.

Biff

> =IF(ISNUMBER(MATCH(A2,sheet2!A:A!sheet3!A:A,0)),"Same","New To The
> Report")
[quoted text clipped - 3 lines]
> invalid due to a syntax error. So, how do you correctly reference
> multiple worksheets in the same formula without causing a syntax error?

Reply to this Message

ChrisPrather - 21 Aug 2006 22:19 GMT

There could be as few as three worksheets or as many as I feel
necessary. It is completely possible that another function may work
better and I wouldn't know because I'm pretty new at this.

Is there something wrong with the sheet1, sheet2, etc.. reference
names? I know they are vanilla, but I don't really mind at this point.
If I could figure out how to reference more than 1 worksheet in a
formula, I would probably change the name of the worksheets to reflect
the date of the data. = )

I reference the entire A column because sometimes there may be more or
less items in that column and I don't really want to have to change the
range each time. Seaching A:A covers any number of items and empty cells
are ignored so it seems to work.

I would totally agree that I may be using a less than efficient formula
due to not knowing better. I really appreciate your help though Biff.

Signature

ChrisPrather

Reply to this Message

Biff - 21 Aug 2006 22:37 GMT

Ok......

Make a list of the sheet names that you want to include in the formula:

H1 = Sheet2
H2 = Sheet3
H3 = Sheet4
H4 = Sheet5

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&H$1:H$4&"'!A:A"),A1)),"Same","New to
report")

> Is there something wrong with the sheet1, sheet2, etc..reference names?

No, not at all! Sometimes you can "build" the references to sheets that have
a sequential naming convention without having to dedicate a range to list
them. (but I wouldn't do it in this case)

> I reference the entire A column because sometimes there may be more or
> less items in that column

Ok, that's fine. But sometimes you might have to make your file as efficient
as possible and referencing large unused portions can cause things to slow
down.

Biff

> There could be as few as three worksheets or as many as I feel
> necessary. It is completely possible that another function may work
[quoted text clipped - 13 lines]
> I would totally agree that I may be using a less than efficient formula
> due to not knowing better. I really appreciate your help though Biff.

Reply to this Message

BOBODD - 22 Aug 2006 05:14 GMT

Hi Chris,

What you're trying to do is called "3D Referencing". Keep in mind that Excel
will only allow you to do this with certain functions. Look up "Refer to the
same cell or range on multiple sheets" in the Excel 2003 help for a list of
formulas that you can do this with (it's quite limited.)

> Ok......
>
[quoted text clipped - 40 lines]
> > I would totally agree that I may be using a less than efficient formula
> > due to not knowing better. I really appreciate your help though Biff.

Reply to this Message