Using Indirect(Address) Function Question

[brejohns]

I have a multiple tab worksheet. I want to be able to:
(1) Search a particular tab based on an input
(2) Within that tab, search for Row heading and a Column heading.

For example, I have several tabs, however I want to search the "Colorado" tab, and want to return the result for "Aurora" [where several cities are listed in the rows of a particular column with the Colorado tab] and "Population" [Where several different statistics are listed within the columns of a specific row]

Thank you for your help

 

[DanteAmor]

Assuming your data on the "Colorado" sheet is like this:

Colorado
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:105.5px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >Population</td><td >Land Area</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Arvada</td><td style="text-align:right; ">120,000</td><td style="text-align:right; ">100</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Aurora</td><td style="text-align:right; ">300,000</td><td style="text-align:right; ">300</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Denver</td><td style="text-align:right; ">700,000</td><td style="text-align:right; ">400</td></tr></table>


Try this:

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Estate</td><td >City</td><td >Statistic</td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Colorado</td><td >Aurora</td><td >Population</td><td style="text-align:right; ">300,000</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=INDEX(INDIRECT("'"&A2&"'!A1:F11"),MATCH(B2,INDIRECT("'"&A2&"'!A1:A11"),0),MATCH(C2,INDIRECT("'"&A2&"'!A1:F1"),0))</td></tr></table></td></tr></table>

 

[brejohns]

Thank you Dante. I am little confused what the "'!A1:F11" is referencing?

 

[DanteAmor]

Thank you Dante. I am little confused what the "'!A1:F11" is referencing?

From A1 to F11 the whole area.
From A1 to A11 the cities.
From A1 to F1 statistics.


=INDEX(INDIRECT("'"&A2&"'!A1:F11"),MATCH(B2,INDIRECT("'"&A2&"'!A1:A11"),0),MATCH(C2,INDIRECT("'"&A2&"'!A1:F1"),0))


You can put how your information is and how you want to get the result, maybe that way it I can help a little more.

 

[brejohns]

I am getting N/A utilizing this formula.

I have sheet called Colorado

=INDEX(INDIRECT("'"&A2&"'!A1:C3"),MATCH(B2,INDIRECT("'"&A2&"'!A1:A3"),0),MATCH(C2,INDIRECT("'"&A2&"'!A1:C1"),0))

 

[DanteAmor]

I am getting N/A utilizing this formula.

I have sheet called Colorado

The formula is correct, check that the data is correct or does not have blank spaces.

 

[brejohns]

It works, Thanks so much Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[SaffronAnne]

First of all, let me just say that I was floored to see someone used almost the same example as I did.
However, my request is a little different - that user wanted to only search things from the Colorado tab - I want to search things from any tab, so although I have a Colorado tab, I might want to search from the Alabama tab and get information from that state.
One more caveat, I am an intermediate user - as in, I virtually never use formulas unless it is to sum something