Excel 2007 : Appending Sheet number automatically by dragging cells in an excel formula

**Ram Kumar** · 07-12-2011, 08:27 AM

Hi,

i want help in appending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.

A1 cell ='9'!$D$9
A2 cell ='10'!$D$9
A3 cell ='11'!$D$9....etc

Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.

can anybody help.

**protonLeah** · 07-12-2011, 08:18 PM

I don't quite understand what you are doing. Note that '9' and 'Sheet9' are two different sheets. '9' cannot refer to Sheet9 since you can have both in the same workbook at the same time. Also, you don't specify the layout of the book. Suppose in sheet TEST, you put the formula:

Please Login or Register  to view this content.

in a cell in row one. The formula will build '9'!D$9
Then you can drag the formula down.

**dani9211** · 12-05-2012, 02:49 AM

i have 100 sheets from '1 to 100', i have same values in every sheet. i used $ operator to keep constant the cell number, when i drag the formula down, it keep the right value but it don't change the sheet number.
e.g.
i have a value "Apple" in Sheet1!A2, Same value in Sheet2!A3 and go on till Sheet100. i need a formula so i can drag and it changes the sheet number no the cell number....hope you understand my question....

**arlu1201** · 12-05-2012, 03:56 AM

Dani9211,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.