Formula Referencing data on multiple worksheets

[ChrisPrather]

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?

[Dave F]

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.
--
Brevity is the soul of wit.


"ChrisPrather" wrote:

>
> 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?
>
>
> --
> ChrisPrather
> ------------------------------------------------------------------------
> ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743
> View this thread: http://www.excelforum.com/showthread...hreadid=573836
>
>

[ChrisPrather]

=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?

[Biff]

*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

"ChrisPrather" <[email protected]>
wrote in message
news:[email protected]...
>
> =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?
>
>
> --
> ChrisPrather
> ------------------------------------------------------------------------
> ChrisPrather's Profile:
> http://www.excelforum.com/member.php...o&userid=37743
> View this thread: http://www.excelforum.com/showthread...hreadid=573836
>

[ChrisPrather]

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.

[Biff]

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

"ChrisPrather" <[email protected]>
wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> ChrisPrather
> ------------------------------------------------------------------------
> ChrisPrather's Profile:
> http://www.excelforum.com/member.php...o&userid=37743
> View this thread: http://www.excelforum.com/showthread...hreadid=573836
>

[BOBODD]

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.)

"Biff" wrote:

> 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
>
> "ChrisPrather" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > 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.
> >
> >
> > --
> > ChrisPrather
> > ------------------------------------------------------------------------
> > ChrisPrather's Profile:
> > http://www.excelforum.com/member.php...o&userid=37743
> > View this thread: http://www.excelforum.com/showthread...hreadid=573836
> >
>
>
>