Lock/Link VLOOKUP to a date

[gnaske]

Hi Forum.

I really don't know if this coding belongs in "VBA/Macros" or in the "Fomulas & Funktions" forum?
I made the codes as "Fomulas & Funktions" directly in the cells, but maybe it should be made as a VBA codes instead.

Neither if it should be made as a VBA Code or as a Function in the cells, I really don't know how to make the codes.

Explanation

• I have 3 VLOOKUP in the Columns("H" - "L" - "N").
• When selecting an Order no. in Column("E"), using the InCellDropdown, it will VLOOKUP the Price, the Delivery Fee % and the Vat % and then calculate the rest automatically.
• The Sold Orders will auto-fill with 1 in Column("G"), just to make it easy.

Problem
When a day is filled with the VLOOKUP, I need the values to be Locked, if either the Price or one of the percentage will be change on the next day in the VLOOKUP List.
This because I always want to go back, to see the prices on what was Sold on each day, according to the Prices and Percentage on the specific day.

Is it in any way possible to make codes like this?
And if it is, I'll be happy if it could be made in the attached TestBook, for then to be send back to me.

Thank you in advance

Ib

[Glenn Kennedy]

Surely order 1 will never be repeated.... or will it? Sheet List looks confusing. It looks like 2 lists. Would it not make more sense to delete one of the columns (B & C), add a date in the other and add on new orders, dates and costs at the bottom and use a formula to check the order date as well????

[gnaske]

Hi Glen.

I have other Codes in B and C in my real workbook and they can be deleted.
This is just a Testbook with the necessary Columns and Cells to show my problem.

First look at the dates in Column A.
The represent a day each.

In Column E (the yellow fields) you select the order, using the InCellDropdown.
______________

Try to select Order no. 1 on the first day, using the inCellDropdown in E5.
Price = 100 in Column H5

Then go to the next day and make the same Order no. 1, using the inCellDropdown in E16.
Price still = 100 in Column H16 - Because we didn't changed the VLOOKUP List.

If you then change the shortcut to edit the VLOOKUP list in Column S4 into 500, it will change the VLOOKUP price in all the cells you've selected Order no. 1.
______________

What I need is...
If you make an order on Date 1/1-2022, it will show the price from the VLOOKUP List.

If you then will change the price in the VLOOKUP List, it shouldn't have any effect on the price on 1/1-2021, but only on the price the next day.

And for each time you'll select a new price or one of the percentage in the VLOOKUP List, it shouldn't change anything backwards in the days.
______________

I was told earlier it could be made if the date in Column A were linked to the VLOOKUP.
Then it wouldn't change anything backwards, but only forward on the next day and so on, when the VLOOKUP list were changed.

Anyway
I don't think it can be made as a VLOOKUP formula code directly in the cells - Column("H" - "L" - "N")
I think I need to make the VLOOKUP in a Userform instead, using VBA codes.

I'll try to to make the Userform instead in this Testbook.
And then I'll probably need some help for the VLOOKUP as a VBA code instead.

Ib

[Glenn Kennedy]

I understand your problem... However, if that is the way you want to go, you will have to use VBA as formulae cannot remember what USED to be in a cell. In which case, I can't help (no VBA).

[gnaske]

Hi Glenn.

I was thinking I needed to use VBA Codes too.
And I already use a Userform to make an Order in my real Workbook.

I just need to add the VBA VLOOKUP to this Userform and then link it to the date.
I just don't know how to make the VLOOKUP and the linking in VBA, but I'll try and then probably get back, because it went wrong

Thank you very much for your input

Ib

[gnaske]

Hi Forum

Problem Solved

Instead of using VLOOKUP in each cell, I made the VLOOKUP as a VBA Code in a Userform.
But the full explanation is in the attached Testbook, if you want to have a look at it.

Thank you for watching

Ib