Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE RANGE FORMULA
I use this formula when I want total number from another worksheet to a
target worksheet: =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Is there a formula I can use with a date range that will pull data across to another worksheet (target worksheet?) Example of what target worksheet would look like: Date: 7/10/2009 WO# DATE RCVD COST REMARKS Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE RANGE FORMULA
With Date in cell A1 of your current sheet and your data in 'Target' (row 1
with headers); the below formula will give you the total for the month of date mentioned in cell B1 =SUMPRODUCT(--(TEXT(Target!B2:B100,"mmm/yyyy")=TEXT(A1,"mmm/yyyy")),Target!C2:C100) If this post helps click Yes --------------- Jacob Skaria "roy.okinawa" wrote: I use this formula when I want total number from another worksheet to a target worksheet: =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Is there a formula I can use with a date range that will pull data across to another worksheet (target worksheet?) Example of what target worksheet would look like: Date: 7/10/2009 WO# DATE RCVD COST REMARKS Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE RANGE FORMULA
Hi,
Are you asking about pulling over data based on a data range or are you asking how to bring over data that is not numeric? =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Another question about the above formula, why are you converting it to text dates? since both components seem to be dates why not just leave them that way. =SUMPRODUCT(--(Overall!H8:H2100=A1),--(Overall!H8:H2100<=A2),Overall!AI8:AI2100) Where A1 and A2 contain dates such as 1/1/2009 and 12/31/2009. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "roy.okinawa" wrote: I use this formula when I want total number from another worksheet to a target worksheet: =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Is there a formula I can use with a date range that will pull data across to another worksheet (target worksheet?) Example of what target worksheet would look like: Date: 7/10/2009 WO# DATE RCVD COST REMARKS Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE RANGE FORMULA
Hello,
Yes. Based on the date range I want to bring info from another worksheet to another worksheet. I don't not want to SUMPRODUCT, I want snapshot data info for a specific month/year. Here is an example (all this info is pulled from master worksheet based on date range): Date: 7/2009 WO# DATE RCVD STATUS DATE CLOSED REMARKS DV1 7/1/2009 Open 7/5/2009 in-progress DV2 7/3/2009 Open 7/7/2009 in-progress DV3 7/9/2009 Closed 7/9/2009 pending parts DV4 7/10/2009 Inbound completed "Shane Devenshire" wrote: Hi, Are you asking about pulling over data based on a data range or are you asking how to bring over data that is not numeric? =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Another question about the above formula, why are you converting it to text dates? since both components seem to be dates why not just leave them that way. =SUMPRODUCT(--(Overall!H8:H2100=A1),--(Overall!H8:H2100<=A2),Overall!AI8:AI2100) Where A1 and A2 contain dates such as 1/1/2009 and 12/31/2009. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "roy.okinawa" wrote: I use this formula when I want total number from another worksheet to a target worksheet: =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Is there a formula I can use with a date range that will pull data across to another worksheet (target worksheet?) Example of what target worksheet would look like: Date: 7/10/2009 WO# DATE RCVD COST REMARKS Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
DATE RANGE FORMULA
Hi again,
Wasn't aware I was doing that on converting dates to text (as far as I know, I know it works though.) This formula just gives me totals. "Shane Devenshire" wrote: Hi, Are you asking about pulling over data based on a data range or are you asking how to bring over data that is not numeric? =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Another question about the above formula, why are you converting it to text dates? since both components seem to be dates why not just leave them that way. =SUMPRODUCT(--(Overall!H8:H2100=A1),--(Overall!H8:H2100<=A2),Overall!AI8:AI2100) Where A1 and A2 contain dates such as 1/1/2009 and 12/31/2009. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "roy.okinawa" wrote: I use this formula when I want total number from another worksheet to a target worksheet: =SUMPRODUCT(--(TEXT(Overall!H8:H2100,"mmm/yyyy")=TEXT(I1,"mmm/yyyy")),Overall!AI8:AI2100) Is there a formula I can use with a date range that will pull data across to another worksheet (target worksheet?) Example of what target worksheet would look like: Date: 7/10/2009 WO# DATE RCVD COST REMARKS Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a formula to determine if date falls within a date range then | Excel Worksheet Functions | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
IF Date range formula | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) |