#Value error message 4

[DebCook]

=IF('[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2)

is the formula I used to collect my data into a follow-up workbook from mulitple Program workbooks. I need to calculate 6-months from the exit dates into column d2:d30. However, not all of the exit dates are completed in the Program workbooks thus making the error #Value in the cells in column d. The exit dates in column c2:c30 are from the (formula)external workbooks; in column d2:d30 is =d2+365/2 returning #Value. I would like those cells to be blank until there is exit data entered into the external workbook. Any help will be very much appreciated!

Thanks!

Deb

 

[kjv1611]

First, what version of Excel are you using?

If 2007, You can use the If(IsError(...)...,...) construction.

Another way, if you know that the values are either valid or blanks, is to use IF(ISBLANK(...)...,...) in the appropriate location. That should work, at least in 2007 and 2003. I don't remember for sure about prior versions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlbo]

Note - iserror() is available in older versions as well

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjv1611]

@ iserror...

Hu! I guess I just didn't notice it before. Well, silly me, I might coulda used that one in the past! 'Course, at times, I might have just used that instead of perhaps a better/more accurate function.

--

"If to err is human, then I must be some kind of human!" -Me

 

[DebCook]

Thanks kjv1611 for your info, but how do I make this IF(IsError(...)...,...) work with all my workbook/worksheet madness? I don't know where or how to do it!

Thanks!

 

[kjv1611]

I'm not actually doing this in Excel right now, but I did clean up some Excel formulas with it just last week, I believe it was.

Try something like:

