How to pick data from different sheets conditionally?
 

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!

How to pick data from different sheets conditionally?
 

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)


"TR" wrote in message
...
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!

How to pick data from different sheets conditionally?
 

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!



"TR" wrote:

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!

How to pick data from different sheets conditionally?
 

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" wrote:

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)


"TR" wrote in message
...
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!

How to pick data from different sheets conditionally?
 

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)


"TR" wrote in message
...
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" wrote:

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)


"TR" wrote in message
...
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!

How to pick data from different sheets conditionally?
 

Again, Thank you!

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

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


Rt




"Chip Pearson" wrote:

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)


"TR" wrote in message
...
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" wrote:

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)


"TR" wrote in message
...
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!