Link to other Worksheets - when data is added updates master works

[Guest]

Hi,
I've got 3 to 4 worksheets that i'd like to lind to a master that is
automatically updated each time i make a change to the support 3 - 4
worksheets. All i'm trying to do is track the number of tests i receive on a
realtime basis. I simply note the receipt on the supporting worksheets with
an X and would like it to show on the master worksheet as a number. for
instance if i've received 35 tests than the 35 X's should appear as the # 35,
and as i add another X it goes up to 36 on the master worksheet.

How can i do this? Regards,

 

[Guest]

Assume the 3 support sheets are named: Sheet1, Sheet2, Sheet3,
and in each sheet, you are recording the receipts "x" in col E, in E1 down

In your master sheet,
you could try in say, A2:
=SUM(COUNTIF(Sheet1!E:E,"x"),COUNTIF(Sheet2!E:E,"x"),COUNTIF(Sheet3!E:E,"x"))
to return the required number

 

[Guest]

Max,

I tried what you suggested but it didn't work; here's what I put
= SUM(COUNTIF(Client Relationships Certification List - Sept 20
2007.xls)Sheet 1!$I$2:$I$50))

Above is what I put in the master sheet referring back to my 1st sheet and I
keep getting an error message.

I type = in my master and then go to the supporting sheet (where the data
resides) to complete the formula and then back to the cell in my master that
I want the real time count to take place - and I keep getting an error.

Thanks.

 

[Guest]

Max,

I tried what you suggested but it didn't work; here's what I put
= SUM(COUNTIF(Client Relationships Certification List - Sept 20
2007.xls)Sheet 1!$I$2:$I$50))

Above is what I put in the master sheet referring back to my 1st sheet and I
keep getting an error message.

I type = in my master and then go to the supporting sheet (where the data
resides) to complete the formula and then back to the cell in my master that
I want the real time count to take place - and I keep getting an error.

Hi Pat.
I believe that Max is right and the file name is wrong.
Try:
=……..([Client Relationships Certification List - Sept 20 2007.xls]Sheet
1!$I$2:$I$50))……..
Regards
Eliano

 

[Guest]

You should reply to my response instead of to your orig. post.

Ok, you didn't say the 3 support sheets were in 3 different workbooks. I
thought you meant all sheets were within the same book. It's more complicated
this way, as COUNTIF won't work with closed books.

If you have to live with this arrangement, then try this alternative using
SUMPRODUCT.

First, open all 3 precedent books together with your book containing your
master sheet. I'll assume the 3 precedent books are named as:

Bk1.xls
Client Relationships Certification List - Sept 20 2007.xls
Bk3.xls

and the target range is E1:E100 in Sheet1 in each of the 3 books
(note that Sheet1 is w/o a space between the "1" and "Sheet")

In your master sheet,

Put instead in say, A2:
=SUMPRODUCT(([Bk1.xls]Sheet1!E1:E100="x")+('[Client Relationships
Certification List - Sept 20
2007.xls]Sheet2'!E1:E100="x")+([Bk3.xls]Sheet3!E1:E100="x"))

The above will return the required result. Save the book. Now you can close
the 3 precedent books, and the formula in A2 will auto-change to reflect the
full paths.


If the 3 precedent books will always be open simultaneously,
you could use:
=SUM(COUNTIF([Bk1.xls]Sheet1!E:E,"x"),COUNTIF('[Client Relationships
Certification List - Sept 20
2007.xls]Sheet2'!E:E,"x"),COUNTIF([Bk3.xls]Sheet3!E:E,"x"))