Find Specific Record And Return A Value

i think you're looking for the "vlookup"-formula, not a macro.

hello and welcome to the forums

do you actually need a macro or can you possibly use something like a "Vlookup" formula in the actual worksheet??

a quick tip, it is always a good idea to post a sample workbook if you can, being careful to remove any confidential or personal information.

Often a forum member will take that sample and add the code before sending it back to you for inclusion in your "real workbook"

again, welcome

To be honest, I don't know exactly what I need! Haha. Before a couple of days ago I essentially knew nothing about excel other than extreme basics. I've gotten this far based on some knowledge of programming and and experimenting :P

I don't have much data in right now because this is essentially a proof of concept so I can learn how to do it before I do nicer layout etc. But, here's how it works at the moment.

The user selects from, "Type" "Size" "Length" and "Quantity". When they hit "Save Entry" the entry is saved. What I also want to have happen is that when they hit save something goes to the second sheet, selects the same item that the user just entered, finds the price for that item, multiplies it by the "Quantity" they selected and then returns that value to the the column labeled "Price" and when they're done a total will be returned. Now, all the other stuff I feel I can figure out, but I've been sticking up on matching the data fields that the user selected to the price chart.

There will eventually be 100's of records too but I'm assuming this should essentially be a non-issue.

Hopefully this will be enough for you to go on?

Thanks again!

Edit: Based on looking at what Vlookup does the problem seems to be that I need to find a value that matches 3 fields and then grab a piece of data. I'm sure there would be a way to do this with conditional statements and I can even think of how it might be done, but I don't know if there's an easier solution?

Hello Wycliffslim,

attached is one example of how you could do it.

A couple of points here:

Your existing private macr4os seem to do the trick they were meant to so that is a good start.

On sheet 2 I have added in a new lookup table (prices) that is partially created from your product list values and the actual price.

the Vlookup function relies on the lookup cells (in this case Col G) being in ascending sequence so I had to sort your original data.

On sheet 1 I created a helper cell in row K to build the key that will natch the lookup table and used the return price per unit value to get the price for this line item

This could just as easily be handled in your macro code by doing the calc there or by at least copying the formula when you insert the new row.

As an aside, I assume row 1 of the Sheet1 is there to allow you to find out the next row to write your order line to, there are other ways to do this in VBA such as:

Please Login or Register  to view this content.

Hope this helps you with your POC, happy to help further if needed

WorkingHopefully.xlsm

Thanks for the help! I never would have known to combine all three groups into one and then compare!

I do have a couple of questions though.

When you say the Vlookup function relies on the cells being in ascending order does that mean that all future data would have to be put in alphabetically or what? I guess I don't 100% understand what you mean by that.
Edit: Okay, I've been reading about it a little bit. Since I DO want an exact value can I assign the range_lookup value to false so that they don't need to be in ascending order??

I also don't fully understand how the Vlookup function is working. I get the grabbing the value from the helper cell and the 2 at the end for grabbing the value from the second column, but the "Prices" value for the table_array is throwing me off? Can you explain that at all?

And yes, the first row is there for that, I did have it as a hidden row and could put it elsewhere too, but I'll try out your way too. I basically just figured out how to do that based on some rudimentary programming history and playing around. I've been having quite a bit of fun with this project!

Rest assured, if your saw my code you would know I am also an old mainframe programmer, and yes I am also having fun learning for this forum, but it is a slow process

your Q1, no that doesn't help you. The fundamental principle behind the vlookup function is ascending sequence. If your updates to Sheet 2 to add new product combinations is fairly frequent (or you have a gazillion product combinations to worry about) then i would look at maybe adding a "sort macro button" on sheet 2 that will do it for you when you finish a batch of updates.

If the updates are infrequent then doing the sort manually via the excel UI would probably be the way to go. Another factor would be, who is going to actually use the spreadsheet in real life and what skill level they have.

As an after thought, take a look at the MATCH function and see if that is more appropriate?

Q2, sorry I assumed a level of knowledge about excel that maybe isnt actually there. If you (in the excel UI) loot at the formulas options you will see "Name Manager" which is where I have defined "prices". This is an easier way to refer to a table instead of saying say G2:H7 and if you insert a row in the middle of the named range the value automatically changes, just makes like easier.

I wouldn't worry about changing your hidden row, that works equally as well.

The key issue is that the entries in Sheet2 MUST be in same format as the dropdowns in Sheet1, even including the "in." for example

You are very welcome

Just remember that when you do get your parts import done, you should also use that process to update Cols B,C & D in sheet2 then manually adjust the "prices" ranges.

If you need more help happy to help offline ([email protected])

If you think your initial request is resolved tehn plese use the thread tools (top right of you original post) to mark the thread "Solved"

and BTW, thanks for the Rep, much appreciated