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

cares work out sheet.xlsx (18.4 KB, 12 views)

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
[Fmr MS MVP - Word]

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

cares work out sheet.xlsx (20.9 KB, 8 views)

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

Dependent Dropdowns.xls (241.0 KB, 12 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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