I have a question?

Is there a formula that I can use which references a different "tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have dates in which
clients were admitted to our treatment program. Also on sheet one, I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only". For
example, cell A1 on the QA Data sheet may list the total number of clients in
2007, and another cell may list a break out of diagnoses. Again, the raw data
for this information (name of client, admission date, diagnosis) is on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet askinig to
capture information from another sheet. The only thing I know how to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan

All the formulas in excel can reference a different worksheet or even a
different file. You don't have to learn how to correctly format those
formulas, as Excel will do it for you. Try this: on your SheetB, in a new
cell start to type in a formula such as:

=COUNT(

then click on another sheet and select a range of cells on that sheet.
Excel will insert the sheet name for you in front of that range. Try it
again with a second file open and you will see the format that Excel uses to
reference another sheet in another file.

But you can't do it with a Tab . . . because that is a diet drink from the
early 70's. ;-)

HTH

"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a different "tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have dates in which
clients were admitted to our treatment program. Also on sheet one, I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only". For
example, cell A1 on the QA Data sheet may list the total number of clients in
2007, and another cell may list a break out of diagnoses. Again, the raw data
for this information (name of client, admission date, diagnosis) is on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet askinig to
capture information from another sheet. The only thing I know how to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan

Any excel formula that accepts a range argument should be able to reference
another worksheet.

=COUNTA('New Rules'!A:A) would count the number of entries in new rules
Column A.

=COUNTIF('New Rules'!B:B, "Depression") would count the number of cells
w/"depression" in column B of new rules worksheet.

Easiest to type =COUNTIF( then use your mouse to click on the sheet you
want and select the range (column B in this example). Excel will put in 'New
Rules'!B:B for you, then finish by typing the ", "Depression")" (sans the
extra quotes I just used). That way excel handles the single quote and
exclamation placement (which is probably what you are doing already to link
worksheets). Can also use that method to reference other workbooks in your
formulae.


"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a different "tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have dates in which
clients were admitted to our treatment program. Also on sheet one, I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only". For
example, cell A1 on the QA Data sheet may list the total number of clients in
2007, and another cell may list a break out of diagnoses. Again, the raw data
for this information (name of client, admission date, diagnosis) is on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet askinig to
capture information from another sheet. The only thing I know how to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan

Thanks Andy and JMB!!!

That was very kewl! I'm impressed by the power of Excel. Now that I know if
can do this (develop formulas that can reference data information from a
different tab on my spreadsheet), I wonder if it can handle something a
little more complicated like an "array formula" (as much of my statistical
data works with arrays, as well as the "countif" and "counta" functions).

Two example array formulas I am using (whose numerical results I'd like to
be referenced on a different worksheet tab) are the following:

REFERENCES DATA BY A SPECIFIC YEAR

=SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC
Initiated","Completed Tx-File Closed"}))

REFERENCES DATA BY A SPECIFIC MONTH IN A YEAR

=SUM(--(TEXT(Z4:Z3500,"mmm yy")="Jan 07"))*(AN4:AN3500={"Referred to Tx","AC
Initiated","Completed Tx-File Closed"})

Best,

Dan



"JMB" wrote:

Any excel formula that accepts a range argument should be able to reference
another worksheet.

=COUNTA('New Rules'!A:A) would count the number of entries in new rules
Column A.

=COUNTIF('New Rules'!B:B, "Depression") would count the number of cells
w/"depression" in column B of new rules worksheet.

Easiest to type =COUNTIF( then use your mouse to click on the sheet you
want and select the range (column B in this example). Excel will put in 'New
Rules'!B:B for you, then finish by typing the ", "Depression")" (sans the
extra quotes I just used). That way excel handles the single quote and
exclamation placement (which is probably what you are doing already to link
worksheets). Can also use that method to reference other workbooks in your
formulae.


"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a different "tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have dates in which
clients were admitted to our treatment program. Also on sheet one, I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only". For
example, cell A1 on the QA Data sheet may list the total number of clients in
2007, and another cell may list a break out of diagnoses. Again, the raw data
for this information (name of client, admission date, diagnosis) is on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet askinig to
capture information from another sheet. The only thing I know how to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan

Hi

Try
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*(ISNUMBER(FIND({ "Referred to Tx","AC
Initiated","Completed Tx-File Closed"},AN4:AN3500))))

and
=SUMPRODUCT((TEXT(Z4:Z3500,mmm yy)="Jan 07")*(ISNUMBER(FIND({"Referred
to Tx","AC Initiated","Completed Tx-File Closed"},AN4:AN3500))))
--
Regards

Roger Govier


"Dan the Man" wrote in message
...
Thanks Andy and JMB!!!

That was very kewl! I'm impressed by the power of Excel. Now that I
know if
can do this (develop formulas that can reference data information from
a
different tab on my spreadsheet), I wonder if it can handle something
a
little more complicated like an "array formula" (as much of my
statistical
data works with arrays, as well as the "countif" and "counta"
functions).

Two example array formulas I am using (whose numerical results I'd
like to
be referenced on a different worksheet tab) are the following:

REFERENCES DATA BY A SPECIFIC YEAR

=SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC
Initiated","Completed Tx-File Closed"}))

REFERENCES DATA BY A SPECIFIC MONTH IN A YEAR

=SUM(--(TEXT(Z4:Z3500,"mmm yy")="Jan 07"))*(AN4:AN3500={"Referred to
Tx","AC
Initiated","Completed Tx-File Closed"})

Best,

Dan



"JMB" wrote:

Any excel formula that accepts a range argument should be able to
reference
another worksheet.

=COUNTA('New Rules'!A:A) would count the number of entries in new
rules
Column A.

=COUNTIF('New Rules'!B:B, "Depression") would count the number of
cells
w/"depression" in column B of new rules worksheet.

Easiest to type =COUNTIF( then use your mouse to click on the
sheet you
want and select the range (column B in this example). Excel will put
in 'New
Rules'!B:B for you, then finish by typing the ", "Depression")" (sans
the
extra quotes I just used). That way excel handles the single quote
and
exclamation placement (which is probably what you are doing already
to link
worksheets). Can also use that method to reference other workbooks
in your
formulae.


"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a different
"tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have dates
in which
clients were admitted to our treatment program. Also on sheet one,
I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only".
For
example, cell A1 on the QA Data sheet may list the total number of
clients in
2007, and another cell may list a break out of diagnoses. Again,
the raw data
for this information (name of client, admission date, diagnosis) is
on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the
various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet
askinig to
capture information from another sheet. The only thing I know how
to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan

Roger, could you give me a little more info. I plugged in your formula but it
didn't seem to work. How would I use your formula examples to reference
between tabs? For example, I would be putting the data results from the "New
Rules Tab" onto my "QA Tab". The array formula examples you gave me, would be
used to do this, but I didn't know what to input to directly reference that I
was pulling the data from the "New Rules Tab" and placing it on the "QA Tab"
(in the same spreadsheet). I got the other formula from Andy and JMB to
work, but I couldn't make the array examples you provided. Any additional
clarity would be greatly appreciated.

Thanks,

Dan

"Roger Govier" wrote:

Hi

Try
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*(ISNUMBER(FIND({ "Referred to Tx","AC
Initiated","Completed Tx-File Closed"},AN4:AN3500))))

and
=SUMPRODUCT((TEXT(Z4:Z3500,mmm yy)="Jan 07")*(ISNUMBER(FIND({"Referred
to Tx","AC Initiated","Completed Tx-File Closed"},AN4:AN3500))))
--
Regards

Roger Govier


"Dan the Man" wrote in message
...
Thanks Andy and JMB!!!

That was very kewl! I'm impressed by the power of Excel. Now that I
know if
can do this (develop formulas that can reference data information from
a
different tab on my spreadsheet), I wonder if it can handle something
a
little more complicated like an "array formula" (as much of my
statistical
data works with arrays, as well as the "countif" and "counta"
functions).

Two example array formulas I am using (whose numerical results I'd
like to
be referenced on a different worksheet tab) are the following:

REFERENCES DATA BY A SPECIFIC YEAR

=SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC
Initiated","Completed Tx-File Closed"}))

REFERENCES DATA BY A SPECIFIC MONTH IN A YEAR

=SUM(--(TEXT(Z4:Z3500,"mmm yy")="Jan 07"))*(AN4:AN3500={"Referred to
Tx","AC
Initiated","Completed Tx-File Closed"})

Best,

Dan



"JMB" wrote:

Any excel formula that accepts a range argument should be able to
reference
another worksheet.

=COUNTA('New Rules'!A:A) would count the number of entries in new
rules
Column A.

=COUNTIF('New Rules'!B:B, "Depression") would count the number of
cells
w/"depression" in column B of new rules worksheet.

Easiest to type =COUNTIF( then use your mouse to click on the
sheet you
want and select the range (column B in this example). Excel will put
in 'New
Rules'!B:B for you, then finish by typing the ", "Depression")" (sans
the
extra quotes I just used). That way excel handles the single quote
and
exclamation placement (which is probably what you are doing already
to link
worksheets). Can also use that method to reference other workbooks
in your
formulae.


"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a different
"tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have dates
in which
clients were admitted to our treatment program. Also on sheet one,
I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only".
For
example, cell A1 on the QA Data sheet may list the total number of
clients in
2007, and another cell may list a break out of diagnoses. Again,
the raw data
for this information (name of client, admission date, diagnosis) is
on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the
various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet
askinig to
capture information from another sheet. The only thing I know how
to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan

Hi Dan

Just insert the tab name in front of the data ranges
Because the tab name has spaces, it must be enclosed in single quotes,
and followed by the usual exclamation mark
'New Rules Tab'!

=SUMPRODUCT(('New Rules Tab'!YEAR(Z4:Z3500)=2007)*
(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File
Closed"},'New Rules Tab'!AN4:AN3500))))


--
Regards

Roger Govier


"Dan the Man" wrote in message
...
Roger, could you give me a little more info. I plugged in your formula
but it
didn't seem to work. How would I use your formula examples to
reference
between tabs? For example, I would be putting the data results from
the "New
Rules Tab" onto my "QA Tab". The array formula examples you gave me,
would be
used to do this, but I didn't know what to input to directly reference
that I
was pulling the data from the "New Rules Tab" and placing it on the
"QA Tab"
(in the same spreadsheet). I got the other formula from Andy and JMB
to
work, but I couldn't make the array examples you provided. Any
additional
clarity would be greatly appreciated.

Thanks,

Dan

"Roger Govier" wrote:

Hi

Try
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*(ISNUMBER(FIND({ "Referred to
Tx","AC
Initiated","Completed Tx-File Closed"},AN4:AN3500))))

and
=SUMPRODUCT((TEXT(Z4:Z3500,mmm yy)="Jan
07")*(ISNUMBER(FIND({"Referred
to Tx","AC Initiated","Completed Tx-File Closed"},AN4:AN3500))))
--
Regards

Roger Govier


"Dan the Man" wrote in message
...
Thanks Andy and JMB!!!

That was very kewl! I'm impressed by the power of Excel. Now that I
know if
can do this (develop formulas that can reference data information
from
a
different tab on my spreadsheet), I wonder if it can handle
something
a
little more complicated like an "array formula" (as much of my
statistical
data works with arrays, as well as the "countif" and "counta"
functions).

Two example array formulas I am using (whose numerical results I'd
like to
be referenced on a different worksheet tab) are the following:

REFERENCES DATA BY A SPECIFIC YEAR

=SUM((YEAR(Z4:Z3500)=2007)*(AN4:AN3500={"Referred to Tx","AC
Initiated","Completed Tx-File Closed"}))

REFERENCES DATA BY A SPECIFIC MONTH IN A YEAR

=SUM(--(TEXT(Z4:Z3500,"mmm yy")="Jan 07"))*(AN4:AN3500={"Referred
to
Tx","AC
Initiated","Completed Tx-File Closed"})

Best,

Dan



"JMB" wrote:

Any excel formula that accepts a range argument should be able to
reference
another worksheet.

=COUNTA('New Rules'!A:A) would count the number of entries in new
rules
Column A.

=COUNTIF('New Rules'!B:B, "Depression") would count the number of
cells
w/"depression" in column B of new rules worksheet.

Easiest to type =COUNTIF( then use your mouse to click on the
sheet you
want and select the range (column B in this example). Excel will
put
in 'New
Rules'!B:B for you, then finish by typing the ", "Depression")"
(sans
the
extra quotes I just used). That way excel handles the single
quote
and
exclamation placement (which is probably what you are doing
already
to link
worksheets). Can also use that method to reference other
workbooks
in your
formulae.


"Dan the Man" wrote:

I have a question?

Is there a formula that I can use which references a different
"tab" on my
spreadsheet.

For example if on sheet 1 (which of named New Rules) I have
dates
in which
clients were admitted to our treatment program. Also on sheet
one,
I list
their diagnoses (e.g depression, anxiety, etc).

On sheet 2 (which I nammed QA Data) I want to list "stats only".
For
example, cell A1 on the QA Data sheet may list the total number
of
clients in
2007, and another cell may list a break out of diagnoses. Again,
the raw data
for this information (name of client, admission date, diagnosis)
is
on sheet
1 (New Rules), but I want to use sheet 2 (QA Data) to tally the
various stats
that I desire (a tab dedicated to stas).

I just didn't know if you can write Excel formula on one sheet
askinig to
capture information from another sheet. The only thing I know
how
to do is to
"link data" between sheets, but I don't want to do that.

Thanks for any suggestions!

Dan