Bring values from variable count of sheets

[inese_green]

Hello! I hope you can all help me with my work. I'm working with multiple sheets, and the count of the sheets are variable. I have the main sheet DATA, and sheet2, sheet3....Sheets contain the same information, except the unique number of samples (Lab. No), what are changing. I need to get information from other sheets to DATA sheet, based on criteria (Lab. No). It would bring from sheet2, sheet3....exact values based on LAb. No. what is on Data sheet.
Like, for Lab. No. 123A456 it would bring for particle diameter 16.0 mm - 4.0, for 11.2 mm - 5.0...so on...
I'm wondering, is it possible to write formula what would fit for variable number of sheet? I I have 2 or 10 sheets, it can bring in Data sheet values based on Lab. No. from multiple sheets.
I hope, I set out clearly what I want to achieve!
Have a nice day!!!

 

[Toadstool]

Hi inese_green,

This would be possible with VBA but I'll assume you want to use Functions.

Excel does have something called 3D functions which let you perform actions across multiple sheets but none of them let you just retrieve data ( see Excel 3D functions )

One method would be to use INDIRECT but it would require you to list the names of each worksheet. If that's acceptable then let me demonstrate. Here I have your Data tab and 4 other sheets with random data in row 3 and columns A to H.

On the Data sheet I enter the sheet names in column Z (out of the way) then for each cell I want to retrieve I construct the address for INDIRECT.

=IF($Z3="","",INDIRECT("'"&$Z3&"'!"&ADDRESS(3,COLUMN())))

The IF statement checks if I have a sheet name to avoid a #REF! error if nothing is found.

The INDIRECT then builds the address for each cell to retrieve. For the Lab No. of "This is Sheet4" I'll want to construct

='This is Sheet4'!A3

...so I put in a single quote (in case the sheet name contains a blank), pull the sheet name from column Z, end the single quotes and put the exclamation mark. For the cell address I'll always pull from row 3 but I want to pull from the same column as my formula so use ADDRESS, like this:

INDIRECT("'"&$Z5&"'!"&ADDRESS(3,COLUMN())))

inese_green.xlsx
	A	B	C	D	E	F	G	H	I	J															Y	Z
1
2
3	123456	a	b	c	d	55	55	55																		Sheet2
4	123457	x	d	r	t	44	55	66																		Sheet3
5	123458	h	j	k	l	22	22	22																		This is Sheet4
6	123459	pp	ll	ww	wwx	11	11	11																		Sheet5 is here
7
8
Data

Cell Formulas
Range	Formula
A3:H7	A3	=IF($Z3="","",INDIRECT("'"&$Z3&"'!"&ADDRESS(3,COLUMN())))

 

[inese_green]

Hi, Toadstool!
Thank you for your time and formulas! I went through the formulas what you where offered, but don't work in my case. It's not like it doesn't work, but it would be very difficult in this cases.

Would you advise me, how to write a code for VBA. Maybe you have some special YouTube channels what would you recommend for learning VBA?!
Than you!

 

[Toadstool]

Hi Inese_Green,

I'm not a VBA programmer so can't offer that solution. Search the forum for "VBA Training" and you'll find some recommendations, like this: VBA online-training-courses

Good luck!