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!

 

[Aladin Akyurek]

Yeah, I see it's possible. I did it creating an external reference.
[...]

What did you put in A1 with that set up?

 

[LewisM]

What did you put in A1 with that set up?

I didn't change anything, A1 says the name of the table I want Excel to show me, just like you teached me.

Then on Name Manager I created external references to the tables on source workbook.

The problem is that your formula only works when source workbook is opened, because of INDIRECT function.

I'm taking a look on this thread, it's the same issue.

I appreciate your help, you've helped me a lot, thank you.

 

[Aladin Akyurek]

I didn't change anything, A1 says the name of the table I want Excel to show me, just like you teached me.

Then on Name Manager I created external references to the tables on source workbook.

The problem is that your formula only works when source workbook is opened, because of INDIRECT function.

I'm taking a look on this thread, it's the same issue.

I appreciate your help, you've helped me a lot, thank you.

Would you add the following code to your workbook using Alt+F11...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

and try replace INDIRECT with EVAL in our set up, with external referencing added.

 

[LewisM]

Would you add the following code to your workbook using Alt+F11...

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

and try replace INDIRECT with EVAL in our set up, with external referencing added.

I have no VBA knowledge, I'll try to learn something and do this. Thanks.

Just explain me this: "and try replace INDIRECT with EVAL in our set up, with external referencing added."

What you mean, would this


=IFERROR(SMALL(IF(1-(INDEX(INDIRECT($A$1),0,1)=""), ROW(INDIRECT($A$1))-MIN(ROW(INDIRECT($A$1)))+1),ROWS($A$3:A3)),"")</pre>
become this?


=IFERROR(SMALL(IF(1-(INDEX(EVAL($A$1),0,1)=""), ROW(EVAL($A$1))-MIN(ROW(EVAL($A$1)))+1),ROWS($A$3:A3)),"")</pre>

 

[Aladin Akyurek]

I have no VBA knowledge, I'll try to learn something and do this. Thanks.

Just explain me this: "and try replace INDIRECT with EVAL in our set up, with external referencing added."

What you mean, would this


=IFERROR(SMALL(IF(1-(INDEX(INDIRECT($A$1),0,1)=""), ROW(INDIRECT($A$1))-MIN(ROW(INDIRECT($A$1)))+1),ROWS($A$3:A3)),"")
</PRE>
become this?


=IFERROR(SMALL(IF(1-(INDEX(EVAL($A$1),0,1)=""), ROW(EVAL($A$1))-MIN(ROW(EVAL($A$1)))+1),ROWS($A$3:A3)),"")
</PRE>

This is not going help us. Post-2003 systems have the same issue with closed books as the previous systems, alas.

 

[LewisM]

This is not going help us. Post-2003 systems have the same issue with closed books as the previous systems, alas.

Ok, Aladin! I'll look for other options.

Thank you for your help!

 

[Aladin Akyurek]

Ok, Aladin! I'll look for other options.

Thank you for your help!

It would help if you could have some code in VBA that opens the related book on demand or by a trigger.

 

[LewisM]

It would help if you could have some code in VBA that opens the related book on demand or by a trigger.

Yeah, I had the same thought.

Maybe this or this can help me, but I don't mind if the workbook is visible or not.

 

[Aladin Akyurek]

Yeah, I had the same thought.

Maybe this or this can help me, but I don't mind if the workbook is visible or not.

Yep, something like that.

 

[LewisM]

Yep, something like that.

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.