I am trying to make a spread sheet that will calculate costs depending on the size of an item but there is a specific machining charge that must be added. I have multiple sheets with different costs depending on the length (Z) of the item from 2" - 12". Each sheet has a machining charge depending on the x and y value. Ive attached a small test spreadsheet. I want to put in a value for X,Y and Z. The X and Y values index from a column and row but the Z value needs to specify which page it will come from. I've indexed the x,y values in a formula under grinding but I don't know how to get the Z value to change which sheet the formula will index from. If I put the number 2 in Z I want it to index x and y from the 2" sheet and if Z=2.5 then I want it to index x and y from the 2.5" sheet. I am also having it determine the cost by referencing a material type and a company abbreviation. The pricing formula is under formula in column A. If you need any specifics let me know. Thanks in advance.
Last edited by ericheu; 06-28-2011 at 01:13 AM. Reason: Solved
You can use INDIRECT to refer to a sheet name....
Try:
The above formula incorporates also the formula in A2 of the Formula tab!=IF(A4=0,"",((A4+0.1)*(B4+0.1)*(C4+0.1)*INDEX('Company Name Abbr'!$C$3:$N$115,MATCH('Grinding Costs'!G4,'Company Name Abbr'!$B$3:$B$115,0),MATCH('Grinding Costs'!D4,'Company Name Abbr'!$C$1:$N$1,0))))+(INDEX(INDIRECT("'"&C4&CHAR(34)&"'!$B$2:$AA$27"),MATCH(B4,INDIRECT("'"&C4&CHAR(34)&"'!$B$1:$AA$1"),0),MATCH(A4,INDIRECT("'"&C4&CHAR(34)&"'!$A$1:$A$27"),0)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I have one more thing that I can't get to work. If the number is not an exact match i can't get it to move up to the next closest number above it in the price sheet. I changed the 0 to a -1 at the end of the match(indirect line but it is not recognizing that command. I didn't think of that when I originally posted. Thanks again
Perhaps?
=IF(A4=0,"",((A4+0.1)*(B4+0.1)*(C4+0.1)*INDEX('Company Name Abbr'!$C$3:$N$115,MATCH('Grinding Costs'!G4,'Company Name Abbr'!$B$3:$B$115,0),MATCH('Grinding Costs'!D4,'Company Name Abbr'!$C$1:$N$1,0))))+(INDEX(INDIRECT("'"&C4&CHAR(34)&"'!$B$2:$AA$27"),MATCH(TRUE,INDEX(INDIRECT("'"&C4&CHAR(34)&"'!$A$1:$A$27")>=A4,0),0),MATCH(TRUE,INDEX(INDIRECT("'"&C4&CHAR(34)&"'!$B$1:$AA$1")>=B4,0))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That was absolutely perfect!!!!! Thank you very much for your help. You actually reduced the time it takes for me to quote by 75%.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks