Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Link to other Worksheets - when data is added updates master works
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, -- Pat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Link to other Worksheets - when data is added updates master works
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Pat" wrote: 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, -- Pat |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Link to other Worksheets - when data is added updates master works
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. -- Pat "Pat" wrote: 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, -- Pat |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Link to other Worksheets - when data is added updates master w
"Pat" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Link to other Worksheets - when data is added updates master w
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")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Pat" wrote: 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. -- Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I generate lists in worksheets based on a master list works | Excel Worksheet Functions | |||
Link Worksheets Into Master | Excel Discussion (Misc queries) | |||
Link different worksheets in different workbooks to 1 master workb | Excel Discussion (Misc queries) | |||
Link Worksheets to a Master Sheet | Excel Worksheet Functions | |||
how do i link a number of worksheets to one master worksheet? | Excel Worksheet Functions |