What I'd like to do is have it where I will enter a quantity in column 'D' on sheet 1, and column 'I' will automatically formulate a price based on quantity multiplied by the price per unit which is set to a corresponding style number on sheet 2. It would also have to take in account the style number on sheet 1 (column B) and make sure it matches the style number in sheet 2 (where it is in column A).
I've attached an example with prices already filled in based on manual calculations. Can this be done? Any help would be greatly appreciated!
Here is the simplest way I could think of it:
You copy that down in Budget and it will automatically pull it for you.=OFFSET(Sheet2!A4,MATCH(Sheet1!B5,Sheet2!A5:A7,0),6)*D5
OR
You name the range of Styles on sheet 2, turn the Styles on sheet 1 into dropdowns via Data Validation, add a column next to it called PER UNIT or something similar, and put:
=IF(B5<>"",VLOOKUP(B5,Sheet2!$A$5:$B$7,2,0))
Last edited by ZENITH; 08-10-2011 at 04:38 PM.
When helped,use the icon right of the post #.
That works great!
What do you mean by named ranges? The actual product style names aren't just a list like 1, 2, 3, etc. They range from COV111 to FRO3441. Things likes that.
Also, could you explain how the drop down boxes could come into play?
Either way, this works great with what I'm trying to do so far, so thanks a lot!
I mean go to sheet 2, select A5:A7, go to formulas tab, define name STYLEOriginally Posted by alepoutre
then on sheet 1 on the style cell B5 select, and go to data tab, Data Validation, change to list, and put =STYLE in Source box. Then you create a new column to the right of it and put that code :That will let you use a dropdown box to select the price per unit IF on sheet 2 the prices are right next to the style in column B, otherwise you need to change that range Sheet2!$A$5:$B$7 to go to the column your price is in and change the number 2 to the column number.=IF(B5<>"",VLOOKUP(B5,Sheet2!$A$5:$B$7,2,0))
Its a good way to do it that way you can use basic math in your budget field without it getting messy.
Last edited by ZENITH; 08-10-2011 at 04:44 PM.
When helped,use the icon right of the post #.
Is there a reason why under the formulas tab that the "Insert Name" option would be greyed out, even when I have my cells selected?
Make sure you aren't editing a cell or copying some cells, press escape, if that doesn't correct it you may need to save and restart excel.
When helped,use the icon right of the post #.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks