quantity has to follow the item

[KlausW]

Hi Excel helpers.
I have a Sheets where I order items, I have made a Combo Box where I can search for the items and they appear from cell a9, b9 and down to a.

If I type G in the Combo Box, everything comes with G, and if I type Yeast on English yeast, everything comes with yeast, etc. Pic1 and Pic2

This works really well, now my challenge I want it like this when I type yeast in Combo Box then I write in column C how much yeast I have to order, this quantity has to follow the item . Also, when I search for something else in Combo Box. Pic 3

Item number is in column A, item name is in column B and quantity is in column C, this is the one I enter the quantity I want to order in.

All help will be appreciated.
Regards Klaus W

 

[KlausW]

Hi BSALV Then I'm back after the course and have had time to try out your great work. When I want to order, I find the item in the combobox, write the number and write remarks if it is to be used. Can I just enter number and press ENTER also the number is moved to sheets "Fra Fil" column M N.
I get this error message see picture 8. The file is still in Dropbox under the name Order UK 1
KW

 

[BSALV]

with your combobox you selected 4 rows of vanille****** (i guess).
Now you want to add a quantity and a remark to 1 of them, but which one ?

Therefore, you have to go to the B-cell of the wanted "vanille******" and then modify the quantity or the remark.
If that workmethod isn't nice, explain how you want it otherwise.
But you have to understand that excel doesn't know which one to choose, except if your activecell is pointing to a B-cell

EDIT : Apparently, last part of vanille ... is a forbidden word

 

[mumps]

It appears that each time the text in the combobox is changed, all the columns from column A to column K are filtered except for the data in column C. It remains the same. I can't seem to find the code that is triggered when you change the combobox. Where is that code located?

 

[BSALV]

your combobox uses the named range "vare" (Q9:Q366) as "Listfillrange" and writes its content in AB4 (linked cell), see screenshot. No VBA involved.
Column AB is the key, if the content of AB4 is in "Fra Fil" you see a number and so on and so on ..., with formulas.
Because the only fixed and stable place to write your price and remarks is at the RHS of your table in "Fra Fil", the columns for the quantity (C) and the remarks (J) in "Prisliste" are formulas that refers to "Fra Fil".
Try for example in your combobox "sa" (for "salmon"), choose a B-cell "Salmon" at the bottom of your screen, change the quantity and/or remark and then go again to your combobox and make that "sa" to "salmon" and your list become shorter and shorter but the quantity and remarks remain in the right record.

So, go once from column A to column K in "Prisliste" and check the formulas, some refer to P:X at the RHS, others refer to "Fra Fil", everything there is formula-driven, no VBA involved, a little bit the "oldfashioned" way.

Columns C and

 

[KlausW]

Hi BSALV

Thank you for a really good job you have done. I'm really grateful for that, I must unfortunately admit that I thought it would be very cumbersome to use this time. And for my needs, right now. I will put the question up in a different way. Get me explained it better. Thank U very much for U corroboration.

I got this VBA code from a member here on MrExcel. It just works really well and I wanted to hear if it is possible to modify it to my needs.
When I type in C4 to C10 or E4 to E10 or G4 to G10 in sheet "Tilmelding" I change the numbers in sheet "Tid" cell G3 and down after.
It is a register program for whether to eat or not.
Best Regards Klaus W

Program

Sub Rektangelafrundedehjørner4_Klik()



Dim DatRng, Dest As Range

Dim TidCol, TidRow, c As Integer



With Sheets("Tilmelding")

Set DatRng = .Range("C4:C10")

On Error GoTo Ooops

TidCol = Application.Match(.Range("A2"), Sheets("Tid").Range("1:1"), 0)

TidRow = Application.Match(.Range("B4"), Sheets("Tid").Range("C:C"), 0)

End With



For c = 0 To 2

Set Dest = Sheets("Tid").Cells(TidRow, TidCol).Offset(0, c).Resize(7, 1)

Dest.Value = DatRng.Offset(0, 2 * c).Value

Next c



Ooops:

If Not Err.Number = 0 Then MsgBox " Not able to match Initial or Date -- Please check and try again"

On Error GoTo 0



End Sub

 

[BSALV]

That macro is okay, but something is perhaps tricky.
(i changed A4 of tilmelding to "Maandag", the dutch version of your "Mandag", because otherwise the dates disappeard (have errors) in Tid)

There is a dangerous contruction, because Tid A3 refers to Stamdata A30, ....
IMHO, Tid A3 must be a fixed date, for example 1/1/2022.
Otherwise the data starting in G3 and to the right and down remain fixed as you modify stamdata A30.
So unvoluntary(?), the dates and the data shifts

So if you're having your problem is Tid columns C filled with errors or real data and in case of real data, is your date in that range ?

 

[ebea]

That macro is okay, but something is perhaps tricky.
(i changed A4 of tilmelding to "Maandag", the dutch version of your "Mandag", because otherwise the dates disappeard (have errors) in Tid)

There is a dangerous contruction, because Tid A3 refers to Stamdata A30, ....
IMHO, Tid A3 must be a fixed date, for example 1/1/2022.
Otherwise the data starting in G3 and to the right and down remain fixed as you modify stamdata A30.
So unvoluntary(?), the dates and the data shifts

So if you're having your problem is Tid columns C filled with errors or real data and in case of real data, is your date in that range ?

The main problem in this you point out BSALV, are that times (in the case in the uploaded File in #15) refer to each other, across multiple pages, and create unnecessary coincidences. In Sheet "Tilmelding", both Day and Date, should be made in another way (look in I4, where you have the same date again).
And you see the same construction in the originally questions uploaded File.