Using VLookup but results show formula instead of results...

OK I just copied the formula and put it in cell A19 and the formula worked so there is something in the way the cells in A9 & A10 have been formatted I assume. Will continue to look into it ...

It's working now but I have changed your formatting:

FINAL PRINTED COST REFERENCE TABLE.xlsx

Hey Ursel, thanks for your help with my spreadsheet. I really appreciate your help. In the formula, where did the $ signs come from? I believe this was the error that was causing this. I have not found any resources online yet that include the $ in vlookup tutorials. When you modified it, did you start with changing the default font to Calbri and then make the modification to the formula manually or did you use a different formatting option in vlookup? In addition, can you incorporate a drop down menu with the vlook function? Probably not since the drop down menu is created using the data functions and vlookup is using the item number to achieve it... IF so, I am looking to make the shirt name the drop down menu. Just curious as I'm just looking at my options. I am cool with using the code above the shirt name to reference the shirt I want. I used the date function to create a drop down menu for my customer list.

I am also looking to use the "1/6" cell (below the "Front/Back" cell) to reference a price on the "Print Price" sheet which I created and added to the attached workbook that you posted. The information that would populate would be based on the "1/6" designation and also be linked to a number in a quantity range that is listed as the headers in the column on the "Print Price" sheet. The number 400 in cell b8 represents where the value would be entered. Presently I'm entering that figure in manually from the print price sheet.

Again, I can't express enough how thankful I am for your help. Thank you in advance for your help. Notice that I moved some of the rows around so the price reflects what text is in the cell preceeding it...

Here is the revised version of the worksheet: FINAL PRINTED COST REFERENCE TABLE-Modify2.xlsx

Hi Mark,

"$" in a formula makes the cell reference an 'Absolute' (as opposed to 'Relative') - if you don't know about this then it is important you read up on it as it is a lifesaver when working with formulas. Have a look here if you're not sure: http://www.homeandlearn.co.uk/excel2...l2007s7p5.html

A drop down menu can be used with VLOOKUP, so in your case cell A10 on the 'Final' sheet could be a drop down so you select one of the Item numbers from the list and not put it in manually.

Sorry but I don't understand what you are wanting to do with the "1/6" cell ... you lost me.

I've attached the file with the Drop down completed

FINAL PRINTED COST REFERENCE TABLE- DROP DOWN.xlsx

Thank you for your info and reference for me to read later today. Thanks also for doing the drop down menu for me! I'll look at it and make note of the formula.

As far as the '1/6 Darks' cell (B11), it is to be used to reference the print price from the "Print Price" sheet. What $ amount it populates will be based on the quantity (listed as '400' in cell B8). If you look at cell G8 on the Print Price sheet, you will see that the row is 1/6 Darks (over to the left) and the quantity falls in the 288-599 column. What I hope can happen is when I type a number in the B8 cell on the 'Final' sheet, it will populate the price based on a number that falls within the quantity range (288-599 in this case). Right now, I am just entering the number manually. Does that help explain it? Thank you again for your help! This is going to revolutionize estimating for me!

The drop down didn't make the swim across the Atlantic...it's not there...lol

Hi again,

I have now made the (cell E7) a drop down as well so you can select the percentage you want and not have them spread across the page. I have also changed the layout a little to make it more readable - especially for anyone with some accounting background! Anyway you don't have to accept it or like but have a look.

Attachment 277976

Cheers

OK in reference to your post #7 this figure can be changed also using a formula ... let me work on this

Just noticed the attachment didn't work so I'll try again:

FINAL PRINTED COST REFERENCE TABLE- DROP DOWN02.xlsx

Cell E10 now has a complicated formula that automatically works out the Print Price determined by the number of items (cell B13). Note the spreadsheet is Protected so as to avoid accidental changing of this formula - you can simply click the Review tab, 'Unprotect Sheet" to make adjustments.

All the best.

FINAL PRINTED COST REFERENCE TABLE- DROP DOWN03.xlsx

Wow, thanks....I get the flow of things with the formula for the various quantities. I had to adapt your formulas to the old layout that we prefer for estimating. At a glance, we can see some vital information for profitability, total garment cost and print costs. Yours does make much more sense for accounting purposes.

I am having a problem now with the customer list drop down not showing more than the first three entries with no scroll option within the drop down to help me select customer's down the list. I have added more clients to the customer sheet but they are not showing. I used the info in the data calculations area to create the drop down. Any suggestions?

Your help has been great for helping me reach my goal. Did you just type the code or did you use the automated vlookup and copy the formula and change the numbers for each quantity range? I didn't realize you could use more than one vlookup in a formula.

In addition, the percentage drop down that you created in the last "dropdown03" attachment did not work either. Maybe an issue on my end?

What started out as a venture to create a simple markup percentage sheet for the garments has turned into a full blown, (nearly) totally automated process for figuring out pricing. I used excel in college a bit but hadn't used it in over 5 years...this has been great to get back into the groove! I'm also using it to track jobs as they come through our shop. It helps me keep track of the job from entry into our system, through design/proofing along with print production through delivery. I'm also using it for food cost/serving pricing in the restaurant that my brother and sister in law manage. What a great tool!

