Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreadsheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreadsheet
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreadsheet
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Thanks Roger. I still seem to be having difficulty. Perhaps I was
understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hi Dan
Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hey Roger!
I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hi Dan
Sorry, I pasted one of the sheet references to the wrong place. Obvioulsy Year has to sit outside of the data range Try =SUMPRODUCT((YEAR('New rules Sample'!Z4:Z10)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New rules Sample'!AN4:AN10)))) -- Regards Roger Govier "Dan the Man" wrote in message ... Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
I think Roger put the sheet name in the wrong place in his earlier
posting. Try this: =SUMPRODUCT((YEAR('New Rules Sample'! Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'! AN4:AN3500)))) Hope this helps. Pete On Jul 6, 1:24 pm, Dan the Man wrote: Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
You beat me to it again - you really are ahead of yourself today !!
<bg Pete On Jul 6, 1:50 pm, "Roger Govier" wrote: Hi Dan Sorry, I pasted one of the sheet references to the wrong place. Obvioulsy Year has to sit outside of the data range Try =SUMPRODUCT((YEAR('New rules Sample'!Z4:Z10)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New rules Sample'!AN4:AN10)))) -- Regards Roger Govier "Dan the Man" wrote in ... Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hey Roger and Peter!
You are both great. I'm moving forward here. The formulas you offered (both Roger's revision, and Pete's) did take in the appropriate tab (Old Rules Sample) where my quality assurance data lives, however the numerical reading was 0. I tested the formula (I test everything) for functionality, and set up it up to offer a numerical value of 4 (four clients "Referred to Tx" in Row AN, and within the 2007 date parameters of the formula per Row Z). I'm sorry to be a pain. I'd be glad to send someone my spreadsheet, but I didn't want to send extra work to anyone beyond this wonderful help you've been offering. I'm off for an hour with a client, so I'll check back to see if Roger or Pete have responded after my session. Eternally Greatful, Dan "Pete_UK" wrote: I think Roger put the sheet name in the wrong place in his earlier posting. Try this: =SUMPRODUCT((YEAR('New Rules Sample'! Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'! AN4:AN3500)))) Hope this helps. Pete On Jul 6, 1:24 pm, Dan the Man wrote: Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hi Dan
It should have worked OK. By all means send me the file and I will take a look. Send to roger at technologyNOSPAM4u.co.uk Do the obvious with "at" and remove NOSPAM -- Regards Roger Govier "Dan the Man" wrote in message ... Hey Roger and Peter! You are both great. I'm moving forward here. The formulas you offered (both Roger's revision, and Pete's) did take in the appropriate tab (Old Rules Sample) where my quality assurance data lives, however the numerical reading was 0. I tested the formula (I test everything) for functionality, and set up it up to offer a numerical value of 4 (four clients "Referred to Tx" in Row AN, and within the 2007 date parameters of the formula per Row Z). I'm sorry to be a pain. I'd be glad to send someone my spreadsheet, but I didn't want to send extra work to anyone beyond this wonderful help you've been offering. I'm off for an hour with a client, so I'll check back to see if Roger or Pete have responded after my session. Eternally Greatful, Dan "Pete_UK" wrote: I think Roger put the sheet name in the wrong place in his earlier posting. Try this: =SUMPRODUCT((YEAR('New Rules Sample'! Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'! AN4:AN3500)))) Hope this helps. Pete On Jul 6, 1:24 pm, Dan the Man wrote: Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hi Dan
File arrived and response sent back by private email For the benefit of the NG, the problem was testing "Referred to Tx" against "Referred to TX" FIND() is case sensitive. Changing to using SEARCH instead resolves the problem. =SUMPRODUCT((YEAR('New Rules Sample'!Z4:Z3500)=2007)* (ISNUMBER(SEARCH({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Dan It should have worked OK. By all means send me the file and I will take a look. Send to roger at technologyNOSPAM4u.co.uk Do the obvious with "at" and remove NOSPAM -- Regards Roger Govier "Dan the Man" wrote in message ... Hey Roger and Peter! You are both great. I'm moving forward here. The formulas you offered (both Roger's revision, and Pete's) did take in the appropriate tab (Old Rules Sample) where my quality assurance data lives, however the numerical reading was 0. I tested the formula (I test everything) for functionality, and set up it up to offer a numerical value of 4 (four clients "Referred to Tx" in Row AN, and within the 2007 date parameters of the formula per Row Z). I'm sorry to be a pain. I'd be glad to send someone my spreadsheet, but I didn't want to send extra work to anyone beyond this wonderful help you've been offering. I'm off for an hour with a client, so I'll check back to see if Roger or Pete have responded after my session. Eternally Greatful, Dan "Pete_UK" wrote: I think Roger put the sheet name in the wrong place in his earlier posting. Try this: =SUMPRODUCT((YEAR('New Rules Sample'! Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'! AN4:AN3500)))) Hope this helps. Pete On Jul 6, 1:24 pm, Dan the Man wrote: Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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- Hide quoted text - - Show quoted text - |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
Hi Roger!
Also sent you a response via private email. Very odd, that "Not Referred to TX" is registering and counting when it is not included in the search paramters. I would think that only "Referred to TX, AC Initiated", and "Completed TX-File Closed" (when a date in Row Z is within 2007) would register. Odd that "Not Referred to TX" is registering as well (especially since that indicie is NOT included in the formula). Dan "Roger Govier" wrote: Hi Dan File arrived and response sent back by private email For the benefit of the NG, the problem was testing "Referred to Tx" against "Referred to TX" FIND() is case sensitive. Changing to using SEARCH instead resolves the problem. =SUMPRODUCT((YEAR('New Rules Sample'!Z4:Z3500)=2007)* (ISNUMBER(SEARCH({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Dan It should have worked OK. By all means send me the file and I will take a look. Send to roger at technologyNOSPAM4u.co.uk Do the obvious with "at" and remove NOSPAM -- Regards Roger Govier "Dan the Man" wrote in message ... Hey Roger and Peter! You are both great. I'm moving forward here. The formulas you offered (both Roger's revision, and Pete's) did take in the appropriate tab (Old Rules Sample) where my quality assurance data lives, however the numerical reading was 0. I tested the formula (I test everything) for functionality, and set up it up to offer a numerical value of 4 (four clients "Referred to Tx" in Row AN, and within the 2007 date parameters of the formula per Row Z). I'm sorry to be a pain. I'd be glad to send someone my spreadsheet, but I didn't want to send extra work to anyone beyond this wonderful help you've been offering. I'm off for an hour with a client, so I'll check back to see if Roger or Pete have responded after my session. Eternally Greatful, Dan "Pete_UK" wrote: I think Roger put the sheet name in the wrong place in his earlier posting. Try this: =SUMPRODUCT((YEAR('New Rules Sample'! Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'! AN4:AN3500)))) Hope this helps. Pete On Jul 6, 1:24 pm, Dan the Man wrote: Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a formula to capture data between "Tabs" on a spreads
The phrase "Referred to Tx" is contained within the phrase "Not
Referred to TX", so it will be counted. You might want to look up SEARCH and EXACT in XL help if you want to refine the count further. Hope this helps. Pete On Jul 6, 8:40 pm, Dan the Man wrote: Hi Roger! Also sent you a response via private email. Very odd, that "Not Referred to TX" is registering and counting when it is not included in the search paramters. I would think that only "Referred to TX, AC Initiated", and "Completed TX-File Closed" (when a date in Row Z is within 2007) would register. Odd that "Not Referred to TX" is registering as well (especially since that indicie is NOT included in the formula). Dan "Roger Govier" wrote: Hi Dan File arrived and response sent back by private email For the benefit of the NG, the problem was testing "Referred to Tx" against "Referred to TX" FIND() is case sensitive. Changing to using SEARCH instead resolves the problem. =SUMPRODUCT((YEAR('New Rules Sample'!Z4:Z3500)=2007)* (ISNUMBER(SEARCH({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Dan It should have worked OK. By all means send me the file and I will take a look. Send to roger at technologyNOSPAM4u.co.uk Do the obvious with "at" and remove NOSPAM -- Regards Roger Govier "Dan the Man" wrote in message ... Hey Roger and Peter! You are both great. I'm moving forward here. The formulas you offered (both Roger's revision, and Pete's) did take in the appropriate tab (Old Rules Sample) where my quality assurance data lives, however the numerical reading was 0. I tested the formula (I test everything) for functionality, and set up it up to offer a numerical value of 4 (four clients "Referred to Tx" in Row AN, and within the 2007 date parameters of the formula per Row Z). I'm sorry to be a pain. I'd be glad to send someone my spreadsheet, but I didn't want to send extra work to anyone beyond this wonderful help you've been offering. I'm off for an hour with a client, so I'll check back to see if Roger or Pete have responded after my session. Eternally Greatful, Dan "Pete_UK" wrote: I think Roger put the sheet name in the wrong place in his earlier posting. Try this: =SUMPRODUCT((YEAR('New Rules Sample'! Z4:Z3500)=2007)*(ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'! AN4:AN3500)))) Hope this helps. Pete On Jul 6, 1:24 pm, Dan the Man wrote: Hey Roger! I did do what you suggested as my initial try (smily cut and pasted your example), however I got a formula error message. I did accidently give you the wrong name for the tab however (very sorry about that), and the actual name is "New Rules Sample" (versus New Rules Tab). I tried to fix that after I cut and pasted your example, but alas not luck. Here is what I cut and pasted: =SUMPRODUCT(('New Rules Sample'!YEAR(Z4:Z3500)=2007)* (ISNUMBER(FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Sample'!AN4:AN3500)))) Using a regular countif( -or- counta( formula worked just fine when I did it, but my spreadsheet didn't seem to like the above Array calculation. Excel 2002 is so tempremental (or possibly just user error on my part, lol). Thanks Roger and Have a good Friday! Dan "Roger Govier" wrote: Hi Dan Why not just copy and paste the formula I gave you? What you have entered has the wrong syntax. -- Regards Roger Govier "Dan the Man" wrote in message ... Thanks Roger. I still seem to be having difficulty. Perhaps I was understanding you wrong (or perhaps I haven't had my coffee yet, lol), but it still isn't seeming to work. I am getting the infamous: #value! error. Any additional clarity you could provide would be greatly appreciated. Below is what I did: =SUMPRODUCT(('New Rules Sample'!AN4:AN3500)*YEAR(Z4:Z3500)=2007)*ISNUMBER( FIND({"Referred to Tx","AC Initiated","Completed Tx-File Closed"},'New Rules Tab'!AN4:AN3500)) Thanks! Dan "Roger Govier" wrote: 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 ... read more »- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
When I type "13" the spreadsheet shows "14." Possibilities? | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
pictures to work with "data" "sort" option | Excel Discussion (Misc queries) |