How to pick data from different sheets conditionally?

[Guest]

I have this question for a long while.

Is that possible to choose data from Sheet1, Sheet2... based on different
conditions? Say, I have market data from Chicago, LA and NYC, each on a
separate sheet, respectively. Can I build a toggle in a summary sheet and
select "Market" and retrieve market data from these markets individually?

Thanks!

 

[Chip Pearson]

You can use the INDIRECT function. Something like

=INDIRECT("'"&C3&"'!A1")

will return the value from cell A1 on the sheet whose name is in cell C3.
Pay attention to the quotes and apostrophes in the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Guest]

I know "If... Then" can work. However, if the number of markets is
significantly large, is there any way to select data from a specific market
without going down to coding?

Appreciate you help!

 

[Guest]

Thank you so much, Chip Pearson!

The Indirect function did help! However, I have a follow up question. My
data sheet is pretty large. A1 is not the only one I need to pull the data
from. Is there any way to use Indirect function to pull a pretty long list of
data, say A1, A2,...., A500.

Thank you!

Rt

 

[Chip Pearson]

Select the range of cells in which you want the results to appear. Type

=INDIRECT("'"&C3&"'A1:A500")

and press CTRL+SHIFT+ENTER rather than just ENTER, where C3 is the cell
containing the sheet name


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Guest]

Again, Thank you!

Initially it didn't work, but i put "!" in the formula and it worked.

=INDIRECT("'"&C3&"'!A1:A500")


Rt