Have a file with multiple tabs need to pull data from exactly the same rows and columns just different sheets

LotusLord

New Member
Joined
Sep 25, 2014
Messages
3
Good evening all.

I am not an expert by any stretch but I have been asked to help with what seems like a large spreadsheet to me. In this file there are several sheets that I need to pull the numbers from and put into 3 master sheets. So for example I'm using the formula =IF(Q6='Escobedo 1'!$A6,'Escobedo 1'!$B6,0)

The cell below that is =IF(Q6='Escobedo 2'!$A6,'Escobedo 2'!$B6,0), and so on down the line for at least 10 more sheets. I'm looking to be able to minimize the manual changing of values in each of these cells, and auto fill is not working for me as I would like. If I drag down All of the cell references are incremented. Does any one have any ideas how to proceed?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Simon4s

Board Regular
Joined
Sep 22, 2014
Messages
143
Are those the actual formulas? I am assuming B6 will change depending on if Escobedo 2, 3, 4,5?

SO you have ten sheets and ten rows:
=IF(Q6='Escobedo 1'!$A6,'Escobedo 1'!$B6,0)
=IF(Q6='Escobedo 2'!$A6,'Escobedo 2'!$B6,0)
=IF(Q6='Escobedo 3'!$A6,'Escobedo 3'!$B6,0)
=IF(Q6='Escobedo 4'!$A6,'Escobedo 4'!$B6,0)
=IF(Q6='Escobedo 5'!$A6,'Escobedo 5'!$B6,0)
=IF(Q6='Escobedo 6'!$A6,'Escobedo 6'!$B6,0)
...
And u want to keep writing these formulas for all the data in each sheet?

I think it would be better to make a drop down list with "Escobedo 1", "Escobedo 2".... all the way to 10 in "A1". in A2 you type =ifs(A1=Escobedo,'Escobedo 1'!A1[A1=escobedo 2..... all the way to 10

Then you apply to all cells in the sheet[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,037
Messages
5,526,402
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top