Formula Referencing data on multiple worksheets

C

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

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

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

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]...
 
C

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

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?
Click to expand...

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
Click to expand...

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
 
B

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