Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collecting data from many sheets to one sheet | Excel Discussion (Misc queries) | |||
macro | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
consolidation of tables in excel with text and figures | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) |