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]

A1: Food

A2; Results

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(INDIRECT($A$1),SMALL(IF(1-(INDIRECT($A$1)=""),
  ROW(INDIRECT($A$1))-MIN(ROW(INDIRECT($A$1)))+1),ROWS($A$3:A3))),"")

 

[LewisM]

A1: Food

A2; Results

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(INDIRECT($A$1),SMALL(IF(1-(INDIRECT($A$1)=""),
  ROW(INDIRECT($A$1))-MIN(ROW(INDIRECT($A$1)))+1),ROWS($A$3:A3))),"")

Thanks for helping, but it didn't work (I guess I'm missing something)...

In which part of the formula do I define the range of my tables?

I get #REF! error if I delete IFERROR part.

 

[Aladin Akyurek]

Thanks for helping, but it didn't work (I guess I'm missing something)...

In which part of the formula do I define the range of my tables?

I get #REF! error if I delete IFERROR part.

I thought you had single column tables, but that is not the case.

I'll assume that you have the following:
>>>
- 'Food' table - A110:F134
- 'Cars' table - A136:F165
- 'Colors' table - A167:F186
<<<

I will also assume that you named

A110:F134 as Food
A136:F165 as Cars
A167:F186 as Colors

using the Name Box or Formulas | Name Manager. the latter is available on post-2003 systems.


If the above assumptions hold...

A1 on a different sheet houses a table name, say Food.

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(1-(INDEX(INDIRECT($A$1),0,1)=""),
  ROW(INDIRECT($A$1))-MIN(ROW(INDIRECT($A$1)))+1),ROWS($A$3:A3)),"")

B3, just enter, copy across, and down:

=IF($A3="","",INDEX(INDIRECT($A$1),$A3,COLUMNS($B$3:B3)))

 

[LewisM]

I thought you had single column tables, but that is not the case.

I'll assume that you have the following:
>>>
- 'Food' table - A110:F134
- 'Cars' table - A136:F165
- 'Colors' table - A167:F186
<<<

I will also assume that you named

A110:F134 as Food
A136:F165 as Cars
A167:F186 as Colors

using the Name Box or Formulas | Name Manager. the latter is available on post-2003 systems.


If the above assumptions hold...

A1 on a different sheet houses a table name, say Food.

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(1-(INDEX(INDIRECT($A$1),0,1)=""),
  ROW(INDIRECT($A$1))-MIN(ROW(INDIRECT($A$1)))+1),ROWS($A$3:A3)),"")

B3, just enter, copy across, and down:

=IF($A3="","",INDEX(INDIRECT($A$1),$A3,COLUMNS($B$3:B3)))

Thank you, it works perfectly this way (when tables and "search" are in the same archive).

But if my tables are in another archive, it doesn't.

Is there a way to do the "search" when tables are not only in a different sheet, but in a different archive?
If not, I will put my data in the same archive.

Thank you very much, you rock!

 

[Aladin Akyurek]

Thank you, it works perfectly this way (when tables and "search" are in the same archive).

You are welcome.

But if my tables are in another archive, it doesn't.

Is there a way to do the "search" when tables are not only in a different sheet, but in a different archive?
If not, I will put my data in the same archive.

Thank you very much, you rock!

What do you mean by an archive, a workbook?

 

[LewisM]

You are welcome.



What do you mean by an archive, a workbook?

Yeah, a workbook/worksheet.

Sorry for my English.

 

[Aladin Akyurek]

Yeah, a workbook/worksheet.

Sorry for my English.

If everything in the same workbook, there won't be any problem.

 

[LewisM]

If everything in the same workbook, there won't be any problem.

Yeah, I wonder if it's possible to look for data in a different workbook.

 

[LewisM]

Yeah, I wonder if it's possible to look for data in a different workbook.

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

But unfortunately it won't work unless source workbook is open.

Any idea on how to make the formula work using a closed external file?

Thank you.