Get Data from Specific Excel Sheet

Question

Hello everyone!

I'm working in a searcher in a given database.

My problem is that I want to pull information from a given Excel Worksheet with specific names on it, for instance:

My desired matrix is in 'C:\USER\DATABASE[BASE_01.xlsx]JAN'!$A$3:$C$20 place in my computer, and when I type:

='C:\USER\DATABASE[BASE_01.xlsx]JAN'!$A$3:$C$20

It gives me my desired matrix (the matrix is always the same size between databases, then $A$3:$C$20 is fixed).

I want to recreate this format as:

	A	B
1	DIRECTION	C:\USER\DATABASE\
2	EXCEL	BASE_01.xlsx
3	PAGE	JAN
4
5	MATRIX	="'"&B1&"["&B2&"]"&B3&"'!$A$3:$C$20"

In cell B5 I get: 'C:\USER\DATABASE[BASE_01.xlsx]JAN'!$A$3:$C$20

But I don't know how to make cell B5 to show me the desired matrix.

Do you know any way to make this work?

I want to use this format to navigate easily between directions, BASE_01, BASE_02, BASE_03, ... databases, and JAN, FEB, MAR, ..., SUMMARY pages.

Thank you in advance.

Ignacio.

Answer 1

How about?

Sub Test2()

With Range("B5:D22") 

    .Formula = "='" & Range("B1").Value & "[" & Range("B2").Value & "]" & Range("B3").Value & "'!A3" 

End With 

End Sub

Answer 2

Thank you for your reply Mr Deitrick.

Sorry I'm not that good at macros.

When I applied the macros, the formula appeared as: =@'C:\USER\DATABASE[BASE_01.xlsx]JAN'!$A$3:$C$20

And it just shows 1 cell of my desired matrix. Specifically, the B5 cell in my BASE_01 excel worksheet.

When I apply the matrix manually (I mean ='C:\USER\DATABASE[BASE_01.xlsx]JAN'!$A$3:$C$20), excel shows me a 18X3 matrix instead of one cell alone.

Answer 3

Try using a simple macro to convert the formula's displayed result into a formula that does the linking:

Sub Test()

With Range("B5")

.Formula = "=" & .Value

End With

End Sub