Offseting a cell reference to a second worksheet

Nico123

New Member
Joined
Aug 18, 2005
Messages
7
I have a range of cells on sheet1 referencing a second worksheet so that cell A1 on sheet 1 contains;
Code:
   =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
Code:
 =OFFSET(Sheet2!M105,99,0).

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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,288
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

New Member
Joined
Aug 18, 2005
Messages
7
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???? :rolleyes:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,327
Messages
5,769,460
Members
425,550
Latest member
Bonebeast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top