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

parkjw

New Member
Joined
Jul 28, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 28, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
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

스크린샷 2020-07-31 오후 3.55.55.png


'Stock2021.xlsx'
As you can see, it contains size, brand, quantity, etc. and sheets 10-13'', 14'', 15'', etc
스크린샷 2020-07-31 오후 9.33.55.png


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

Well-known Member
Joined
Apr 11, 2012
Messages
11,726
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,117
Messages
5,835,489
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top