Link to home
Create AccountLog in
Avatar of infiniti7181
infiniti7181Flag 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 :)
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of infiniti7181


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

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.
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Pleased to help