Linking two files together and entering/modifying data on different worksheets based on data on a different file

[parkjw]

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!

 

[mumps]

I can't promise a solution but it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of the "sales" sheet and one or two of your "codes and categories of stock" sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). What is the full name including extension of the workbook containing the "codes and categories of stock" sheets?

 

[parkjw]

I can't promise a solution but it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of the "sales" sheet and one or two of your "codes and categories of stock" sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). What is the full name including extension of the workbook containing the "codes and categories of stock" sheets?

Hi, thank you for your reply!

'Income2021.xlsx'
As you can see, it contains quantity, description/code, brand, etc

'Stock2021.xlsx'
As you can see, it contains size, brand, quantity, etc. and sheets 10-13'', 14'', 15'', etc

So what I wanted to do is, look at the last two digits of the code/description, the brand and the quantity in 'Income2021.xlsx' (columns D, E and F)

Based on the last two digits of the Description column, I would find the sheet that is relevant to it (eg, for row 20 195-65-15, the last two digits are '15' so I would go to sheet 15'' in Stock2021.xlsx)
(also, don't worry about Alignment or non-digits or decimal points, I will move the entire file to a new format/table)

Once I am in sheet 15'', I look for the size that matches the description/code (eg, I will look for the rows in column A with 1956515 values)

For each row with the matching code, I look in column B for the cells that match the brand name (eg, I will look for the cell in column B with the value 'DT30'

Once I have found the row that contains the item description and brand (eg here, row 56), I will update the quantity (column c) (so here, I will subtract the quantity in column c by 2)

1. Look at final two characters of the value in the 'Description' column (column E of 'Income2021.xlsx')
2. Look for sheet in 'Stock2021.xlsx' that matches the last two characters in step 1 (eg, if ends with '14', go to sheet 14'', if ends with '19', go to sheet 19'')
3. Once you find the sheet in 'Stock2021.xlsx), look for the rows whose value in column A (size) match (eg, look for 2056016, look for 1956515)
4. Once you have found the relevant rows, look under column B (brand) for the cell whose brand/value matches (eg, look or DT30 or Davanti)
5. Once you have found the matching row, update the quantity column C

I know this sounds complicated and would be very long to do, but is it possible?

I did plan to make a separate column in 'Income2021.xlsx' that contains the item code in full numericals without '-' so it appears like 2056515 and keep the item description as it is to make it much more convenient to look up relevant columns in the Stock file

 

[mumps]

It's hard to work with pictures. Please use the add-in to post screen shots instead of pictures. This will allow me to copy/paste your data into a workbook so I can test a possible solution. I can't copy/paste from a picture.