Dynamically Build Datasheet

mongoose00318

New Member
Joined
Feb 5, 2025
Messages
4
Office Version
  1. 365
  2. 2024
  3. 2021
  4. 2019
Platform
  1. Windows
Hello, I am trying to create a way to quickly consolidate the data stored across 3 worksheets into one worksheet for analysis/charting purposes. I have uploaded images to try to help illustrate.

In worksheets PhysicalCount, AS400Count, & Shipments, the layout is the same as you can see in picture #1. Picture #3 is a named list called PartNumbers.

In image 2 I would like to do the following:
ColA = Incrementing dates for each part number (so after the data you see in picture 2, it would just repeat the same list of part numbers except move to Jan 2nd, etc). This will be used to reference the needed column in one of the first 3 worksheets
ColB = The part number used to reference the row in one of the 3 first worksheets
ColC = The result that is in worksheet "PhysicalCount" using the values in Sheet! columns A & B (ie. 1/1/25 & FA1015MA)
ColD = The result that is in worksheet "AS400Count" using the values in Sheet! columns A & B (ie. 1/1/25 & FA1015MA)
ColE = The result that is in worksheet "Shipments" using the values in Sheet! columns A & B (ie. 1/1/25 & FA1015MA)

I used =PartNumbers!$A$1:$A$32 in column B to dump the values in the named range "PartNumbers" and have got to here in column C: =PartNumbers!$A$1:$A$32

I have tried using match, xlookup, vlookup, etc and just can't figure out the combination but I know I am missing another vlookup or maybe it just needs to be an xlookup? I know Xlookup can only be used in 365 which is okay but if possible I would like something that is a little more cross compatible?

Any help would be greatly appreciated! Thank you all.

Sincerely,

J
 

Attachments

  • 1.JPG
    1.JPG
    66.7 KB · Views: 8
  • 2.JPG
    2.JPG
    67.8 KB · Views: 8
  • 3.JPG
    3.JPG
    58.2 KB · Views: 9

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Okay I tried this as well:

=INDEX(PhysicalCount!A2:AF31,MATCH(B7,PhysicalCount!A2:A31,0),MATCH(A7,PhysicalCount!B1:AF1,0))

But it gave me an error. Here is a link to my Excel sheet: 2025_Master.xlsx
 
Upvote 0
My solution: =INDIRECT("'AS400Count'!" & ADDRESS(MATCH(I3, AS400Count!A:A, 0), MATCH(I4, AS400Count!1:1, 0)))

For those that are looking to do something similar.
 
Upvote 0
Solution

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

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
Back
Top