Multiple Data Validation Criterias

[MCorrea]

Hi,

I'm working with data validation in multiple spreadsheets in a specific
excel project. My question is:

Is it possible to insert multiple Data Validation criterias for a
single cellin Excel 2003?
Example:

List
=Indirect(categories)

Date
=Today()+7

The combinatio of these two criterias would be the results shown in the
adjacent cell.

Thanks,

MCorrea
Excel 2003

 

[JulieD]

Hi

not sure what you're asking here ... validation checks the entry of data
into a cell, so say we're talking about cell A1, what do you want the user
to type in cell A1 so that it passes validation?

Cheers
JulieD

 

[MCorrea]

Hi Julie,

What I have is a spreadsheet with multiple vlookups and data
validations such as:

Column A Column B Column C Column D
Category Service Description Labor Cost Material Cost

The Category column is linked to a data validation list where I have a
couple of variables such as Preliminary Services, Doors & Windows, Concrete,
etc. Column B is an indirect Data Validation link to Column A, so for
instance if I choose Doors & Windows in the category column cell all of the
tasks related to Doors & Windows will appear in a list for me to choose one
from. In column. Column C and D have Vlookup functions that give me the
direct cost of labor and materials related to the specific Service
Description choosen. Well, to have the costs of the services is helpful but I
need to know what to buy and who's doing the labor. So what I need is a new
indirect data validation that could be located in a separate spreadsheet that
when I choose Doors & Windows from the list it will specify me what the
materials are and who's the labor. Keep in mind that the data is available
since the costs came up in the cells. I just can't compile them in a new Data
Validation and Vlookup list.

Thanks,

MCorrea

 

[JulieD]

Hi

i'm still a bit confused, my understanding is ... the labour cost for Doors
& Windows is populated via a VLOOKUP that says something along the lines of
=VLOOKUP(B2,LabourTable,2,0)
and what you want is in column E (for example) the name of the person who is
doing the work ... so where is the name of the person stored currently? in
the LabourTable in column 3? or do you want to have a data validation list
of 5 people and choose from this drop down who is doing the labour on this
job?

(likewise for the materials)

Cheers
JulieD

 

[MCorrea]

JulieD,

This is how it works. I have a labor list in the LaborCosts Worksheet.

column A column B column C
1 Carpenter $16.50 h
2 Helper $10.60 h
3 Mason $21.20 h
4 Plumber $23.20 h

and there on...

On the Service Description Worksheet I have the services devided in to
category lists. So, in the Doors & Windows column I would have for instance:

Service
Labor Material
4"-0 double hung wood window installation - each $68.40
$750.00
etc...

The labor cost for the window installation stands for:
=LaborCosts!B1*2+LaborCosts!B2*3

So it takes the equivalent of 2 hours of a carpenter's wage and 3 of a
helper's wage to install the window. And the same works for materials, I have
a Material shpreadsheet with the costs of the window, nails, glue, tar paper,
the list goes on. The $750.00 for the material cost is a variant of all these
materials. Well, that goes on for just about 3000 more services and materials
that I have in the database. The system works great and the numbers are
allways right on. The problem is that after the bid is done I spend a long
time doing the material and labor take-off (what and how much I have to buy
and who's doing the work). Well, since I have all this material and labor
information, and I know how much of each is being used I shouldn't be
spending time doing the take-offs. The problem is that I just can't figure
out a way to get it working.

What I need is, once I choose
'4"-0 double hung wood window installation - each' in the Bidding
spreadsheet from an =indirect(Category) data validation, the material
take-off would come up in a new spreadsheet (i.e.):

Column A Column B

Doors & Windows Qtdy.

Carpinter 2
Helper 3
4"-0 double hung wood window - each 1
1/16" nail 30
Tar Paper 5
etc...

I don't mean to give you too much trouble, it's just that this has been a
challenge of mine for quite a while. I know that with MS Access this could
solve this problem with no trouble but the nice thing about the system is
that it works all in Excel nicely.

best,

MCorrea

ps.: could a pivot table be the answer????