Avatar of infiniti7181
infiniti7181
Flag for United Arab Emirates asked on

Excel from multi sheets

Hello Gurus,

Good day to you . I would like to get clarified for a below query .


I have an example sheet contains
1. Sheet 2
2. Sheet 1

Sheet 1 contains data , in real i would be extracting from CRM as xls file and i will paste it as Sheet 1
Sheet 2 will be record extraction from Sheet 1 .

What i am trying to acheive is as follows :
1. What formula will be suitable to select with certain details to get extracted for Region and Unit by using Code as baseline . i tried to use Dget function , but it works only one cell at a time , which makes it tedious for 1000 plus line items
2. i  would like to know , if some one gives me diff sheet , how can i understand the difference, in this case , my third sheet there is discrepency in unit for Code 3019 .

kindly advise with suggestions for me to understand the logic for a newbie :)
BR,
sid
Test.xlsx
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Roy Cox

8/22/2022 - Mon
Roy Cox

Have you tried a PivotTable?

Create a PivotTable to analyze worksheet data

I converted the source data to a Table so that creating the PivotTable is easier and the data range will expand as data is added.

Overview of Excel tables
Test.xlsx
infiniti7181

ASKER
Dear Sir,

Thanks for the response .



i didnt consider pivot in mind due to the below reasons.
1. The sheet i extracted was from third source, so i need to validate with the information first . So in Sheet 2 , i can have pivot
2. my concern is
is there a way to extract info from sheet 1 , keeping in mind , i may need to see if there is any discrepency in sheet 3
in future the line would increase to 1000 + liness, i am not sure, if pivot can be solution to this .

please advise, if i am wrong or any easier way to deal to it

Thanks
Sid
Roy Cox

A PivotTable can cope with 1000+ lines, it would be automatic when you refresh it so you wouldn't need to use multiple formulas. You can easily change the PivotTable by adding or removing Fields to get the desired report. Do what I suggested and convert to the Data to a Table.

I'm not sure what you mean by a discrepancies in sheet3? There can't be any discrepancies if the PivotTable is based on the data.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Roy Cox

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Roy Cox

Pleased to help