concatenation

Hi,

I have multiple lists of details which I have concatenated into a new list.

ie week number (42) period number (10) and week commencing date (09/01/06)
now reads "Week 42, Period 10, wc 09/01/06".

My problem is getting a seperate worksheet to read this list of information,
but allow the new worksheet to lookup the list without adding in the amount
of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of new
worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel to
reference one sheet in sequencial cells but the new worksheet to miss the
spare cells I need to use for other data? I do not want to individually link
each cell.

hope I have explained myself,

Matt

You have sheet 1 with values/formulas that will be in every row
You have sheet 2 with values/formulas that will be in every 9th row

Which sheet has the values and which has the formulas that need to link to
the other

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"mattguerilla" <mattguerilla@discussions.microsoft.com> wrote in message
news:A0B501FE-2D6B-4AC3-A77A-4CD4C9B48AF7@microsoft.com...
>
> Hi,
>
> I have multiple lists of details which I have concatenated into a new
> list.
>
> ie week number (42) period number (10) and week commencing date (09/01/06)
> now reads "Week 42, Period 10, wc 09/01/06".
>
> My problem is getting a seperate worksheet to read this list of
> information,
> but allow the new worksheet to lookup the list without adding in the
> amount
> of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of
> new
> worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel
> to
> reference one sheet in sequencial cells but the new worksheet to miss the
> spare cells I need to use for other data? I do not want to individually
> link
> each cell.
>
> hope I have explained myself,
>
> Matt

this is basically for a calendar tool. The first sheet, has concatenated
week number, period number etc and appears on every row (52 weeks). The
second sheet has a break down for each day in the year, and the top of each
page needs to link to the correct concatenated cell in sheet one. So, the
first sheet has data on each row, while the second needs to link to that data
every tenth row.

Hope you can help. I want to be able to change the data in the first sheet
to make each new year easy to create.

"Ken Wright" wrote:

> You have sheet 1 with values/formulas that will be in every row
> You have sheet 2 with values/formulas that will be in every 9th row
>
> Which sheet has the values and which has the formulas that need to link to
> the other
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ------------------------------Â*------------------------------Â*----------------
> It's easier to beg forgiveness than ask permission :-)
> ------------------------------Â*------------------------------Â*----------------
>
>
>
> "mattguerilla" <mattguerilla@discussions.microsoft.com> wrote in message
> news:A0B501FE-2D6B-4AC3-A77A-4CD4C9B48AF7@microsoft.com...
> >
> > Hi,
> >
> > I have multiple lists of details which I have concatenated into a new
> > list.
> >
> > ie week number (42) period number (10) and week commencing date (09/01/06)
> > now reads "Week 42, Period 10, wc 09/01/06".
> >
> > My problem is getting a seperate worksheet to read this list of
> > information,
> > but allow the new worksheet to lookup the list without adding in the
> > amount
> > of spare rows. IE, reference cell a1 (concatenated data) into cell a1 of
> > new
> > worksheet, then cell a2 linked to cell a10 and so on. How do I get Excel
> > to
> > reference one sheet in sequencial cells but the new worksheet to miss the
> > spare cells I need to use for other data? I do not want to individually
> > link
> > each cell.
> >
> > hope I have explained myself,
> >
> > Matt
>
>
>

Lets suppose your data starts on row 2 in A2 on sheet 1 and goes down
through A53

Lets also suppose you want to link cells

A10 on sheet 2 to A2 on sheet1
A20 on sheet 2 to A3 on sheet1
A30 on sheet 2 to A4 on sheet1
A40 on sheet 2 to A5 on sheet1
etc

In cell A10 on sheet 2, if you put the following formula in:-

=OFFSET(Sheet1!$A$1,ROW()/10,0)

then select A1:A10 on sheet 2, and grab the fill handle at the very bottom
right of the selection and drag down to about A530, you will see that every
10th row has been populated with a link to a cell on sheet 1, where it has
incremented the linked row by only 1 row each time.

Does this help?

Regards
Ken....................

"mattguerilla" <mattguerilla@discussions.microsoft.com> wrote in message
news:E9BA13E8-28C8-452E-A919-4DD26A0E197C@microsoft.com...
>
> this is basically for a calendar tool. The first sheet, has concatenated
> week number, period number etc and appears on every row (52 weeks). The
> second sheet has a break down for each day in the year, and the top of
> each
> page needs to link to the correct concatenated cell in sheet one. So, the
> first sheet has data on each row, while the second needs to link to that
> data
> every tenth row.
>
> Hope you can help. I want to be able to change the data in the first
> sheet
> to make each new year easy to create.
>
> "Ken Wright" wrote:
>
>> You have sheet 1 with values/formulas that will be in every row
>> You have sheet 2 with values/formulas that will be in every 9th row
>>
>> Which sheet has the values and which has the formulas that need to link
>> to
>> the other
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP - Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> ------------------------------*------------------------------*----------------
>> It's easier to beg forgiveness than ask permission :-)
>> ------------------------------*------------------------------*----------------
>>
>>
>>
>> "mattguerilla" <mattguerilla@discussions.microsoft.com> wrote in message
>> news:A0B501FE-2D6B-4AC3-A77A-4CD4C9B48AF7@microsoft.com...
>> >
>> > Hi,
>> >
>> > I have multiple lists of details which I have concatenated into a new
>> > list.
>> >
>> > ie week number (42) period number (10) and week commencing date
>> > (09/01/06)
>> > now reads "Week 42, Period 10, wc 09/01/06".
>> >
>> > My problem is getting a seperate worksheet to read this list of
>> > information,
>> > but allow the new worksheet to lookup the list without adding in the
>> > amount
>> > of spare rows. IE, reference cell a1 (concatenated data) into cell a1
>> > of
>> > new
>> > worksheet, then cell a2 linked to cell a10 and so on. How do I get
>> > Excel
>> > to
>> > reference one sheet in sequencial cells but the new worksheet to miss
>> > the
>> > spare cells I need to use for other data? I do not want to
>> > individually
>> > link
>> > each cell.
>> >
>> > hope I have explained myself,
>> >
>> > Matt
>>
>>
>>