Reply to thread

Message: [QUOTE="Chip Pearson, post: 13880256"] You can build up a text string any way you want and then use INDIRECT to convert it to an actual range reference. For example, if your worksheets are named Sheet1, Sheet2 and so on, enter the following in, say, G5 and copy down as far as you need to go. =INDIRECT("Sheet"&ROW()-ROW($G$5)+1&"!A1") Change the $G$5 to the first cell in which this formula appears, and change the A1 to the cell on each worksheet whose value you want to retrieve. If your sheets are not so logically named, first create a list of your worksheets starting in, say, H10. and enter the following formula in the first cell where the returned list is to begin and copy down as far as you need to go. Watch carefully the combinations of single quotes and double quotes. =INDIRECT("'"&H10&"'!A1") Both formulas work by using the & concatenate operator with cell references to build a text string that is a syntactically correct cell address. INDIRECT then takes that text string and converts it to an actual range reference and returns the value of that range. In general, you can use an INDIRECT function anywhere you would normally use a cell address. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC [URL="http://www.cpearson.com"]www.cpearson.com[/URL] [/QUOTE]