| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statements with more than one date column
I have a workbook with date and description data. I have tried multiple
formulas like this one and get #values or name... =IF(('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'!G4,"")*IF(('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"") MS G-4 and MS I-4 are dates I-2 is the lower date and J-2 is the higher date. "To make it simple I want the items due in Jan. to show up and all other data to be blank" This information is on a second worksheet in the same workbook. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statements with more than one date column
I think you want something like this:
=IF(AND('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'! G4,IF(AND('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"")) Hope this helps. Pete On Dec 23, 6:46*pm, fyrefox wrote: I have a workbook with date and description data. I have tried multiple formulas like this one and get #values or name... =IF(('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'!G4,"")*IF(('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"") MS G-4 and MS I-4 are dates I-2 is the lower date and J-2 is the higher date. "To make it simple I want the items due in Jan. to show up and all other data to be blank" This information is on a second worksheet in the same workbook. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statements with more than one date column
Pete_UK
We are almost there except when the items that are dated in column MS I are coming up #REF! I also have a question about sorting. Will I be able to sort the Jan sheet independent of the master sheet? "Pete_UK" wrote: I think you want something like this: =IF(AND('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'! G4,IF(AND('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"")) Hope this helps. Pete On Dec 23, 6:46 pm, fyrefox wrote: I have a workbook with date and description data. I have tried multiple formulas like this one and get #values or name... =IF(('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'!G4,"")*IF(('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"") MS G-4 and MS I-4 are dates I-2 is the lower date and J-2 is the higher date. "To make it simple I want the items due in Jan. to show up and all other data to be blank" This information is on a second worksheet in the same workbook. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statements with more than one date column
You will get the #REF error if the cell references do not exist - are
you sure you have typed them in correctly? Sometimes when a long formula is broken into separate lines on the newsgroups you get spurious line-breaks, often with a hyphen character, so check that you do not have any of these. The only spaces in the formula occur between "Master" and "Sheet", so check that you have not introduced any other spaces at the end of the lines. Also, check that the sheet is actually named "Master Sheet", with no leading or trailing spaces. I'm still not exactly sure what you want to do, and I'm not sure what you want to sort - let's fix the formula first (presumably you want to copy it down), and then we can see what happens. I think you will need a different approach if you want the data to be automatically sorted. Hope this helps. Pete On Dec 23, 7:45*pm, fyrefox wrote: Pete_UK We are almost there except when the items that are dated in column MS I are coming up #REF! I also have a question about sorting. Will I be able to sort the Jan sheet independent of the master sheet? "Pete_UK" wrote: I think you want something like this: =IF(AND('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'! G4,IF(AND('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"")) Hope this helps. Pete On Dec 23, 6:46 pm, fyrefox wrote: I have a workbook with date and description data. I have tried multiple formulas like this one and get #values or name... =IF(('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'!G4,"")*IF(('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"") MS G-4 and MS I-4 are dates I-2 is the lower date and J-2 is the higher date. "To make it simple I want the items due in Jan. to show up and all other data to be blank" This information is on a second worksheet in the same workbook. Thanks- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statements with more than one date column
Pete_UK
The formula works great!! I set it to an array and it fixed the problem. Thanks for your help!!!! "Pete_UK" wrote: You will get the #REF error if the cell references do not exist - are you sure you have typed them in correctly? Sometimes when a long formula is broken into separate lines on the newsgroups you get spurious line-breaks, often with a hyphen character, so check that you do not have any of these. The only spaces in the formula occur between "Master" and "Sheet", so check that you have not introduced any other spaces at the end of the lines. Also, check that the sheet is actually named "Master Sheet", with no leading or trailing spaces. I'm still not exactly sure what you want to do, and I'm not sure what you want to sort - let's fix the formula first (presumably you want to copy it down), and then we can see what happens. I think you will need a different approach if you want the data to be automatically sorted. Hope this helps. Pete On Dec 23, 7:45 pm, fyrefox wrote: Pete_UK We are almost there except when the items that are dated in column MS I are coming up #REF! I also have a question about sorting. Will I be able to sort the Jan sheet independent of the master sheet? "Pete_UK" wrote: I think you want something like this: =IF(AND('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'! G4,IF(AND('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"")) Hope this helps. Pete On Dec 23, 6:46 pm, fyrefox wrote: I have a workbook with date and description data. I have tried multiple formulas like this one and get #values or name... =IF(('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'!G4,"")*IF(('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"") MS G-4 and MS I-4 are dates I-2 is the lower date and J-2 is the higher date. "To make it simple I want the items due in Jan. to show up and all other data to be blank" This information is on a second worksheet in the same workbook. Thanks- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF statements with more than one date column
You're welcome - thanks for feeding back.
Pete On Dec 24, 3:40*am, fyrefox wrote: Pete_UK The formula works great!! I set it to an array and it fixed the problem. Thanks for your help!!!! "Pete_UK" wrote: You will get the #REF error if the cell references do not exist - are you sure you have typed them in correctly? Sometimes when a long formula is broken into separate lines on the newsgroups you get spurious line-breaks, often with a hyphen character, so check that you do not have any of these. The only spaces in the formula occur between "Master" and "Sheet", so check that you have not introduced any other spaces at the end of the lines. Also, check that the sheet is actually named "Master Sheet", with no leading or trailing spaces. I'm still not exactly sure what you want to do, and I'm not sure what you want to sort - let's fix the formula first (presumably you want to copy it down), and then we can see what happens. I think you will need a different approach if you want the data to be automatically sorted. Hope this helps. Pete On Dec 23, 7:45 pm, fyrefox wrote: Pete_UK We are almost there except when the items that are dated in column MS I are coming up #REF! I also have a question about sorting. Will I be able to sort the Jan sheet independent of the master sheet? "Pete_UK" wrote: I think you want something like this: =IF(AND('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'! G4,IF(AND('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"")) Hope this helps. Pete On Dec 23, 6:46 pm, fyrefox wrote: I have a workbook with date and description data. I have tried multiple formulas like this one and get #values or name... =IF(('Master Sheet'!G4=I$2,'Master Sheet'!G4<=J$2),'Master Sheet'!G4,"")*IF(('Master Sheet'!I4=I$2,'Master Sheet'!I4<=J$2),'Master Sheet'!I4,"") MS G-4 and MS I-4 are dates I-2 is the lower date and J-2 is the higher date. "To make it simple I want the items due in Jan. to show up and all other data to be blank" This information is on a second worksheet in the same workbook. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
| Need the formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
| Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
| How do I use 3 separate column qualifiers to sum? IF statements? | Excel Worksheet Functions | |||
| If data in one column, take date, add 2 days, and turn the entire column a color... | Excel Discussion (Misc queries) | |||
Linear Mode