=IF(ISERROR(IF('[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2)),"",IF('[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2)

This is totally untested, so let us know if it gives you any errors... Or, well, just let us know how it works. [WINK]

--

"If to err is human, then I must be some kind of human!" -Me

 

[DebCook]

That took out the #Value error, thanks! Where in this formula, do I put the d2+365 (d2 being the cell that holds the exit date from the original worksheet)to calculate the date for the year?

 

[kjv1611]

On that usage, I just got to thinking....

You're wanting to say 6 months out from the certain date, right?

So why are you using [blue]d2+365/2[/blue]

I could be wrong, but based on your original post, I'd think you'd want something more like:

[blue]d2+180[/blue]

Well, I don't know it just seems simpler that way, less error prone. Or if you want it to be exactly 365/2, then 182.5. Truth is, neither number will always be exactly 6 months, b/c it depends upon what 6 months you're talking about and whether or not the year is a leap year.

And I thought from your original post that the d2+365/2 formula was being calculated in the cells being fed INTO these cells, correct?

So in that case, why would no need to put the same formula into this new cell?

I may just be totally missing something, so please forgive me if I'm just being dense.



--

"If to err is human, then I must be some kind of human!" -Me

 

[xlhelp]

in column d2:d30 is =d2+365/2 returning #Value

kjv1611, sorry for jumping in. Deb, I hope above you mean to say C2+365/2, else the formula above will give you a circular reference in column D.

I believe there is nothing wrong with your original formula. I think the probelm exists with your =D2+365/2 formula where you are trying to mix text and number. And that just ain't done. So, what you really need is
=If(iserror(D2+365/2),"",D2+365/2)

I keep metioning D2 since that's what you mentioned originally.

A man has only two choices: He can be right or he can be happy.

 

[kjv1611]

xlhelp, don't be sorry at all! Main thing is the problem gets solved. Also, I'm more like the student, and you the teacher anyhow!

--

"If to err is human, then I must be some kind of human!" -Me

 

[DebCook]

here is a sample of what I'm work'n with? 6-Month Due Date & 12-Month Due Date hold the calculation from the Exit Date

Contract # First Name Last Name Exit Date 6-Month Due Date 6-Month Sent Date 6-Month Return Date 12-Month Due Date 12-Month Sent Date 12-Month Return Date
ARM-WIA/D-020-06 #VALUE!
ARM-WIA/D-001-09 #VALUE! #VALUE!
ARM-WIA/D-005-09 #VALUE! #VALUE!
ARM-WIA/D-004-09 #VALUE! #VALUE!
ARM-WIA/D-003-09 #VALUE! #VALUE!
ARM-WIA/D-006-08 #VALUE! #VALUE!
ARM-WIA/D-002-09 7/16/09 1/14/10 7/16/10
ARM-WIA/D-007-07 #VALUE! #VALUE!
ARM-WIA/D-006-09 #VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!
#VALUE! #VALUE!

 

[xlhelp]

Hi, Deb.

What are the formulas in column E and columns H?

A man has only two choices: He can be right or he can be happy.

 

[DebCook]

I copied the foumula that kjv posted above and that worked taking the #Value out, but now I need those cells to calculate 6 & 12 month due dates.

Thanks!

 

[AnotherHiggins]

That's the problem with "6 months out". It's an ambiguous description.

Granted, once you pick a method for calculating it, you'll just stick with that method.

But I'd take a second to consider what would product the most meaningful data.

- 180 days from today?
- The first of the month, 6 calender-months out?
- The same day-of-month as today (currently the 24th), 6 calender-months out?
- The beginning of a Quarter?

Any of those dates can be calculated formulaically.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlhelp]

Let me rephrase it:

Which column(s) has (or had) this formula:
=IF('[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2)
if it's not the same now, what is it?


Which column has (or had) this formula:
=d2+365/2
if it's not the same now, what is it?


Which column has (or had) this formula:
=d2+365
if it's not the same now, what is it?

Please name the column and also post the formulas.

Thanks.



A man has only two choices: He can be right or he can be happy.

 

[DebCook]

Column A: Contract #: =IF('F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!B2="","",'F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!B2)

Column B: First Name: =IF('F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!C2="","",'F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!C2)

Column C: Last Name: =IF('F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!D2="","",'F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!D2)

Column D: Exit Date: =IF('F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2)

Column E: 6-Month Due Date: =IF(ISERROR(IF('F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2)),"",IF('F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2="","",'F:\Workload Lists FY2010\[WIA DW Client Tracking FY2010.xlsx]Classroom'!M2))

Column F: 6-Month Sent Date: manual data entry
Column G: 6-Month Return Date: manual data entry

Column H: 12-Month Due Date: I haven't entered a formula into it yet; waiting to figure out how the 6-month will need to be constructed!

Thank you!

 

[DebCook]

Thank you everyone for you help constructing this workbook.

 

[SkipVought]

Deb,

You might consider using MS Query to grab data from your 2010 workbook using an appropriate criteria. You would end up not having to use a bunch of lookup formulas and also would have alot more flexibility to point the query to a different workbook if need be.

I often grab data from other workbooks using MS Query and almost NEVER do lookup formulas to external workbooks. Much cleaner, IMHO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Taking into consideration what you need and what kjv1611 and John have suggested, the best option, in my opinion, would be:

Column E:

=IF(ISERROR(EDATE(D2,6)),"",EDATE(D2,6))

and column H:

=IF(ISERROR(EDATE(D2,12)),"",EDATE(D2,12))

or, if you want the date to be a weekday, then

Column E:

=IF(WEEKDAY(EDATE(B2,6))=7,EDATE(B2,6)+2,IF(WEEKDAY(EDATE(B2,6))=1,EDATE(B2,6)+1,EDATE(B2,6)))

and column H:

=IF(WEEKDAY(EDATE(B2,12))=7,EDATE(B2,12)+2,IF(WEEKDAY(EDATE(B2,12))=1,EDATE(B2,12)+1,EDATE(B2,12)))

If above still gives you an error, then go to Tools, Add-ins and put a check mark beside Analysis Toolpack and click OK.





A man has only two choices: He can be right or he can be happy.

 

[xlhelp]

MY apologies.

That second part should say D2 instead of B2.

A man has only two choices: He can be right or he can be happy.