I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
with similar format. The top row a1 to a10 has cells with heading . I add new
this similar sheets continuously with changing data in b1 to b10. Now can I
make sheet1 as index of those sheets and how to transfer pasting the contents
of b1 to b10 data of every sheet into the row cells of sheet1 automatically
as and when the sheets I add?

Assume your source sheets are simply named as numbers: 1,2,3,... and you want
to retrieve the contents in cells B1, B2, B3, ... from each sheet into a
summary sheet

In your summary sheet,
List the source sheetnames in B1 across, eg: 1,2,3, ...
List the cell references in A2 down, eg: B1, B2, ...

Then place in B2:
=INDIRECT("'"&B$1&"'!"&$A2)
Copy B2 across/fill down to populate the contents from all the source sheets

If you need it with an error trap to return neat looking blanks ("") for any
source sheets not existing as yet, you could use instead in B2:
=IF(ISERROR(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIRECT("'"&B$1&"'!"&$A2))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Narnimar" wrote:
> I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
> with similar format. The top row a1 to a10 has cells with heading . I add new
> this similar sheets continuously with changing data in b1 to b10. Now can I
> make sheet1 as index of those sheets and how to transfer pasting the contents
> of b1 to b10 data of every sheet into the row cells of sheet1 automatically
> as and when the sheets I add?

I am confused by your statement "The top row a1 to a10 has cells with heading"

A1:A10 is a one column range, not the top row.

Where exactly are your headings located?

And where exactly is the data located?


Gord Dibben MS Excel MVP


On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar <(E-Mail Removed)>
wrote:

>I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
>with similar format. The top row a1 to a10 has cells with heading . I add new
>this similar sheets continuously with changing data in b1 to b10. Now can I
>make sheet1 as index of those sheets and how to transfer pasting the contents
>of b1 to b10 data of every sheet into the row cells of sheet1 automatically
>as and when the sheets I add?

Dear Gord Dibben,

Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data
will be in row A2 to J2. Thanks for your help.


"Gord Dibben" wrote:

> I am confused by your statement "The top row a1 to a10 has cells with heading"
>
> A1:A10 is a one column range, not the top row.
>
> Where exactly are your headings located?
>
> And where exactly is the data located?
>
>
> Gord Dibben MS Excel MVP
>
>
> On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar <(E-Mail Removed)>
> wrote:
>
> >I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
> >with similar format. The top row a1 to a10 has cells with heading . I add new
> >this similar sheets continuously with changing data in b1 to b10. Now can I
> >make sheet1 as index of those sheets and how to transfer pasting the contents
> >of b1 to b10 data of every sheet into the row cells of sheet1 automatically
> >as and when the sheets I add?
>
>

Sorry Max for my wrong statement. I mean the Heading row A1 to J1 and the data
will be in row A2 to J2. Thanks for your further help.

"Narnimar" wrote:

> I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
> with similar format. The top row a1 to a10 has cells with heading . I add new
> this similar sheets continuously with changing data in b1 to b10. Now can I
> make sheet1 as index of those sheets and how to transfer pasting the contents
> of b1 to b10 data of every sheet into the row cells of sheet1 automatically
> as and when the sheets I add?

Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over?

To link, run this macro after you have made the new sheet and entered data in
A2:J2

The macro will add the links to the first blank row below existing data on Index
sheet

Sub linkit()
Dim rng As Range
Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng.Formula = "='" & ActiveSheet.Name & "'!A2"
Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight
End Sub

To copy, run this macro to copy contents to first blank row.

Sub copyit()
Dim rng As Range
Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
ActiveSheet.Range("A2:J2").Copy Destination:=rng
End Sub


Gord

On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar <(E-Mail Removed)>
wrote:

>
>Dear Gord Dibben,
>
>Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data
>will be in row A2 to J2. Thanks for your help.
>
>
>"Gord Dibben" wrote:
>
>> I am confused by your statement "The top row a1 to a10 has cells with heading"
>>
>> A1:A10 is a one column range, not the top row.
>>
>> Where exactly are your headings located?
>>
>> And where exactly is the data located?
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>> On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar <(E-Mail Removed)>
>> wrote:
>>
>> >I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
>> >with similar format. The top row a1 to a10 has cells with heading . I add new
>> >this similar sheets continuously with changing data in b1 to b10. Now can I
>> >make sheet1 as index of those sheets and how to transfer pasting the contents
>> >of b1 to b10 data of every sheet into the row cells of sheet1 automatically
>> >as and when the sheets I add?
>>
>>

Thanks for the quick reply. I have checked it, it links the data of A to J
of the same sheet (named Index). But I need this sheet is to be an index
sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no.
of sheets)I add continuously need to be copied or linked. Your further
assistance is highly appreciated. Thanks.

