Lookup across worksheets

ctaylor415 · May 16th, 2002, 07:30 AM

I have a file that contains a number of identical sheets. I now want to create a summary sheet. Is it possible to have a formula which changes the sheet depending on a cell containing the sheet name.

sann0638 · May 16th, 2002, 08:26 AM

hello there,
i use the Indirect function eg
=INDIRECT(A1&"B2") will return cell B2 on the sheet whose name is in cell A1 of the current sheet. I find that copy and past this function, it is best to use row and column offset, so that to get cell "B5" you need =OFFSET(INDIRECT(A1&"B2"),row_offset,column_offset)

There may well be a better way of doing this, mind.

Rgds,
mike

ctaylor415 · May 17th, 2002, 05:09 AM

Thanks. I have not used that one before. I have found that the following works

INDIRECT(ADDRESS(rownum,colnum,1,0,sheetname),0)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 07:30 AM	#1
ctaylor415 New Member Join Date: May 2002 Location: Chris Taylor Posts: 3	I have a file that contains a number of identical sheets. I now want to create a summary sheet. Is it possible to have a formula which changes the sheet depending on a cell containing the sheet name.

May 16th, 2002, 08:26 AM	#2
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	hello there, i use the Indirect function eg =INDIRECT(A1&"B2") will return cell B2 on the sheet whose name is in cell A1 of the current sheet. I find that copy and past this function, it is best to use row and column offset, so that to get cell "B5" you need =OFFSET(INDIRECT(A1&"B2"),row_offset,column_offset) There may well be a better way of doing this, mind. Rgds, mike

May 17th, 2002, 05:09 AM	#3
ctaylor415 New Member Join Date: May 2002 Location: Chris Taylor Posts: 3	Thanks. I have not used that one before. I have found that the following works INDIRECT(ADDRESS(rownum,colnum,1,0,sheetname),0)