How to get data in a variable cell range depending on cell value

[LewisM]

Hello, everybody. Please I need your help.


I have two Excel archives, the 1st contains multiple data tables, the 2nd I would like to make it work like a "Search", I mean, it would get data from the 1st archive depending on cell value.


Let me explain:


What I got on 1st archive:
- 'Food' table - A110:F134
- 'Cars' table - A136:F165
- 'Colors' table - A167:F186


What I want to do on 2nd archive:
If A1="Food", show me the whole 'Food table'
If A1="Cars", show me the whole 'Cars table'
If A1="Colors", show me the whole 'Colors table'

Tables have variable ranges.


I may insert or exclude rows on data (1st archive), then the 'Search' must show me always the updated data.


I need it to work with formulas.


I hope I was clear, I've been searching for it all over the web, and I can't find anything...
Can you guys help me, please?
Thanks in advance!

 

[LewisM]

This VBA code by Mike H from PC review works fine for me:

Sub OpenAndDothings()'Change this to your directory
MyPath = "C:\"
MyFile = "Book3.xls"
Workbooks.Open Filename:=MyPath & MyFile
'Do things
ActiveWorkbook.Close savechanges:=False
End Sub

When I run it, it opens my source workbook and closes it.
This way, I have no problems with INDIRECT function.

Thank you for your support, Aladin, you helped me a lot.

Greetings from Brazil.

This VBA code by Peter T from PC Review works better for me, 'cause it doesn't close my source workbook - it hides it. It's better 'cause if I want to get data from other table (changing cell value), I need the source workbook to be opened.

Sub test1()
Dim sName As String
Dim wb As Workbook
sName = "myFile.xls"
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:= _
"C:\<path>" & sName)
wb.Windows(1).Visible = False
wb.Saved = True
Application.ScreenUpdating = True
MsgBox wb.Name & " is open and hidden"


End Sub

Just wanted to tell ya in case of someone out there needs something similar.


Thank you once again, Aladin!

 

[Aladin Akyurek]

This VBA code by Peter T from PC Review works better for me, 'cause it doesn't close my source workbook - it hides it. It's better 'cause if I want to get data from other table (changing cell value), I need the source workbook to be opened.

Sub test1()
Dim sName As String
Dim wb As Workbook
sName = "myFile.xls"
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:= _
"C:\
" & sName)
wb.Windows(1).Visible = False
wb.Saved = True
Application.ScreenUpdating = True
MsgBox wb.Name & " is open and hidden"


End Sub

Just wanted to tell ya in case of someone out there needs something similar.

Good idea.

Thank you once again, Aladin!

You are welcome.