"Gord Dibben" wrote:

> Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over?
>
> To link, run this macro after you have made the new sheet and entered data in
> A2:J2
>
> The macro will add the links to the first blank row below existing data on Index
> sheet
>
> Sub linkit()
> Dim rng As Range
> Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
> .Offset(1, 0)
> rng.Formula = "='" & ActiveSheet.Name & "'!A2"
> Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight
> End Sub
>
> To copy, run this macro to copy contents to first blank row.
>
> Sub copyit()
> Dim rng As Range
> Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
> .Offset(1, 0)
> ActiveSheet.Range("A2:J2").Copy Destination:=rng
> End Sub
>
>
> Gord
>
> On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar <(E-Mail Removed)>
> wrote:
>
> >
> >Dear Gord Dibben,
> >
> >Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data
> >will be in row A2 to J2. Thanks for your help.
> >
> >
> >"Gord Dibben" wrote:
> >
> >> I am confused by your statement "The top row a1 to a10 has cells with heading"
> >>
> >> A1:A10 is a one column range, not the top row.
> >>
> >> Where exactly are your headings located?
> >>
> >> And where exactly is the data located?
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >>
> >> On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar <(E-Mail Removed)>
> >> wrote:
> >>
> >> >I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
> >> >with similar format. The top row a1 to a10 has cells with heading . I add new
> >> >this similar sheets continuously with changing data in b1 to b10. Now can I
> >> >make sheet1 as index of those sheets and how to transfer pasting the contents
> >> >of b1 to b10 data of every sheet into the row cells of sheet1 automatically
> >> >as and when the sheets I add?
> >>
> >>
>
>

Don't run either macro from Index sheet.

Run while the sheet you just added is the ActiveSheet


Gord


On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar <(E-Mail Removed)>
wrote:

>Thanks for the quick reply. I have checked it, it links the data of A to J
>of the same sheet (named Index). But I need this sheet is to be an index
>sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no.
>of sheets)I add continuously need to be copied or linked. Your further
>assistance is highly appreciated. Thanks.
>
>"Gord Dibben" wrote:
>
>> Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over?
>>
>> To link, run this macro after you have made the new sheet and entered data in
>> A2:J2
>>
>> The macro will add the links to the first blank row below existing data on Index
>> sheet
>>
>> Sub linkit()
>> Dim rng As Range
>> Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
>> .Offset(1, 0)
>> rng.Formula = "='" & ActiveSheet.Name & "'!A2"
>> Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight
>> End Sub
>>
>> To copy, run this macro to copy contents to first blank row.
>>
>> Sub copyit()
>> Dim rng As Range
>> Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
>> .Offset(1, 0)
>> ActiveSheet.Range("A2:J2").Copy Destination:=rng
>> End Sub
>>
>>
>> Gord
>>
>> On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar <(E-Mail Removed)>
>> wrote:
>>
>> >
>> >Dear Gord Dibben,
>> >
>> >Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data
>> >will be in row A2 to J2. Thanks for your help.
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> I am confused by your statement "The top row a1 to a10 has cells with heading"
>> >>
>> >> A1:A10 is a one column range, not the top row.
>> >>
>> >> Where exactly are your headings located?
>> >>
>> >> And where exactly is the data located?
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >>
>> >> On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar <(E-Mail Removed)>
>> >> wrote:
>> >>
>> >> >I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
>> >> >with similar format. The top row a1 to a10 has cells with heading . I add new
>> >> >this similar sheets continuously with changing data in b1 to b10. Now can I
>> >> >make sheet1 as index of those sheets and how to transfer pasting the contents
>> >> >of b1 to b10 data of every sheet into the row cells of sheet1 automatically
>> >> >as and when the sheets I add?
>> >>
>> >>
>>
>>

