Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 9th 05, 02:41 PM
Jon
 
Posts: n/a
Default Copy text from same cell on every sheet to title sheet?

Hi,

hope someone can help on this.

What I have is a document which contains 101 separate worksheets. The
first sheet is a summary page and there are 100 other sheets which are
identical.

What I need is for the same cell on each of the 100 sheets to be copied
into the summary sheet, but moving down 1 row each time.

EG:
I want Cell A1 from sheet 1 to appear in cell A1 on the summary page.
I want cell A1 from sheet 2 to appear in cell B1 on the summary page.
I want cell A1 from sheet 3 to appear in cell C1 on the summary page etc

Can excel do this automatically?
--
www.unlockingshop.co.uk
change 'spam' to 'info' to email
Motorola V3 Razr unlocking by post - 15

  #2   Report Post  
Old February 9th 05, 03:01 PM
Max
 
Posts: n/a
Default

One way

Assuming the 100 sheets are named: Sheet1, Sheet2 ... Sheet100

In "Summary"
------------
Put in A1:

=OFFSET(INDIRECT("Sheet"&COLUMNS($A$1:A1)&"!$A$1") ,ROWS($A$1:A1)-1,)

Copy A1 across to Cv1, fill down by as many rows as required

--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----
"Jon" wrote in message
...
Hi,

hope someone can help on this.

What I have is a document which contains 101 separate worksheets. The
first sheet is a summary page and there are 100 other sheets which are
identical.

What I need is for the same cell on each of the 100 sheets to be copied
into the summary sheet, but moving down 1 row each time.

EG:
I want Cell A1 from sheet 1 to appear in cell A1 on the summary page.
I want cell A1 from sheet 2 to appear in cell B1 on the summary page.
I want cell A1 from sheet 3 to appear in cell C1 on the summary page etc

Can excel do this automatically?
--
www.unlockingshop.co.uk
change 'spam' to 'info' to email
Motorola V3 Razr unlocking by post - 15



  #3   Report Post  
Old February 9th 05, 03:11 PM
Earl Kiosterud
 
Posts: n/a
Default

Jon,

Put this in A1 of the summary sheet, and fill down:
=INDIRECT("'Sheet"&B1 & "'!" & "A1")

In Column B, put 1, 3, 3, going down. Do this by putting 1 in the first
cell, then use the Fill Handle downward holding Ctrl.

Hide Column B.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Jon" wrote in message
...
Hi,

hope someone can help on this.

What I have is a document which contains 101 separate worksheets. The
first sheet is a summary page and there are 100 other sheets which are
identical.

What I need is for the same cell on each of the 100 sheets to be copied
into the summary sheet, but moving down 1 row each time.

EG:
I want Cell A1 from sheet 1 to appear in cell A1 on the summary page.
I want cell A1 from sheet 2 to appear in cell B1 on the summary page.
I want cell A1 from sheet 3 to appear in cell C1 on the summary page etc

Can excel do this automatically?
--
www.unlockingshop.co.uk
change 'spam' to 'info' to email
Motorola V3 Razr unlocking by post - 15





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. RonMc5 Excel Discussion (Misc queries) 9 February 3rd 05 12:51 AM
Find all text instances in a sheet and add one number from each row Greg Excel Discussion (Misc queries) 1 January 31st 05 11:45 PM
Cell set to wrap text and blank line -- fix? Fred Holmes Excel Discussion (Misc queries) 0 January 25th 05 07:10 PM
how to hyperlink text to a cell steve New Users to Excel 1 December 14th 04 08:21 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 07:42 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017