Reading from set Row/Column location

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

Try this:

=INDIRECT("Sheet2!A1")

That will *always* refer to Sheet2 A1.

--
Biff
Microsoft Excel MVP


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

Use INDIRECT

=INDIRECT("Sheet2!A1")
--
If this post helps click Yes
---------------
Jacob Skaria


"Ant" wrote:

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

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
---
"Ant" wrote:
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

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
---