No problem with this revised spec:
> .. the data will be in row A2 to J2.

as the suggestion to use indirect is easily adaptable

Just change the line in my response:
> List the cell references in A2 down, eg: B1, B2, ...

to read as:
List the cell references in A2 down, eg: A2, B2, C2, ... J2

Then use the same formula suggested in B2, viz, either:
=INDIRECT("'"&B$1&"'!"&$A2)

or
=IF(ISERROR(INDIRECT("'"&B$1&"'!"&$A2)),"",INDIRECT("'"&B$1&"'!"&$A2))

and copy B2 across/fill down to populate the contents from all the source
sheets
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Gord Dibben,
I got the point. I prefer your cell linking macro. But it adds up same data
if I run it repeatedly. Can you do further some modification in the macro so
that it does not add up rows again which are already updated once from the
sheets? Thanks.

"Gord Dibben" wrote:

> Don't run either macro from Index sheet.
>
> Run while the sheet you just added is the ActiveSheet
>
>
> Gord
>
>
> On Sun, 9 Dec 2007 12:31:00 -0800, Narnimar <(E-Mail Removed)>
> wrote:
>
> >Thanks for the quick reply. I have checked it, it links the data of A to J
> >of the same sheet (named Index). But I need this sheet is to be an index
> >sheet. The data in the A2 to J2 of the other sheets (named 1, 2, 3,...n no.
> >of sheets)I add continuously need to be copied or linked. Your further
> >assistance is highly appreciated. Thanks.
> >
> >"Gord Dibben" wrote:
> >
> >> Do you want the new sheet's A2:J2 linked to the Index sheet or just copied over?
> >>
> >> To link, run this macro after you have made the new sheet and entered data in
> >> A2:J2
> >>
> >> The macro will add the links to the first blank row below existing data on Index
> >> sheet
> >>
> >> Sub linkit()
> >> Dim rng As Range
> >> Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
> >> .Offset(1, 0)
> >> rng.Formula = "='" & ActiveSheet.Name & "'!A2"
> >> Sheets("Index").Range(rng.Address & ":J" & rng.Row).FillRight
> >> End Sub
> >>
> >> To copy, run this macro to copy contents to first blank row.
> >>
> >> Sub copyit()
> >> Dim rng As Range
> >> Set rng = Sheets("Index").Cells(Rows.Count, 1).End(xlUp) _
> >> .Offset(1, 0)
> >> ActiveSheet.Range("A2:J2").Copy Destination:=rng
> >> End Sub
> >>
> >>
> >> Gord
> >>
> >> On Sun, 9 Dec 2007 08:15:01 -0800, Narnimar <(E-Mail Removed)>
> >> wrote:
> >>
> >> >
> >> >Dear Gord Dibben,
> >> >
> >> >Sorry for my wrong statement. I mean the Heading row A1 to J1 and the data
> >> >will be in row A2 to J2. Thanks for your help.
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> I am confused by your statement "The top row a1 to a10 has cells with heading"
> >> >>
> >> >> A1:A10 is a one column range, not the top row.
> >> >>
> >> >> Where exactly are your headings located?
> >> >>
> >> >> And where exactly is the data located?
> >> >>
> >> >>
> >> >> Gord Dibben MS Excel MVP
> >> >>
> >> >>
> >> >> On Sat, 8 Dec 2007 09:20:01 -0800, Narnimar <(E-Mail Removed)>
> >> >> wrote:
> >> >>
> >> >> >I have a work book that has a no. of sheets fro sheet 2 to n no. no sheets
> >> >> >with similar format. The top row a1 to a10 has cells with heading . I add new
> >> >> >this similar sheets continuously with changing data in b1 to b10. Now can I
> >> >> >make sheet1 as index of those sheets and how to transfer pasting the contents
> >> >> >of b1 to b10 data of every sheet into the row cells of sheet1 automatically
> >> >> >as and when the sheets I add?
> >> >>
> >> >>
> >>
> >>
>
>