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" wrote in message
...

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" wrote in message
...

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" wrote in message
...

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" wrote in message
...

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