Populate data from different sheets into 1 page based on criteria

JeromePl

New Member
Joined
Dec 17, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello - I am having some trouble on how to automate excel to grab bits of data from cells in other sheets (same workbook) and bring it into a single page summary. For example, I am wanting to populate this table and based on what serial number is typed in (B-002 etc), the table below will populate with data from the other sheets based on that value. This is what is looks like:

Device Information
Serial NumberB-002
Firmware Number
Device Main Board
Device Display Board
Device Imaging Module
Device Interface
Device Heater
Device Reader

An example of what the other sheet looks like:

Sheet 1

Serial NumberB-001B-002B-003B-004B-005
DisciplineEngSysSoftEngEng
Device Location
Device UserAUDUSUSAUDAUD
Firmware Release0.1.00.0.70.0.30.2.10.0.0
Variations

This is what sheet 2 looks like:

Serial NumberB-001B-002B-003
Main Board1234563435324567
Display Board246834534534523
Imaging Module135734534323435
Interface246893457765345777
Heater12348923423234234
Reader88794457565676874

I have tried playing around with 'INDEX' or 'IFS' - =IFS($D$3=B-001, 'Device Config Tracker'!F12, $D$3=B-002, 'Device Config Tracker'!G12 ) but I would need to write out 250 different IF statements as I need to have this setup for 250 different serial number (B-001 to B-250). Is there an easier way to perform this?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,130,123
Messages
5,640,245
Members
417,131
Latest member
Seanr19871

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