Hi, so I am working on a complex Excel right now. Apologies for the confusing title, but this problem seems confusing to me.
So for the problem, firstly, One Excel file is the sales file where the description of the goods sold are listed with price, code, method of payment, etc.
I have another excel file where there are different sheets based on different codes and categories of stock I have (eg, A = cables, B = mobile phones, C = tablet) that lists the code and stock levels of the items.
In my first Excel file, I want to look at the code column that displays the code of the item sold. I want to look at the last two digits (eg, 182-720-912 I would be looking at 12) and use that to find the relevant worksheet on the second file.
So it should be something like this:
If XX = '12', link to Sheet A, XX = '19' link to sheet B
Once I have linked it, I want to look through the code section of the sheet and link the original (full) code to the cell whose item code matches the code. Finally, I want to update stock level of that row
(eg, 182-720-912, linked to sheet A, find cell under code column with 182-720-912, and update stock from 9 to 8)
So to conclude, it kind of looks like this, the result I want:
- File 1: 182-720-912 is the item sold
Starts with 12, so link to sheet A in file 2
In sheet A, under "Code" column, look for "182-720-912"
On the row containing "182-720-912", go to "Stock Level" column and
update it
Is it possible? If so, can anyone help? Thank you!
So for the problem, firstly, One Excel file is the sales file where the description of the goods sold are listed with price, code, method of payment, etc.
I have another excel file where there are different sheets based on different codes and categories of stock I have (eg, A = cables, B = mobile phones, C = tablet) that lists the code and stock levels of the items.
In my first Excel file, I want to look at the code column that displays the code of the item sold. I want to look at the last two digits (eg, 182-720-912 I would be looking at 12) and use that to find the relevant worksheet on the second file.
So it should be something like this:
If XX = '12', link to Sheet A, XX = '19' link to sheet B
Once I have linked it, I want to look through the code section of the sheet and link the original (full) code to the cell whose item code matches the code. Finally, I want to update stock level of that row
(eg, 182-720-912, linked to sheet A, find cell under code column with 182-720-912, and update stock from 9 to 8)
So to conclude, it kind of looks like this, the result I want:
- File 1: 182-720-912 is the item sold
Starts with 12, so link to sheet A in file 2
In sheet A, under "Code" column, look for "182-720-912"
On the row containing "182-720-912", go to "Stock Level" column and
update it
Is it possible? If so, can anyone help? Thank you!