How to create a fill down that increments based on the worksheet n

"Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com

> Not sure if my subject helpeds explain much.
>
> I have a "register" worksheet, that tabulates all the information
> found on the other worksheets in the database. The register worksheet
> is called "register" and the other worksheets are numbered "1", "2",
> "3" and so on.
>
> At the first row of the register worksheet, i have a lot of cells that
> reference off the "1" worksheet. Simple links so i can see all the
> information i want on one screen.
> On the second row of the register, i want the same cell locations,
> but on the second ("2") worksheet, and this continues through. The
> first column contains the worksheet number "1", "2" etc.
>
> I've hunted around with different fill techniques, and different
> reference techniques but haven't been able to find anything.
> Hopefully someone out there has a better idea than i do. Open to ALL
> ideas as its is just a new spreadsheet.

If you have absolute references in the link in row 1 you can simply copy and
past down in row2, 3 and so on. then with Find and Replace you can Replace
Sheet1 with Sheet2 in the second row and so on.


--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

Not sure if my subject helpeds explain much.

I have a "register" worksheet, that tabulates all the information found on
the other worksheets in the database. The register worksheet is called
"register" and the other worksheets are numbered "1", "2", "3" and so on.

At the first row of the register worksheet, i have a lot of cells that
reference off the "1" worksheet. Simple links so i can see all the
information i want on one screen.
On the second row of the register, i want the same cell locations, but on
the second ("2") worksheet, and this continues through. The first column
contains the worksheet number "1", "2" etc.

I've hunted around with different fill techniques, and different reference
techniques but haven't been able to find anything. Hopefully someone out
there has a better idea than i do. Open to ALL ideas as its is just a new
spreadsheet.

Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
Appreciate the help though.

"Franz" wrote:

> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
>
> > Not sure if my subject helpeds explain much.
> >
> > I have a "register" worksheet, that tabulates all the information
> > found on the other worksheets in the database. The register worksheet
> > is called "register" and the other worksheets are numbered "1", "2",
> > "3" and so on.
> >
> > At the first row of the register worksheet, i have a lot of cells that
> > reference off the "1" worksheet. Simple links so i can see all the
> > information i want on one screen.
> > On the second row of the register, i want the same cell locations,
> > but on the second ("2") worksheet, and this continues through. The
> > first column contains the worksheet number "1", "2" etc.
> >
> > I've hunted around with different fill techniques, and different
> > reference techniques but haven't been able to find anything.
> > Hopefully someone out there has a better idea than i do. Open to ALL
> > ideas as its is just a new spreadsheet.
>
> If you have absolute references in the link in row 1 you can simply copy and
> past down in row2, 3 and so on. then with Find and Replace you can Replace
> Sheet1 with Sheet2 in the second row and so on.
>
>
> --
> Hoping to be helpful...
>
> Regards
>
> Franz
>
> ----------------------------------------------------------------------------------------
> To reply translate from italian InVento (no capital letters)
> ----------------------------------------------------------------------------------------
>
>
>

Skot

You state you have worksheets named 1, 2, 3 and so on.

Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?

How far does "and so on" stretch?

Use of the INDIRECT Function is handy for incrementing sheet names in
formulas.

e.g.

Insert a new sheet and name it Namesheet.

Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
sheets)

=INDIRECT(Namesheet!A1 & "!" & "$A$4")

Drag/copy this down the column to return contents of A4 from each sheet in the
list from A1:A10

Adjust accordingly if your formulas go across rows.

If sheets are named Sheet1 etc. you can use

=INDIRECT("Sheet" & ROW() &"!" & "$A$4")

OR use Column() if across rows.


Gord Dibben Excel MVP



On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
wrote:

>Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
>Appreciate the help though.
>
>"Franz" wrote:
>
>> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
>> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
>>
>> > Not sure if my subject helpeds explain much.
>> >
>> > I have a "register" worksheet, that tabulates all the information
>> > found on the other worksheets in the database. The register worksheet
>> > is called "register" and the other worksheets are numbered "1", "2",
>> > "3" and so on.
>> >
>> > At the first row of the register worksheet, i have a lot of cells that
>> > reference off the "1" worksheet. Simple links so i can see all the
>> > information i want on one screen.
>> > On the second row of the register, i want the same cell locations,
>> > but on the second ("2") worksheet, and this continues through. The
>> > first column contains the worksheet number "1", "2" etc.
>> >
>> > I've hunted around with different fill techniques, and different
>> > reference techniques but haven't been able to find anything.
>> > Hopefully someone out there has a better idea than i do. Open to ALL
>> > ideas as its is just a new spreadsheet.
>>
>> If you have absolute references in the link in row 1 you can simply copy and
>> past down in row2, 3 and so on. then with Find and Replace you can Replace
>> Sheet1 with Sheet2 in the second row and so on.
>>
>>
>> --
>> Hoping to be helpful...
>>
>> Regards
>>
>> Franz
>>
>> ----------------------------------------------------------------------------------------
>> To reply translate from italian InVento (no capital letters)
>> ----------------------------------------------------------------------------------------
>>
>>
>>

Absolutly perfect. Thanks for that. Just used:

=INDIRECT(A2&"!"&"$B$4") which is essentially what u wrote.

Thanks a heap again, saved me a mountain of work.

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

Skot

Glad you're up and running. Thanks for the feedback.

Gord

On Tue, 10 May 2005 18:34:02 -0700, "Skot" <Skot@discussions.microsoft.com>
wrote:

