I am trying to work out a work order worksheet. I would like the "Labor Total" to equal the hours Xs one of two shop rates (88 or 60/hour), selectable via drop down menu in the labor parts cell.
I am totally confused and am quite sure I am not making much sense. I am hoping (and am certain that) there are many people here smarter and more experienced than me who are willing to help.
I have attached the file. Column L will be hidden.
With great thanks,
Sample Excel File.xls
I would suggest having a "default" rate and switching to the other rate only a specific value is entered in your test cell.
So let's assume you are choosing Shop1 or Shop2 in cell L7. We'll test for one, the other will be implied.
=E7*IF(L7="Shop1", 60, 88)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Would you mind, breaking that down even further for me? I really want to get this right.
Thank you-
Sure.
1) What cell in row 7 will you be selecting the parts shop? L7?
2) What exactly will the two choices be?
3) What are the rates for each choice?
4) Will there ever be MORE than these two choices?
Thank you.
1) L7
2) Maint. Shop, Paint Shop
3) 88 or 60
4) Yes, one or 2 more possibly in the future.
This LOOKUP formula can be expanded as needed to allow for as many shop names and rates as you wish.
=LOOKUP(L7, {"Maint. Shop","Paint Shop","Shop1","Shop2"}, {88,60,0,0})
IMPORTANT - the array of shop names is alphabetized! Remember that. The array of rates must be in the correct order to match the order of the shop names.
So if you add a new shop in the future to be Shop1 and it's actually called ACME, well, that would have to be moved to the start of the rates array, and the rates reordered to match.
So I create two arrays (as described), and place that LOOKUP formula in the Labor rate cell?
Yes, that should work. Post your formula here for review if it's different from mine.
I am trying that and i must be missing something. I am sorry, I really am not as equipped for this than I thought. This should work in Excel 03?
You can do it. Post the formulas you've tried that aren't working as expected.
I don't even want to post my crazy attempts at formulas. Seriously, I need instructions that a 7 year old can follow. I am having an array of problems and I thought I could make a simple spreadsheet, it is turning out to be really, really hard.
I am also trying to figure out how to make it kick out a new line with the same functions on enter.
I also am trying to figure out how to give totals and a signature line at the bottom of the page without having a "definite" last page (because it's dependent on the number of line items.)
I don't even know the right questions to ask. I used to be able to do some of these things before I stopped working full time when my son was born. Now I'm trying to get some work and I have forgotten everything. It's frustrating and embarrassing.
I appreciate you trying to help, though.
Looking back at post #6:
Notice the red array has 4 elements?Originally Posted by JBeaucaire
Each element is enclosed in "quotes" since they are text strings.
Each element in the array is separated by a comma
The elements in this array are listed in alphabetical order.
The array is enclosed in curly braces { }
Notice the rblue array also has 4 elements?
Each element is NOT enclosed in "quotes" since they are numeric values.
Each element in the array is separated by a comma
The array is enclosed in curly braces { }
The first element in each of the arrays go together. So the second element "Paint Shop" matches to the second element 60.
In the formula the first parameter is the "shop" we are trying to lookup in the first array.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks