Microsoft Office Forums multidependant validation and lookup

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-20-2013, 08:41 PM
mark-gabb mark-gabb is offline multidependant validation and lookup Windows XP multidependant validation and lookup Office 2007
Novice
multidependant validation and lookup
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Angry multidependant validation and lookup

hey guys

i know this has been covered before but im stuggling to work out how to get it to work for my circumstance

sheet1
a company
b service
c day
d time frame
e unit cost
f time spent
g total cost

im trying to work it out so that when you choose a company it lists the services of that company
then based on services you get a drop down for day, and so on till it auto fills unit cost




if anyone can give me some advice on this it would be great i have a sample im already working on but cant seem to get it quite right yet

edit: have done some more work on sheet to reflect 2nd company in first sheet and to sho difference in times of day for different companys
Attached Files
File Type: xlsx cares work out sheet.xlsx (18.4 KB, 6 views)
Reply With Quote
  #2  
Old 05-20-2013, 10:09 PM
macropod's Avatar
macropod macropod is offline multidependant validation and lookup Windows 7 32bit multidependant validation and lookup Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

For E7, you could use an array formula input as:
=INDEX(Sheet1!$E$1:$E$100, MATCH(A7&B7&C7&D7, Sheet1!$A$1:$A$100 & Sheet1!$B$1:$B$100 & Sheet1!$C$1:$C$100 & Sheet1!$D$1:$D$100,0))
with Ctrl-Shift-Enter. However, you have also some work to do to make sure your dropdown selections exactly match the data on the Sheet1 (eg 'home care' is not 'homecare').
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-20-2013, 10:45 PM
mark-gabb mark-gabb is offline multidependant validation and lookup Windows XP multidependant validation and lookup Office 2007
Novice
multidependant validation and lookup
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Default

that worked perfectly, thank you so much for the tip


while i have you could you also help with a further piece on what im looking up
my time validation is causing me trouble,
i would like it to return one time base for sgsc and the other results for bcsc, however im finding that if i ask it to just minimize the results to show for one company or another i am stuck with every copy of the time

more or less my plan is to make it as simple as possible for users, so that once they select company only services listed apear and so on

something along these lines but i doesnt work

=index(Sheet1!b2:b100(MATCH(A7, Sheet1!A2:A100,0)))

new update with suggested changes
Attached Files
File Type: xlsx cares work out sheet.xlsx (20.9 KB, 2 views)
Reply With Quote
  #4  
Old 05-21-2013, 04:17 AM
macropod's Avatar
macropod macropod is offline multidependant validation and lookup Windows 7 32bit multidependant validation and lookup Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,544
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Mark,

I don't use Excel much, so I can't really give a lot of help with the validation list issues. That said, I think your requirements for A7 can be met by using a separate column with just one entry per company.

FWIW, I'm attaching a workbook that uses validation plus a macro to clear a dependent dropdown so that you won't get invalid results.
Attached Files
File Type: xls Dependent Dropdowns.xls (241.0 KB, 6 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-21-2013, 05:18 AM
mark-gabb mark-gabb is offline multidependant validation and lookup Windows XP multidependant validation and lookup Office 2007
Novice
multidependant validation and lookup
 
Join Date: Nov 2011
Posts: 17
mark-gabb is on a distinguished road
Default

Yeah yeah yeah

I get it, awesome thanks for that, hmmmm so now all I really need to do is get time of day working, services I can mange well enough using an indirect with a separate column, but the time of day will be really hard if it can't look up the first 3 columbs to get its time of day part by itself
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
multidependant validation and lookup Data Validation drop down rkalapura Excel 1 05-27-2012 02:23 AM
Forms -with validation ubns Excel 1 05-04-2012 08:51 AM
If/then formulas with data validation shira47 Excel 1 04-09-2012 05:14 AM
Data validation with IF Klyxrastafari Excel 1 10-03-2011 04:41 PM
Outlook userform validation help aiwnjoo Outlook 0 12-08-2010 12:57 AM


All times are GMT -7. The time now is 06:52 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft