Offseting a cell reference to a second worksheet

[Nico123]

I have a range of cells on sheet1 referencing a second worksheet so that cell A1 on sheet 1 contains;

   =Sheet2!M105

All my references to sheet two were manually created, but the format of my data on sheet2 repeats itself four times (thought the vaules are different). If I could offset my references (and copy & paste?), I will save myself the drudge of manually creating 75% of the remaining references.

How can I copy a range of these cell references on sheet1 (elsewhere on sheet 1) , but offset the reference of all the references on sheet1 to sheet2 worksheet by 99 rows.

In effect, I would like an easy way to write/copy the above from Cell A1 in sheet1 to cell A400 in sheet 1 in the below format

 =OFFSET(Sheet2!M105,99,0).

Is there an easy way to do this? Any help welcome!

 

[Anthony47]

Sorry nico,
I didn't understand how data is organized on Sheet2 and how you need to translate them on Sheet1. Could you retry?

Bye.

 

[Nico123]

Hi,

In essence, I have data (contract pricing) on sheet 2 containing information for a particular product dispersed over several rows.

Each product on sheet2 contains between 2 and 8 rows of data to each product. Each product is available in one of 14 regions, which are split into different columns across sheet2. This group of about 15 products (on sheet2 with data split between multiple rows) creates the basic format of how my source data is presented. This format is repeated down the page of sheet two with different values for each product. (the values change because of payment method and product duration). I have four blocks of rates below one another all in the same format.

I am translating each product for each region into a single row of data on sheet1. I have manually linked the first block of data in sheet 2 by selecting a cell in sheet1 and selcting the cell to link to in sheet 2 (this took a long time).

As the format of my data on sheet2 consists of 4 blocks repeating in the same format for each product and region, I know that the cell 99 rows below A1 relates to the same product and same region (but holds a different value), I want to copy the cell references and offset by 99 rows. See my above point. When I use the offset functinon and point to the cell on sheet1 with the reference, if creates an offset from sheet one, rather than an offset from the link reference on sheet2!
I am sure there may have been an easier way around this. One solution would have been to allow me to copy a formula dow a column in sheet1, but have the formula increment by columns acrioss sheet2 without the row in the formulae incrementing. Alas, I could not do this either!

Failing a solution, I can do the manual link thing again.

Hope this makes thingts a little clearer????