Live data and manual entry

[afc171]

Hi all,

I have a spreadsheet which pulls data from Sage. There are two columns added on the spreadsheet which the user picks items, the and Department it is for.
The issue I have is that if something gets added or removed from Sage and then the databases is updated, the quantity and department are no longer assigned to picked product row.
Is there a way to keep these assigned to the selected row?

e.g of what is looks like..

Thanks

 

[afc171]

Looks like isn't a way to combine the two from what searches I have done.

At the moment I have a macro to copy the data to a new sheet which will be just static data. Is there a way to compare the Sage table after refreshing with the static sheet and add the new items to the bottom of the sheet?

Sage data:

4Despatch Consumables V1 (1).xlsm
	B	C	D	E	F	G	H
1	Despatch Consumables
2	product	long_description	lot_number	bin_number	quantity	inspection_date	expiry_date
3	GEN0023	Azowipes	15278	LC14COSH	8	20/Jun/2022	31/May/2024
4	GEN0023	Azowipes	15852	LC14COSH	19	29/Jul/2022	30/Jun/2024
5	GEN0023	Azowipes	15959	LC14COSH	20	26/Aug/2022	30/Jun/2024
6	PKG0001	BUF BOX 120 X 79 X 60MM	300421	LC/P8	135	30/Apr/2021
7	PKG0001	BUF BOX 120 X 79 X 60MM	260422	LC/P8	1821	26/Apr/2022
8	PKG0002	CARDICE 11.5KG BAG 10MM PELLETS	051022	KD80	3	05/Oct/2022
9	PKG0003	BUF BOX SLEEVES LIT.BUF	170566KSC	LC/P8	294	27/May/2021
10	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	090719	LC/P8	68	09/Jul/2019
11	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	031019	LC/P8	154	03/Oct/2019
12	PKG0005	ICT KIT SLEEVE LIT.KIT PACK	031019	LC/P8	312	03/Oct/2019
13	PKG0006	MMT/RMT KIT SLEEVE LIT.MMT	169162LC	LC/P8	949	30/Sep/2020
14	PKG0007	MED DRY ICE BOX 283X283X259MM	041022	LC/P8	60	04/Oct/2022
15	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	170367KSC	LC/P8	14	22/Apr/2021
16	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	211021	LC/P8	100	21/Oct/2021
17	PKG0009	DOUBLE WALL BROWN BOX 305X305X305MM L49	167212KSC	LC/P8	71	01/Oct/2019
18	PKG0010	A4 PLAIN DOC ENCLOSED WALLETS 1000/BOX	150720	LC/P8	3000	15/Jul/2020
19	PKG0011	A7 PLAIN DOC ENCLOSED WALLETS 1000/BOX	091019	LC/P8	5	09/Oct/2019
20	PKG0013	BROWN TAPE LOW NOISE 48MMX66M 36/BOX	161121	LC/P8	180	16/Nov/2021
21	PKG0014	BUBBLE BAG 130MMX185MM 500/BOX	081221	LC/P8	360	08/Dec/2021
22	PKG0015	SMALL BUBBLE WRAP - 3X500MMX200M	210422	LC/P8	2	21/Apr/2022
23	PKG0016	CLEAR TAPE LOW NOISE 48MMX66M 36/BOX	031019	LC/P8	102	03/Oct/2019
24	PKG0017	WHITE M/LITE ENV MLPF/3 220X330MM 50/BOX	170274KSC	LC/P8	800	26/Mar/2021
25	PKG0018	WHITE M/LITE ENV MLPH/5 270X360MM 50/BOX	100621	LC/P8	300	10/Jun/2021
26	PKG0019	150W/TE DIECUT CARTON 513X365MM ICT	170565KSC	LC/P8	2389	21/Jul/2021
27	PKG0020	WHITE M/LITE ENV MLPK/7 350X470MM 50/BOX	031019	LC/P8	150	03/Oct/2019
Despatch-LC-Stock

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:I70	Expression	=ROW(B3)=HighlightStock	text	NO

Copied to Static

4Despatch Consumables V1 (1).xlsm
	A	B	C	D	E	F	G	H	I
1	Despatch Consumables - Picked Stock
2	product	long_description	lot_number	bin_number	quantity	inspection_date	expiry_date	Book out Quantity	Initials
3	GEN0023	Azowipes	15278	LC14COSH	8	20/Jun/22	31/May/24
4	GEN0023	Azowipes	15852	LC14COSH	19	29/Jul/22	30/Jun/24
5	GEN0023	Azowipes	15959	LC14COSH	20	26/Aug/22	30/Jun/24
6	PKG0001	BUF BOX 120 X 79 X 60MM	300421	LC/P8	135	30/Apr/21
7	PKG0001	BUF BOX 120 X 79 X 60MM	260422	LC/P8	1821	26/Apr/22
8	PKG0002	CARDICE 11.5KG BAG 10MM PELLETS	051022	KD80	3	05/Oct/22
9	PKG0003	BUF BOX SLEEVES LIT.BUF	170566KSC	LC/P8	294	27/May/21
10	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	090719	LC/P8	68	09/Jul/19
11	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	031019	LC/P8	154	03/Oct/19
12	PKG0005	ICT KIT SLEEVE LIT.KIT PACK	031019	LC/P8	312	03/Oct/19
13	PKG0006	MMT/RMT KIT SLEEVE LIT.MMT	169162LC	LC/P8	949	30/Sep/20
14	PKG0007	MED DRY ICE BOX 283X283X259MM	041022	LC/P8	60	04/Oct/22
15	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	170367KSC	LC/P8	14	22/Apr/21
16	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	211021	LC/P8	100	21/Oct/21
17	PKG0009	DOUBLE WALL BROWN BOX 305X305X305MM L49	167212KSC	LC/P8	71	01/Oct/19
18	PKG0010	A4 PLAIN DOC ENCLOSED WALLETS 1000/BOX	150720	LC/P8	3000	15/Jul/20
19	PKG0011	A7 PLAIN DOC ENCLOSED WALLETS 1000/BOX	091019	LC/P8	5	09/Oct/19
20	PKG0013	BROWN TAPE LOW NOISE 48MMX66M 36/BOX	161121	LC/P8	180	16/Nov/21
21	PKG0014	BUBBLE BAG 130MMX185MM 500/BOX	081221	LC/P8	360	08/Dec/21
22	PKG0015	SMALL BUBBLE WRAP - 3X500MMX200M	210422	LC/P8	2	21/Apr/22
23	PKG0016	CLEAR TAPE LOW NOISE 48MMX66M 36/BOX	031019	LC/P8	102	03/Oct/19
24	PKG0017	WHITE M/LITE ENV MLPF/3 220X330MM 50/BOX	170274KSC	LC/P8	800	26/Mar/21
25	PKG0018	WHITE M/LITE ENV MLPH/5 270X360MM 50/BOX	100621	LC/P8	300	10/Jun/21
26	PKG0019	150W/TE DIECUT CARTON 513X365MM ICT	170565KSC	LC/P8	2389	21/Jul/21
27	PKG0020	WHITE M/LITE ENV MLPK/7 350X470MM 50/BOX	031019	LC/P8	150	03/Oct/19
PickedStock

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	Cell Value	between 1 and 1000	text	NO