>Absolutly perfect. Thanks for that. Just used:
>
>=INDIRECT(A2&"!"&"$B$4") which is essentially what u wrote.
>
>Thanks a heap again, saved me a mountain of work.
>
>"Gord Dibben" wrote:
>
>> Skot
>>
>> You state you have worksheets named 1, 2, 3 and so on.
>>
>> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>>
>> How far does "and so on" stretch?
>>
>> Use of the INDIRECT Function is handy for incrementing sheet names in
>> formulas.
>>
>> e.g.
>>
>> Insert a new sheet and name it Namesheet.
>>
>> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
>> sheets)
>>
>> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>>
>> Drag/copy this down the column to return contents of A4 from each sheet in the
>> list from A1:A10
>>
>> Adjust accordingly if your formulas go across rows.
>>
>> If sheets are named Sheet1 etc. you can use
>>
>> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>>
>> OR use Column() if across rows.
>>
>>
>> Gord Dibben Excel MVP
>>
>>
>>
>> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
>> wrote:
>>
>> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
>> >Appreciate the help though.
>> >
>> >"Franz" wrote:
>> >
>> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
>> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
>> >>
>> >> > Not sure if my subject helpeds explain much.
>> >> >
>> >> > I have a "register" worksheet, that tabulates all the information
>> >> > found on the other worksheets in the database. The register worksheet
>> >> > is called "register" and the other worksheets are numbered "1", "2",
>> >> > "3" and so on.
>> >> >
>> >> > At the first row of the register worksheet, i have a lot of cells that
>> >> > reference off the "1" worksheet. Simple links so i can see all the
>> >> > information i want on one screen.
>> >> > On the second row of the register, i want the same cell locations,
>> >> > but on the second ("2") worksheet, and this continues through. The
>> >> > first column contains the worksheet number "1", "2" etc.
>> >> >
>> >> > I've hunted around with different fill techniques, and different
>> >> > reference techniques but haven't been able to find anything.
>> >> > Hopefully someone out there has a better idea than i do. Open to ALL
>> >> > ideas as its is just a new spreadsheet.
>> >>
>> >> If you have absolute references in the link in row 1 you can simply copy and
>> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
>> >> Sheet1 with Sheet2 in the second row and so on.
>> >>
>> >>
>> >> --
>> >> Hoping to be helpful...
>> >>
>> >> Regards
>> >>
>> >> Franz
>> >>
>> >> ----------------------------------------------------------------------------------------
>> >> To reply translate from italian InVento (no capital letters)
>> >> ----------------------------------------------------------------------------------------
>> >>
>> >>
>> >>
>>
>>

Gord, Using your formula, can the same principle apply to data from different
workbooks all having identical worksheets and name ranges. If yes, (1)how
should
the formula be and (2) can the information be read from closed workbooks.
This is a late posting and I shall watch this topic for any responses.
Thank you,
RobertR

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>

I tried this and I get (#REF!) in the cell. What am I doing wrong
Thankyou in advance for your help

Cindy

"Gord Dibben" wrote:

> Skot
>
> You state you have worksheets named 1, 2, 3 and so on.
>
> Do you mean Sheet1, Sheet2, Sheet3 or just 1, 2, 3?
>
> How far does "and so on" stretch?
>
> Use of the INDIRECT Function is handy for incrementing sheet names in
> formulas.
>
> e.g.
>
> Insert a new sheet and name it Namesheet.
>
> Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
> sheets)
>
> =INDIRECT(Namesheet!A1 & "!" & "$A$4")
>
> Drag/copy this down the column to return contents of A4 from each sheet in the
> list from A1:A10
>
> Adjust accordingly if your formulas go across rows.
>
> If sheets are named Sheet1 etc. you can use
>
> =INDIRECT("Sheet" & ROW() &"!" & "$A$4")
>
> OR use Column() if across rows.
>
>
> Gord Dibben Excel MVP
>
>
>
> On Tue, 10 May 2005 14:58:02 -0700, "Skot" <Skot@discussions.microsoft.com>
> wrote:
>
> >Unfortunatly, I will have 1000's of rows, which makes that a lengthy process.
> >Appreciate the help though.
> >
> >"Franz" wrote:
> >
> >> "Skot" <Skot@discussions.microsoft.com>ha scritto nel messaggio
> >> 0AE679C8-2F12-4F10-878A-D3615E90604A@microsoft.com
> >>
> >> > Not sure if my subject helpeds explain much.
> >> >
> >> > I have a "register" worksheet, that tabulates all the information
> >> > found on the other worksheets in the database. The register worksheet
> >> > is called "register" and the other worksheets are numbered "1", "2",
> >> > "3" and so on.
> >> >
> >> > At the first row of the register worksheet, i have a lot of cells that
> >> > reference off the "1" worksheet. Simple links so i can see all the
> >> > information i want on one screen.
> >> > On the second row of the register, i want the same cell locations,
> >> > but on the second ("2") worksheet, and this continues through. The
> >> > first column contains the worksheet number "1", "2" etc.
> >> >
> >> > I've hunted around with different fill techniques, and different
> >> > reference techniques but haven't been able to find anything.
> >> > Hopefully someone out there has a better idea than i do. Open to ALL
> >> > ideas as its is just a new spreadsheet.
> >>
> >> If you have absolute references in the link in row 1 you can simply copy and
> >> past down in row2, 3 and so on. then with Find and Replace you can Replace
> >> Sheet1 with Sheet2 in the second row and so on.
> >>
> >>
> >> --
> >> Hoping to be helpful...
> >>
> >> Regards
> >>
> >> Franz
> >>
> >> ----------------------------------------------------------------------------------------
> >> To reply translate from italian InVento (no capital letters)
> >> ----------------------------------------------------------------------------------------
> >>
> >>
> >>
>
>