| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help please-SUMPRODUCT and Dynamic Range
I have two sheets, for short story purpose, we'll call them SheetA and
SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide
=OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) Perhaps ....if it is for column D =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) Check ADMDAY as well =OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B) ,1) Perhaps ....if it is for column B =OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B) ,1) HTH "Tasha" wrote: I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
I will try that, thanks....and will let you know if it works. Can you please
explain to me the steps in the formula? I have looked in Help, I have also read on the internet, but thinking I am not understanding the 'Offset' part of the columns and rows?? "Toppers" wrote: Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide =OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) Perhaps ....if it is for column D =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) Check ADMDAY as well =OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B) ,1) Perhaps ....if it is for column B =OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B) ,1) HTH "Tasha" wrote: I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
Note that offset does not work if the workbook is closed
-- Regards, Peo Sjoblom "Tasha" wrote in message ... I will try that, thanks....and will let you know if it works. Can you please explain to me the steps in the formula? I have looked in Help, I have also read on the internet, but thinking I am not understanding the 'Offset' part of the columns and rows?? "Toppers" wrote: Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide =OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) Perhaps ....if it is for column D =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) Check ADMDAY as well =OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B) ,1) Perhaps ....if it is for column B =OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B) ,1) HTH "Tasha" wrote: I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
even if the named ranges specify the path to SheetA? When I close the
workbook that has SheetA in it, in SheetB, in my calculated fields, the path shows up instead of just the worksheet name??? "Peo Sjoblom" wrote: Note that offset does not work if the workbook is closed -- Regards, Peo Sjoblom "Tasha" wrote in message ... I will try that, thanks....and will let you know if it works. Can you please explain to me the steps in the formula? I have looked in Help, I have also read on the internet, but thinking I am not understanding the 'Offset' part of the columns and rows?? "Toppers" wrote: Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide =OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) Perhaps ....if it is for column D =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) Check ADMDAY as well =OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B) ,1) Perhaps ....if it is for column B =OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B) ,1) HTH "Tasha" wrote: I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
=OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1)
The reference cell from which your offsetting is $D$2. If you are only interested in a single column , in this case D, then the next two paramters are set to 0 i.e. 0 rows and 0 columns from Dd2 (D2!). The COUNTA is the number of rows (height) of the column FROM the offset and if you start from D2 you should subtract 1 from this, as it will include the header. The 1 is width (number of columns) so ! in this case. =OFFSET(dlywrksht!$D$1,0,0,COUNTA(dly wrksht!$D:$D),1) OR =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D)-1,1) HTH "Tasha" wrote: I will try that, thanks....and will let you know if it works. Can you please explain to me the steps in the formula? I have looked in Help, I have also read on the internet, but thinking I am not understanding the 'Offset' part of the columns and rows?? "Toppers" wrote: Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide =OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) Perhaps ....if it is for column D =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) Check ADMDAY as well =OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B) ,1) Perhaps ....if it is for column B =OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B) ,1) HTH "Tasha" wrote: I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
this is my updated formula, still not pulling anything, getting 0 in all
results??? I guess that is better than an error, but not much :) =SUMPRODUCT('SheetA.xls'!PHYNO2=B$3)*(SheetA.xls'! ADMDAY2=$D$2) my named ranges are working though, when I do F5 and type in the name, it selects the column or cells I named. Then when I save SheetA.xls and close it, in SheetB, I get an error that the named ranges don't exists , even when I try to Update the Values in the link. "Toppers" wrote: =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) The reference cell from which your offsetting is $D$2. If you are only interested in a single column , in this case D, then the next two paramters are set to 0 i.e. 0 rows and 0 columns from Dd2 (D2!). The COUNTA is the number of rows (height) of the column FROM the offset and if you start from D2 you should subtract 1 from this, as it will include the header. The 1 is width (number of columns) so ! in this case. =OFFSET(dlywrksht!$D$1,0,0,COUNTA(dly wrksht!$D:$D),1) OR =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D)-1,1) HTH "Tasha" wrote: I will try that, thanks....and will let you know if it works. Can you please explain to me the steps in the formula? I have looked in Help, I have also read on the internet, but thinking I am not understanding the 'Offset' part of the columns and rows?? "Toppers" wrote: Named range PHYNO is offset 3 columns i.e is column G and is 4 columns wide =OFFSET(dlywrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) Perhaps ....if it is for column D =OFFSET(dlywrksht!$D$2,0,0,COUNTA(dly wrksht!$D:$D),1) Check ADMDAY as well =OFFSET(dlywrksht!$B$2,1,1,COUNTA(dlywrksht!$B:$B) ,1) Perhaps ....if it is for column B =OFFSET(dlywrksht!$B$2,0,0,COUNTA(dlywrksht!$B:$B) ,1) HTH "Tasha" wrote: I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
Change the name definitions to
ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1) PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1) and add another PATCNT: =OFFSET('dly wrksht'!$A$1,0,2,COUNTA('dly wrksht'!$B:$B),1) then use a formula in D2 of =SUMPRODUCT((PHYNO=$B2)*(ADMDAY=E$1),PATCNT) and copy across and down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tasha" wrote in message ... I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
"Bob Phillips" wrote...
Change the name definitions to ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1) PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1) .... But the 'dly wrksht' worksheet is in a different, apparently closed workbook. Excel returns #REF! for names defined as formulas in closed workbooks. Note the OP's specs: "this is the formula for each name... ADMDAY=OFFSET('dly wrksht'!$B$2,1,1,COUNTA('dly wrksht'!$B:$B),1) and PHYNO=OFFSET('dly wrksht'!$D$2,1,3,COUNTA('dly wrksht'!$D:$D),4)" [single quotes added] There's no robust way to do this unless the two COUNTA calls return the same result. Otherwise, the two derived ranges could have different numbers of rows, and the different 5th args to OFFSET ensure they have different numbers of columns. I'll assume the two COUNTA calls return the same result. In the workbook that needs to refer to this other workbook define the names ADMDAY: ='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$C$3:$C$1002 PHYNO: ='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$G$3:$J$1002 N: =COUNTA('H:\ExcelDocs\[SheetA.xls]dly wrksht'!$B:$B) S: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1000,1)) and use a formula like =SUMPRODUCT((PHYNO=B3)*(ADMDAY=D2)*(S<=N)) Adjust the bottom row index as needed, but don't use more rows than needed. References to large ranges in other, closed workbooks can really slow things down and eat memory. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
I ended up using the SUMPRODUCT formula Bob gave me on the 16th with the
specific cell references, and then I edited the range names by inserting $B:B$ and the same for D. Oh, and I also copied the spreadsheet I was using seperately, in with the workbook my summary sheet was on, that way it would be open when I was updating it. Seems to be working, am going to keep an eye on it for now and make sure it is totalling correctly, but so far, so good!!! THANKS AGAIN!!! so much for your help!!! "Harlan Grove" wrote: "Bob Phillips" wrote... Change the name definitions to ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1) PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1) .... But the 'dly wrksht' worksheet is in a different, apparently closed workbook. Excel returns #REF! for names defined as formulas in closed workbooks. Note the OP's specs: "this is the formula for each name... ADMDAY=OFFSET('dly wrksht'!$B$2,1,1,COUNTA('dly wrksht'!$B:$B),1) and PHYNO=OFFSET('dly wrksht'!$D$2,1,3,COUNTA('dly wrksht'!$D:$D),4)" [single quotes added] There's no robust way to do this unless the two COUNTA calls return the same result. Otherwise, the two derived ranges could have different numbers of rows, and the different 5th args to OFFSET ensure they have different numbers of columns. I'll assume the two COUNTA calls return the same result. In the workbook that needs to refer to this other workbook define the names ADMDAY: ='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$C$3:$C$1002 PHYNO: ='H:\ExcelDocs\[SheetA.xls]dly wrksht'!$G$3:$J$1002 N: =COUNTA('H:\ExcelDocs\[SheetA.xls]dly wrksht'!$B:$B) S: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1000,1)) and use a formula like =SUMPRODUCT((PHYNO=B3)*(ADMDAY=D2)*(S<=N)) Adjust the bottom row index as needed, but don't use more rows than needed. References to large ranges in other, closed workbooks can really slow things down and eat memory. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
Thank you for all your help Bob!!! I ended up using the SUMPRODUCT formula
you gave me on the 16th with the specific cell references, and then I edited the range names by inserting $B:B$ and the same for D. Seems to be working, am going to keep an eye on it for now and make sure it is totalling correctly, but so far, so good!!! THANKS AGAIN!!! so much for your help!!! "Bob Phillips" wrote: Change the name definitions to ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1) PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1) and add another PATCNT: =OFFSET('dly wrksht'!$A$1,0,2,COUNTA('dly wrksht'!$B:$B),1) then use a formula in D2 of =SUMPRODUCT((PHYNO=$B2)*(ADMDAY=E$1),PATCNT) and copy across and down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tasha" wrote in message ... I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help please-SUMPRODUCT and Dynamic Range
Thank you for all your help Bob!!! I ended up using the SUMPRODUCT formula
you gave me on the 16th with the specific cell references, and then I edited the range names by inserting $B:B$ and the same for D. Seems to be working, am going to keep an eye on it for now and make sure it is totalling correctly, but so far, so good!!! THANKS AGAIN!!! so much for your help!!! "Bob Phillips" wrote: Change the name definitions to ADMDAY: =OFFSET('dly wrksht'!$A$1,0,1,COUNTA('dly wrksht'!$B:$B),1) PHYNO: =OFFSET('dly wrksht'!$A$1,0,3,COUNTA('dly wrksht'!$B:$B),1) and add another PATCNT: =OFFSET('dly wrksht'!$A$1,0,2,COUNTA('dly wrksht'!$B:$B),1) then use a formula in D2 of =SUMPRODUCT((PHYNO=$B2)*(ADMDAY=E$1),PATCNT) and copy across and down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tasha" wrote in message ... I have two sheets, for short story purpose, we'll call them SheetA and SheetB. SheetA has the data I am pulling from, to SheetB. SheetA has columns B(DAY-which is the day of the month the pt was admitted), and column D(PHYNO-physicians number). This sheet is updated daily, and I have been using SUMPRODUCT in SheetB to give me a count of patients admitted by physician for each day of the month. SheetB is set up as column B(PHYNO) and column D(formula shown below). In cell D2 across through AH2 is the date shown as 1,2,3,4 according to what day of the month it is. Sheet A has named ranges(dynamic) of ADMDAY and PHYNO. I don't know for certain that I named these correctly, this is the formula for each name...ADMDAY=OFFSET(dly wrksht!$B$2,1,1,COUNTA(dly wrksht!$B:$B),1) and PHYNO=OFFSET(dly wrksht!$D$2,1,3,COUNTA(dly wrksht!$D:$D),4) =SUMPRODUCT(('H:\ExcelDocs\[SheetA.xls]dly wrksht'!PHYNO=B3)*('H:\ExcelDocss\[SheetA.xls]dly wrksht'!ADMDAY=D2)) This is not giving me anything but #REF! errors. I don't know if I'm not referencing the named ranges right, or if I didn't name them right, or if my SUMPRODUCT calculation is wrong????? SheetA A B C D E PATNO DAY DAYS PHYNO HSV 201 1 4 0854 M 202 2 2 10440 S 203 3 1 9996 D SheetB A B C D E F G H I J (ETC.) PHYSICIAN NAME PHYNO SPECIALTY 1 2 3 4 5 6 7 DR FEELGOOD 10440 FP (FORMULA IN EACH CELL) Can someone please help me? I've worked and worked for hours on this and am to the point of beating my head against the wall..... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
| Dynamic name reference in SUMPRODUCT | Excel Worksheet Functions | |||
| Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) | |||
Hybrid Mode
