#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

 

[xlhelp]

Sometimes I leave my brain at home. So, above stuff one more time. Hopefully with some thought put into it.

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(ISERROR(EDATE(D2,6)),"",IF(WEEKDAY(EDATE(D2,6))=7,EDATE(D2,6)+2,IF(WEEKDAY(EDATE(D2,6))=1,EDATE(D2,6)+1,EDATE(D2,6)))))

and column H:

=(IF(ISERROR(EDATE(D2,12)),"",IF(WEEKDAY(EDATE(D2,12))=7,EDATE(D2,12)+2,IF(WEEKDAY(EDATE(D2,12))=1,EDATE(D2,12)+1,EDATE(D2,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.

 

[DebCook]

xlhelp, the 1st senerio worked perfectly! No need for add-in.

When I grow up I wanna be smart like you guys!

Skip: originally I started with Access. This database was awesome. Calculations of due dates, avg.wage, you name it it answered any & all questions that could possibly be asked. Too many inexperienced staff that feared Access to use it. Oh, well, so now, staff has one workbook to enter data and sha-zam! all data is collected to this follow-up.

All is happy...

Thank you so much!

 

[SkipVought]

I was NOT suggestin MS Access. I was suggesting using MS Query in EXCEL.

faq68-5829

Skip,

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

 

[kjv1611]

DebCook,

What Skip was mentioning was using a tool not terribly different than queries in Access, but it's totally within Excel... well, at least it looks that way, though I suppose it's actually a separate application - whatever the detail, it's running queries within your Excel workbooks. And when you take the time to use it, it does work VERY well. I often forget about using it, and wish I had used it.

As far as your mention of Access, if you had all your stuff in Access, calculations and all, the easiest thing there, in my opinion, would be a structure overall like this:

[ol][li]Access database, split, with one main back-end, and front-ends on each user's PC, where they could customize some portions if desired for themselves.[/li]
[li]In the default front-end, you'd have one (or more) userform which handled everything with buttons, combo boxes, text boxes, etc.[/li]
[li]Then, if the final reported data is desired to be in Excel, the you can spit the data out in Excel, again from the userform.[/li]
[/ol]

Anyhow, if someone can open Excel, and do basic work within Excel, then SURELY they can work within a customized user form which is specifically customized for their need(s).

Anyway, that's just a thought. But if it's working now, and everyone is happy, then it may not be worth the time and effort of redoing the Access database.

--

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