AutoFill reference multiple worksheets

**Joeg101** · 10-03-2007, 10:58 PM

Hi,
I need to reference the first cell in each worksheet down a single column.

Example:
cell A1 on Sheet1 is equal to cell A1 on Sheet2
cell A2 on Sheet1 is equal to cell A1 on Sheet3
cell A3 on Sheet1 is equal to cell A1 on Sheet4
cell A4 on Sheet1 is equal to cell A1 on Sheet5

Is there an automated way to do this, as I am working with over 200 worksheets in a single Workbook.

Thank you in advance for your time,
Joe

**starguy** · 10-03-2007, 11:29 PM

Originally Posted by Joeg101

Hi,
I need to reference the first cell in each worksheet down a single column.

Example:
cell A1 on Sheet1 is equal to cell A1 on Sheet2
cell A2 on Sheet1 is equal to cell A1 on Sheet3
cell A3 on Sheet1 is equal to cell A1 on Sheet4
cell A4 on Sheet1 is equal to cell A1 on Sheet5

Is there an automated way to do this, as I am working with over 200 worksheets in a single Workbook.

Thank you in advance for your time,
Joe

Try this in Sheet1 A1 and copy it down

=INDIRECT("Sheet"&ROW(A2)&"!A1")

You should not change the Sheet names for this formula to work.

**Joeg101** · 10-04-2007, 10:23 AM

That worked!

Thank you so much Starguy. I really appreciate it.

Be Well,
Joe

**starguy** · 10-05-2007, 02:44 AM

Originally Posted by Joeg101

That worked!

Thank you so much Starguy. I really appreciate it.

Be Well,
Joe

You're welcome. Good to see that your problem is solved.

**Joeg101** · 02-03-2008, 09:01 PM

Is there a way to use this formula in which I can name the sheets? Each sheet needs to be a persons name.

=INDIRECT("sheet" &ROW(A2)&"!A1")

Thank in advance for your time. I greatly appreciate it.

-Joe

**husnain.** · 06-03-2015, 11:36 PM

Originally Posted by starguy

Try this in Sheet1 A1 and copy it down

=INDIRECT("Sheet"&ROW(A2)&"!A1")

You should not change the Sheet names for this formula to work.

Dear Starguy, what if i need to do the reverse action? "I am working on multi worksheets and managed to have a summary sheet that contains the name of each worksheet in column B.
Now i have another column F that contains specific values against all worksheets. I want these values to be auto filled on respective Worksheets in Cell location D3 of each worksheet.
i am trying to find the solution but it seems only you can help me."

**FDibbins** · 06-04-2015, 12:19 AM

husnain, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

(also, this thread is over 7 years old, I doubt anyone is still monitoring it