VBA-Advanced Lookup functions

[eguirocker3]

I have a tough example that I can't solve and I'm not sure if it can be solved. I'm in need of a formula or macro to lookup values in another file.

Directions:
1. Open Lookup file (attached). What's in yellow is the answer guide. I would like to have a formula or macro to lookup the values in DSMP file (attached) and return the same values that I have currently in yellow. Lookup basically by promo, Base SRP/LUCP, & March.
2. I can't change DSMP file (columns, rows, merged cells etc). I want to keep this file as is.

Anybody have any ideas? It would be greatly appreciated.

[Keebellah]

I think I can help using VBA.
What do you want to trigger it.
Will you fill in all the promo values and then for example press a button to execute the macro?
The block in which the values are to be found now, will that always be columns below 'MERCHANDISING - LUCP includes Ad Fees' in the DSMP file?
What about the months, now you have January, February March and April, will you be adding columns?
You see, I need to know this so that I can make sure the macro will be dynamic enough to adapt to the data found.
I'll be doing it with Excel 2010 which won't be a problem with any version between 2007 and the latest

[eguirocker3]

I actually need to adjust this example. My directions in previous example were wrong. I was wanting to delete thread but I guess only moderators can do that. Can you solve the below instead?

Directions:
If I change a input on lookup file, I need that to automatically update the DSMP file. Let's just focus on March for now.
For example, if I change 50401 March Base SRP from 4.99 to 3.99, then I would like the DSMP file for 50401 March Base SRP to change to 3.99 as well. This is so I don't have to manually go back and forth every time I change a input on lookup file.

Also, DSMP file cannot change. I would like to have no formulas on any of those cells either.

[Keebellah]

When you say "DSMP file cannot change" does this mean the layout? You do want to update a value in it, correct?

I'll take a look tomorrow , it's bedtime here

BTW why is the post marked SOLVED, it isn't

[eguirocker3]

Yes layout cannot change and yes I need the value to update. Sorry thread should be unsolved now. I changed it. Thanks for looking into this example.

[Keebellah]

Is it only the Base SRP that'll change?

[eguirocker3]

And LUCP. I just focused on March for Base SRP and LUCP to make a easier example.

[Keebellah]

Good morning,
I did some testing and the macro will find the values.
I'll still have to workout the macro but for testing purposes I ran the macro and show the results in the three columns next to the lookup table (see png file)
You can see the target sheet's name, the range where the promo code was found and the range are in H-M where to look for March (or whatever will be the month in the Lookup table and the update SRP and LUCP accordingly.

I also attached the Lookup file with the macro (FindPromo) in it.

I hope to be able to work the actual updating out later this afternoon

[Keebellah]

I couldn't wait until this afternoon,
Try this one.

To trigger the macro just right-click in column A and that promo code will be updated in the DSMP file
If one or more of the values are not filled or NA the update will also place NA in the target file
If you want to change this you'll have to add some checks IF Then Else before running the macro.

[eguirocker3]

I right click, but I can't look at DSMP file to see if my change in lookup file replaced the value in the right spot on the DSMP file. What happened to the DSMP file? You can bring them into one workbook as visible tabs if you would like.

[Keebellah]

If the update was successful you get a checkmark next to the row.
To see it, well open the file and see.
You can add an option open workbook to check it, but I did not include a check to see if it was opened so if its open an you want to update, some things may not work.
You got the main solution, small things should be simple for you.

[eguirocker3]

I like the right click and check mark methodology you built in the FindPromo macro. That's cool stuff! I can't find that values being updated though.

Example,
If I change Lookup file cell B3 from 4.99 to 5.99, then cell L12 on DSMP file first tab doesn't update to 5.99. It's still at the 4.99.

[Keebellah]

I told you it's NOT the FindPromo macro it's the Right-click in Column A that triggers the new macro

The result should be something like the image attached:

I just made a minor modification here to test and I could display two columns next to the checkmark that shows the old values that were changed

BUT REMEMBER ITS RIGHT-CLICK in Column A to trigger the macro that actually Updates the DSMP file

[Keebellah]

True but Base SRP is not is NOT L2 but L12 and L12 has been updated accordingly.

Then I think you should explain more thoroughly.
You've got the vba code you can modify it to your further needs.

[eguirocker3]

Yes I right click column A and then it triggers the macro and places a check next to row. When I go open DSMP file on my computer to see if it changed, I don't see it. I put some additional comments in both attached files wihtin this reply to better illustrate. I don't know VBA code. Almost there, keep up the good work!

[Keebellah]

Have you actually read what I said in previous answer:

L12 is the updated row NOT L2 because that is not Base SRP
I'll check what you sent but

[Keebellah]

Okay my mistake juts change a minor change to the vba code:

Please Login or Register  to view this content.

Only the red number which is 5 in the code, change it to 2

[Keebellah]

Just in case, here's my version corrected and extra info.

[eguirocker3]

Awesome! Good job! It works!