| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Lookup cell value using list of worksheet names
I've got a workbook with 80 worksheets in. The first worksheet (INDEX) has a vertical list of all the worksheet names. I want to lookup a cell in each of the worksheets using the vertical list and return it to a column on the INDEX sheet. However, I don't want to use a VLOOKUP as this would be time consuming. What I'd ideally like is a formula along the lines of: ='A2'!C5 where A2 is one of the worksheet names, and C5 is the cell on that worksheet that I want to return. Is there any easy way to do this? -- amaranth ------------------------------------------------------------------------ amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031 View this thread: http://www.excelforum.com/showthread...hreadid=393812 |
|
#2
|
|||
|
|||
|
Hi,
Try this: =INDIRECT("'"&A2&"'!C5") or this (if the C5 reference is variable): =INDIRECT("'"&A2&"'!"&CELL("address",C5)) Regards, KL "amaranth" wrote in message ... I've got a workbook with 80 worksheets in. The first worksheet (INDEX) has a vertical list of all the worksheet names. I want to lookup a cell in each of the worksheets using the vertical list and return it to a column on the INDEX sheet. However, I don't want to use a VLOOKUP as this would be time consuming. What I'd ideally like is a formula along the lines of: ='A2'!C5 where A2 is one of the worksheet names, and C5 is the cell on that worksheet that I want to return. Is there any easy way to do this? -- amaranth ------------------------------------------------------------------------ amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031 View this thread: http://www.excelforum.com/showthread...hreadid=393812 |
|
#3
|
|||
|
|||
|
For when C5 is a variable you can simply use:
=INDIRECT("'"&A2&"'!" & C5) instead of: =INDIRECT("'"&A2&"'!"&CELL("address",C5)) "KL" wrote in message ... Hi, Try this: =INDIRECT("'"&A2&"'!C5") or this (if the C5 reference is variable): =INDIRECT("'"&A2&"'!"&CELL("address",C5)) Regards, KL "amaranth" wrote in message ... I've got a workbook with 80 worksheets in. The first worksheet (INDEX) has a vertical list of all the worksheet names. I want to lookup a cell in each of the worksheets using the vertical list and return it to a column on the INDEX sheet. However, I don't want to use a VLOOKUP as this would be time consuming. What I'd ideally like is a formula along the lines of: ='A2'!C5 where A2 is one of the worksheet names, and C5 is the cell on that worksheet that I want to return. Is there any easy way to do this? -- amaranth ------------------------------------------------------------------------ amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031 View this thread: http://www.excelforum.com/showthread...hreadid=393812 |
|
#4
|
|||
|
|||
|
Hi David,
I actually meant the variability of the cell's address (i.e. relative reference) not the value - excuse my French :-) Your formula requires the cell reference to be a value of the cell C5. Regards, KL "David McRitchie" wrote in message ... For when C5 is a variable you can simply use: =INDIRECT("'"&A2&"'!" & C5) instead of: =INDIRECT("'"&A2&"'!"&CELL("address",C5)) "KL" wrote in message ... Hi, Try this: =INDIRECT("'"&A2&"'!C5") or this (if the C5 reference is variable): =INDIRECT("'"&A2&"'!"&CELL("address",C5)) Regards, KL "amaranth" wrote in message ... I've got a workbook with 80 worksheets in. The first worksheet (INDEX) has a vertical list of all the worksheet names. I want to lookup a cell in each of the worksheets using the vertical list and return it to a column on the INDEX sheet. However, I don't want to use a VLOOKUP as this would be time consuming. What I'd ideally like is a formula along the lines of: ='A2'!C5 where A2 is one of the worksheet names, and C5 is the cell on that worksheet that I want to return. Is there any easy way to do this? -- amaranth ------------------------------------------------------------------------ amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031 View this thread: http://www.excelforum.com/showthread...hreadid=393812 |
|
#5
|
|||
|
|||
|
Ahh, yes, your formulas are the same the difference being that
the one with Address can be used with the fill handle to fill down while the first one had the address in quotes so fill handle would not work. My mistake in thinking you were supplying two different purposes. I had used the CELL with "address" for that purpose before but now that you brought it up I'd not realized why HYPERLINK Worksheet Formula was a bit more complicated when used with INDIRECT than I had used -- obviously wasn't using fill down. http://www.mvps.org/dmcritchie/excel...2.htm#indirect --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KL" wrote in message ... Hi David, I actually meant the variability of the cell's address (i.e. relative reference) not the value - excuse my French :-) Your formula requires the cell reference to be a value of the cell C5. Regards, KL "David McRitchie" wrote in message ... For when C5 is a variable you can simply use: =INDIRECT("'"&A2&"'!" & C5) instead of: =INDIRECT("'"&A2&"'!"&CELL("address",C5)) "KL" wrote in message ... Hi, Try this: =INDIRECT("'"&A2&"'!C5") or this (if the C5 reference is variable): =INDIRECT("'"&A2&"'!"&CELL("address",C5)) Regards, KL "amaranth" wrote in message ... I've got a workbook with 80 worksheets in. The first worksheet (INDEX) has a vertical list of all the worksheet names. I want to lookup a cell in each of the worksheets using the vertical list and return it to a column on the INDEX sheet. However, I don't want to use a VLOOKUP as this would be time consuming. What I'd ideally like is a formula along the lines of: ='A2'!C5 where A2 is one of the worksheet names, and C5 is the cell on that worksheet that I want to return. Is there any easy way to do this? -- amaranth ------------------------------------------------------------------------ amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031 View this thread: http://www.excelforum.com/showthread...hreadid=393812 |
|
#6
|
|||
|
|||
|
Yup, that's it. Thanks for coming back.
Regards, KL "David McRitchie" wrote in message ... Ahh, yes, your formulas are the same the difference being that the one with Address can be used with the fill handle to fill down while the first one had the address in quotes so fill handle would not work. My mistake in thinking you were supplying two different purposes. I had used the CELL with "address" for that purpose before but now that you brought it up I'd not realized why HYPERLINK Worksheet Formula was a bit more complicated when used with INDIRECT than I had used -- obviously wasn't using fill down. http://www.mvps.org/dmcritchie/excel...2.htm#indirect --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "KL" wrote in message ... Hi David, I actually meant the variability of the cell's address (i.e. relative reference) not the value - excuse my French :-) Your formula requires the cell reference to be a value of the cell C5. Regards, KL "David McRitchie" wrote in message ... For when C5 is a variable you can simply use: =INDIRECT("'"&A2&"'!" & C5) instead of: =INDIRECT("'"&A2&"'!"&CELL("address",C5)) "KL" wrote in message ... Hi, Try this: =INDIRECT("'"&A2&"'!C5") or this (if the C5 reference is variable): =INDIRECT("'"&A2&"'!"&CELL("address",C5)) Regards, KL "amaranth" wrote in message ... I've got a workbook with 80 worksheets in. The first worksheet (INDEX) has a vertical list of all the worksheet names. I want to lookup a cell in each of the worksheets using the vertical list and return it to a column on the INDEX sheet. However, I don't want to use a VLOOKUP as this would be time consuming. What I'd ideally like is a formula along the lines of: ='A2'!C5 where A2 is one of the worksheet names, and C5 is the cell on that worksheet that I want to return. Is there any easy way to do this? -- amaranth ------------------------------------------------------------------------ amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031 View this thread: http://www.excelforum.com/showthread...hreadid=393812 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro help | Excel Discussion (Misc queries) | |||
| Create a list in one worksheet of the other worksheets' names | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Lookup names to get data | Excel Worksheet Functions | |||
Linear Mode
