Data Summary using 3D references

[dukesd]

I have a database that contains many different worksheets that all are organized identically. I want to create a summary worksheet that compiles data from multiple cells (20 pieces of data, each scattered around the worksheet) from each worksheet into a database to automate my data analysis... Is there a way to do this? I've lost myself in nested "IF" loops and am convinced there must be a better way... I don't want to SUM the data or anything (which the 3D refs functions all make you perform operations on the data), I simply want to reference the cell contents of each sheet. Is there a way to "fill down" and increment the worksheet rather than the cell?

Here's the desired outcome (column fields are separated by "/"):

Sample Number/data1/data2/data3/data4/data5/.../data20
1...
2...
3...
4...
5...

Ideally, if I add a worksheet in between the 3D cell reference, I'd like it to add another line to the database to include the next sample!!!

Thanks to anyone who can help me out!!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

You could start by using INDIRECT to reference the combination of a SheetName plus Cell Address.

Excel Workbook
	A	B	C	D	E	F	G
1			Addresses->	A1	D11	C5	J3
2			Sample No.	Data1	Data2	Data3	Data4
3	Sheet Names	Sheet01	1	123.0	456.0	78.0	910.0
4		Sheet02	2	120.7	459.8	72.3	913.0
5		Sheet03	3	114.8	465.2	68.0	914.0
6		Sheet04	4	108.4	458.3	64.9	904.4
7		Sheet05	5	107.9	464.5	73.7	899.1
8		Sheet06	6	106.2	461.0	83.3	891.0
9		Sheet07	7	103.2	463.3	86.7	892.1
10		Sheet08	8	103.0	459.3	76.8	884.8
11		Sheet09	9	98.3	465.2	73.9	892.7
12		Sheet10	10	105.9	473.1	64.6	888.7
13		Sheet11	11	103.0	468.8	63.5	880.6
14		Sheet12	12	94.5	466.3	62.7	882.4
Sheet

As to automatically adding to your table when a sheet is added, that could be done with VBA if really necessary.

 

[dukesd]

JS411, Thanks for your suggestion - clever putting the reference locations in row A! This was able to populate my summary quite nicely.

Now that the first hurdle is solved, is there a function that will call up the worksheet names automatically (fill in column "B" for me)? Inputting ~50 worksheet names will lead to some misspellings and frustration ahead for multiple users of the database that aren't as excel-friendly...

 

[Jerry Sullivan]

Here's a macro that could do that...

Sub List_SheetNames()
    Dim cStart As Range
    Dim lIdx As Long
    Set cStart = Range("B3")
    
    For lIdx = 1 To Worksheets.Count
        cStart(lIdx) = Worksheets(lIdx).Name
    Next
End Sub