You are more than welcome ... I enjoy the challenge of try to solve issues in Excel. I am self-taught so I too am learning: I like trying to figure out formulas but know very little about using VBA!!

Now I assume you did go to the 'Review' tab, and click on the 'Unprotect Sheet' button so you can make changes.

OK the drop downs can be fixed at your end. Select the cell with the Drop down in it. The Customer one was in B9 in the last version I attached only had 3 customers so to change this select that cel,l go to the 'Data' tab, click the 'Data Validation' arrow, select 'Data Validation....' and the dialog box opens. Here you will see under the 'Settings' tab, that under 'Allow:' 'List' is shown, and in the 'Source:' it states '=Customer'. Now if all the customers are not shown then select the little red arrow at the end on this input area and then find the sheet and select all the customers. Do the same for any other Drop downs that may not be working, click on the cell then go to Data Validation and then check they say 'List' and under 'Source' check the correct source is selected..

The formula with all the VLOOKUPs took me a while to work on but I eventually got it ... could have used INDEX & MATCH if the orders were not varied but that didn't work so had to go with the long nested IF formula ... gotta love that one!

If I can help you further, then feel free to contact me, and if you are happy with this then you had better mark it as "SOLVED" and please, if you don't mind' click the little "* Add Reputation" at the bottom left of this post.

All the best if all your ventures.

Cheers,

I found this video on You Tube for VBA in Excel: http://www.youtube.com/watch?v=t1vdt0FddsE
I plan on watching it to expand my knowledge! Your formulas are excellent as I know nothing about the 'IF' and 'AND' functions within Excel.

Yes, I did go into the Review tab and unprotect the worksheet in order to make changes.

I found the problem with the drop downs on my end which was two fold. 1). The cells that I wanted to reference were not included in the 'range'. Only the first three were included in the range because that was all there were when I set it up originally. I added customers afterwards and they were 'out of range' so to speak (lol). 2.) The cells that the drop down was supposed to access were not in a 'named range'. I had to go back to the 'Customer' sheet, select the cells with the customer names in them that I wanted in the drop down and go to the name box above and type the word 'customer' so that the Data Validation knew where to look. After I did this, I went to the 'Data' tab on the ribbon and ran the 'Data Validation' process. It works great! I even had a name out of alphabetical order on purpose and they showed on the drop down in the order they were represented in the cell order in the 'A' column on the 'Customer' sheet. After I sorted them alphabetically, I went back to the drop down and sure enough, they were listed in alphabetical order! Here is the reference that I found to do this: http://spreadsheets.about.com/od/dat...ation-pt-2.htm

I added a bunch more information to the 'Print Price' sheet (there are 82 different combinations for front/back prints). I went back in and did the same thing: 1.) Selected the cells in the 'A' column, gave it a name and then ran the 'Data Validation' process. They all appeared in the drop down menu on the 'Final' sheet! However, since all the newly added prices are not in the range that you have in your excellent formula, I will go back in and manually change the range in the formula from ending at cell J11 ($j$11 in your formula) to the last cell on the 'Print Price' page. This will allow the proper data to populate to the 'Print Price' cell. Presently, any price after cell J11 has a N/A in the 'Print Price' cell. I discovered that editing formulas is easier in MS Word as when I pasted it, it was easier to find the data that I needed to change. I will copy and paste it back into Excel after I have all the ending cell data changed for all the quantity ranges.

I will be happy to mark this as solved and would be happy to 'Add Reputation' for you my friend! You have been a GREAT help in getting this 'solved'!

I have another question concerning drop down menus: I presently have a drop down menu for cell A10 that populates information to cells A11 & A12 from the Shirt Info sheet. Can I make the info in cell A11 into a drop down menu that populates information to cells A10 and A12 also? That way, I could choose the product name if I didnt remember the item number. I tried to add a comma and another VLOOK formula to the cell but keep getting an error. See the attached example:

FINAL PRINTED COST REFERENCE TABLE-QUESTION-DROP DOWN.xlsx

You'll get a Forum Moderator come on here and tell you not to start another question within a Thread, but in response to your question the answer is yes. Cell A11 could also be a Drop down menu but your formula in cell A12 would need to be changed. In short you can have a Drop down menu or a formula in a cell but not both (I'm talking about A10). So if you want the Drop down menu in A11 then create a VLOOKUP formula in A10 or you could end up with a Shirt name and Item number that don't match.

Originally Posted by Ursul

You'll get a Forum Moderator come on here and tell you not to start another question within a Thread, but in response to your question the answer is yes. Cell A11 could also be a Drop down menu but your formula in cell A12 would need to be changed. In short you can have a Drop down menu or a formula in a cell but not both (I'm talking about A10). So if you want the Drop down menu in A11 then create a VLOOKUP formula in A10 or you could end up with a Shirt name and Item number that don't match.

Don't mean to violate the rules but thank you for your input.

By the way, I edited the complicated formula that Ursul wrote for me by copying the formula and pasting it into Microsoft Word. All the key values that were common to the VLOOKUP formula for different prices/quantities lined up great for easy editing. Open the attached file to see what I mean. I stumbled upon this when I was making editing changes after adding additional data to the other worksheets that are referenced in the main quote worksheet...

QUOTE FORMturkey.docx