Data Validation between Workbooks

**slumeet** · 09-19-2011, 07:30 PM

I have two workbooks I'm working with. One is the input/output spreadsheet and the other is the data spreadsheet. The inputs are dates, the outputs are decimals.

What I already have is a few macros that will look up the dates from the input cells into the data spreadsheet and output the results from the matching dates. The issue is that not all dates contain values to output (like on weekends/holidays/etc) so if you input an invalid date it blows up the macro.

What I would like to do: Have a control to click after inputting values that put the number "1" in the column next to it and shade the background of that cell red as well. If you have all valid dates, I would like cells D5:D7 to say:

ALL
DATES
VALID

If you have some ideas about how to write this macro, it'd be much appreciated. I was thinking of doing an if(vlookup()) combo but not sure if that's the best way.

I've attached a sample to show what I'm talking about. The first tab will be the input/output and second tab will be the Data workbook which will be a SEPARATE workbook completely. Just added it to the same file here for simplicity.

**Marcol** · 09-19-2011, 09:13 PM

[EDIT 1]
I must have been asleep when I read this thread. I've missed the point completely. Two workbooks!!!
[EDIT 2]
Why two workbooks? Would this not be more logically just one workbook?

You could use data validation to prevent entering dates other than Mon to Fri
e.g.
Select C5:C50
data validation
Allow:= Custom

Please Login or Register  to view this content.

or maybe conditional formatting to change the font colour to red
Formula:=

Please Login or Register  to view this content.

Where "ValidDates" is a named range on sheet "Data"
Refers to:=

Please Login or Register  to view this content.

Or again with the named range
data validation
Allow:= Custom

Please Login or Register  to view this content.

Using the Named Range will restrict entries to that in your sheet "Data"

**slumeet** · 09-20-2011, 08:15 AM

There are many reasons this is in two workbooks. The date sample I gave you is just a small portion of the actual WB. There are many tabs of dates/data which all update every day. If I were to insert the model into the data WB it would complicate things quite a bit. Another reason is that I'm making this for people not at all familiar with Excel so I would like to give them the model and keep the data sheet up to date on my end so they don't have to fiddle around or worry about it. As long as the reference is there they just have to hit buttons.

I'm trying to avoid inputting formulas in the cells, I can just use vlookups for the dates in the check column (D) if I wanted to be done but I'd like to keep this all in VBA. Was your code for VBA or cell formulas?

**Marcol** · 09-20-2011, 08:54 AM

I left the formula on the forum, after I realised that I had gone off on the wrong foot.

They are all worksheet formulae, there aren't tags for formulae in this forum.
I wouldn't dismiss native formulae for VBa, it is usually more efficient and automatic.
Use protected sheets if you are worried about accidental changes to formulae

Your code is very diffictult to follow, but I have made some progress with it. The problem is working out what sheet is in what workbook, we'll get there.
Can't do more 'til tonight.
In the meantime you might want to replace this

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

**slumeet** · 09-20-2011, 11:01 AM

Cool thanks, works great. I didn't include my data spreadsheet (which is called "Market Data") that my inputs sheet - called "Rates Model" - draws it's info from. The "GetXXX" macros are already done and functioning like I need them to. They open the Market data file, extract data,then close the file.

I would leave it all as is, but there's no error check in the model for dates inputted in Rates Model that don't match Market Data. I just want to add that error check somehow. So I thought adding a control button to check the dates against each other then spitting out some result in the cell next to the date was the best way to go.

Thanks for the help so far, I'll remember the step function.

**slumeet** · 09-20-2011, 05:50 PM

I think I may have been over thinking this one. How would a simple if then statement work here? Like running code to open the data file, check to see if each input has a match in the data sheet, put a 1 in the D column next to the date that doesn't match and put nothing next to the ones that do. Should be simple enough right?