Reading from set Row/Column location


A

Ant

I am not an advanced user of this product and this question I think has a
simple answer, but all my research does not find a solution.

What I would like to achieve;
I am attempting to get a cell on Sheet 1 to read the contents of a
particular Row/Column cell on Sheet 2 with out it being dependant on the
particular cell.

Discussion;
This might appear simple to some but my ending condition is what defeats me.
I want the Sheet1 cell to only read from for example Row1/Column1 of sheet2.
So for example if I insert a column later into the first column location on
Sheet2 the original cell that was targeted for reading effectively is moved
to column2. Now under normal formulas the sheet1 cell will follow the
original cell to its new location and will now read from Row1/Column2.

However I actually want the Sheet1 cell to only ever read the contents from
Row1/Column1 regardless of what formatting or insertions occurs on Sheet2.

This is where my efforts collapse.

I would greatly appreciate if any one has any suggestions on how to achieve
this. I have an feeling the answer is simple and I am just not seeing it.

Thanks
 
Ad

Advertisements

M

Max

In say, Sheet2,

Place this in any start cell, eg in B2,
then copy across/fill down as far as required:
=OFFSET(Sheet1!$A$1,ROWS($1:1)-1,COLUMNS($A:A)-1)
The above will always link to whats within the corresponding range covered
in the source Sheet1, with top left anchor cell A1, regardless of new row/col
insertions in Sheet1. Try it out and convince yourself. Adapt the
sheetname/anchor cell (ie the "Sheet1!$A$1" part in the expression) to suit
the source range that you want to link.

Success? Click YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
 
Ad

Advertisements

M

Max

In say, Sheet2,

Place this in any start cell, eg in B2,
then copy across/fill down as far as required:
=OFFSET(Sheet1!$A$1,ROWS($1:1)-1,COLUMNS($A:A)-1)
The above will always link to whats within the corresponding range covered
in the source Sheet1, with top left anchor cell A1, regardless of new row/col
insertions in Sheet1. Try it out and convince yourself. Adapt the
sheetname/anchor cell (ie the "Sheet1!$A$1" part in the expression) to suit
the source range that you want to link.

Success? Click YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
 
M

Max

It should have read the other way around, sorry. Your source sheet is Sheet2.
Just change the sheetname in the expression to: Sheet2!$A$1, and you can
use/propagate it easily in Sheet1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
 
Ad

Advertisements

M

Max

It should have read the other way around, sorry. Your source sheet is Sheet2.
Just change the sheetname in the expression to: Sheet2!$A$1, and you can
use/propagate it easily in Sheet1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top