|
Formula for looking up blank and/or date-filled cells- contingent
Hi:
Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- contingent
Does this work for you:
=IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
Unfortnately, that returns an Value error. I think it doesn't want to
multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
No, it doesn't want to multiply *text*!
That means your dates are *not* XL "legal" dates. Try this: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Unfortnately, that returns an Value error. I think it doesn't want to multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
Now I am seeing a Circular Reference Error.
Also, how do I make the dates legal as you say below? "RagDyeR" wrote: No, it doesn't want to multiply *text*! That means your dates are *not* XL "legal" dates. Try this: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Unfortnately, that returns an Value error. I think it doesn't want to multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
A circular reference means that the cell containing the formula is itself
within the calculating range of the formula, or, a cell in the calculating range of the formula is referring to the cell containing the formula. As far as "legal" dates - In XL, a date is a number (days since the start of 1900) formatted to display in any one of a number of different masks that you may choose. This allows for calculations to be performed on them (dates). Actually, it's easier to enter a "legal" date then not! XL takes anything that looks like a date, and makes it a date. These entries will *all* give you a "legal" date of July 2, 2008: July 2, 2008 Jul 2, 08 Jul 2 7/2/08 7/2/2008 7/2 7-2 7-2-08 7-2-2008 7-2/08 7/2-08 Plus a few others. However, what mask displays after you hit <Enter depends on your formatting. If the cell(s) for date entry are *PRE*formatted to TEXT, all bets are off! Anything entered may look like a date, but will *not* be a "legal" date. This might be your situation. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roady" wrote in message ... Now I am seeing a Circular Reference Error. Also, how do I make the dates legal as you say below? "RagDyeR" wrote: No, it doesn't want to multiply *text*! That means your dates are *not* XL "legal" dates. Try this: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Unfortnately, that returns an Value error. I think it doesn't want to multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
I checked and my dates are in fact "legal" so that was not the issue. And
yet, neither of those formulas work: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) Any other suggestions? I appreciate it! Roady "RagDyer" wrote: A circular reference means that the cell containing the formula is itself within the calculating range of the formula, or, a cell in the calculating range of the formula is referring to the cell containing the formula. As far as "legal" dates - In XL, a date is a number (days since the start of 1900) formatted to display in any one of a number of different masks that you may choose. This allows for calculations to be performed on them (dates). Actually, it's easier to enter a "legal" date then not! XL takes anything that looks like a date, and makes it a date. These entries will *all* give you a "legal" date of July 2, 2008: July 2, 2008 Jul 2, 08 Jul 2 7/2/08 7/2/2008 7/2 7-2 7-2-08 7-2-2008 7-2/08 7/2-08 Plus a few others. However, what mask displays after you hit <Enter depends on your formatting. If the cell(s) for date entry are *PRE*formatted to TEXT, all bets are off! Anything entered may look like a date, but will *not* be a "legal" date. This might be your situation. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roady" wrote in message ... Now I am seeing a Circular Reference Error. Also, how do I make the dates legal as you say below? "RagDyeR" wrote: No, it doesn't want to multiply *text*! That means your dates are *not* XL "legal" dates. Try this: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Unfortnately, that returns an Value error. I think it doesn't want to multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
How did you do the "legal" check?
Enter =ISNUMBEWR(cellref) in a cell adjacent to one of the dates. Return TRUE or FALSE? Gord Dibben MS Excel MVP On Thu, 3 Jul 2008 12:33:03 -0700, Roady wrote: I checked and my dates are in fact "legal" so that was not the issue. And yet, neither of those formulas work: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes", "N/A")) =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) Any other suggestions? I appreciate it! Roady "RagDyer" wrote: A circular reference means that the cell containing the formula is itself within the calculating range of the formula, or, a cell in the calculating range of the formula is referring to the cell containing the formula. As far as "legal" dates - In XL, a date is a number (days since the start of 1900) formatted to display in any one of a number of different masks that you may choose. This allows for calculations to be performed on them (dates). Actually, it's easier to enter a "legal" date then not! XL takes anything that looks like a date, and makes it a date. These entries will *all* give you a "legal" date of July 2, 2008: July 2, 2008 Jul 2, 08 Jul 2 7/2/08 7/2/2008 7/2 7-2 7-2-08 7-2-2008 7-2/08 7/2-08 Plus a few others. However, what mask displays after you hit <Enter depends on your formatting. If the cell(s) for date entry are *PRE*formatted to TEXT, all bets are off! Anything entered may look like a date, but will *not* be a "legal" date. This might be your situation. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roady" wrote in message ... Now I am seeing a Circular Reference Error. Also, how do I make the dates legal as you say below? "RagDyeR" wrote: No, it doesn't want to multiply *text*! That means your dates are *not* XL "legal" dates. Try this: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Unfortnately, that returns an Value error. I think it doesn't want to multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
Formula for looking up blank and/or date-filled cells- conting
What does "Both Don't Work" mean?
Are you getting #Value! errors from both formulas ... or what? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roady" wrote in message ... I checked and my dates are in fact "legal" so that was not the issue. And yet, neither of those formulas work: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) Any other suggestions? I appreciate it! Roady "RagDyer" wrote: A circular reference means that the cell containing the formula is itself within the calculating range of the formula, or, a cell in the calculating range of the formula is referring to the cell containing the formula. As far as "legal" dates - In XL, a date is a number (days since the start of 1900) formatted to display in any one of a number of different masks that you may choose. This allows for calculations to be performed on them (dates). Actually, it's easier to enter a "legal" date then not! XL takes anything that looks like a date, and makes it a date. These entries will *all* give you a "legal" date of July 2, 2008: July 2, 2008 Jul 2, 08 Jul 2 7/2/08 7/2/2008 7/2 7-2 7-2-08 7-2-2008 7-2/08 7/2-08 Plus a few others. However, what mask displays after you hit <Enter depends on your formatting. If the cell(s) for date entry are *PRE*formatted to TEXT, all bets are off! Anything entered may look like a date, but will *not* be a "legal" date. This might be your situation. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roady" wrote in message ... Now I am seeing a Circular Reference Error. Also, how do I make the dates legal as you say below? "RagDyeR" wrote: No, it doesn't want to multiply *text*! That means your dates are *not* XL "legal" dates. Try this: =IF(AND(A10,B10),"No",IF(OR(A10,B10),"Yes","N/A")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Unfortnately, that returns an Value error. I think it doesn't want to multiply dates- just as a guess... "RagDyeR" wrote: Does this work for you: =IF(A1*B10,"No",IF(A1+B10,"Yes","N/A")) ? Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Roady" wrote in message ... Hi: Column A is called 'Receipt Deadline' (mo/dd/yr) Column B is called 'Actual Receipt Date'. (mo/dd/yr) Column C would be called 'Outstanding Receipts'?' (yes/no/'N/A') I want Column C to look in Column A and IF there is a date entered there, then look up column B and see if there is a date entered there as well. Then 1 of 3 scenarios will occur in Column C: 1. If there is a date in A but none in B, then I want it to return a value of 'Yes' in C. 2. If there is a date in both A and B, then I want it to return a value of 'No' in C. 3. If there are no dates in either A or B, I want it to return a value of 'N/A'. Can you help? thank you! Roady |
| All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com