Looking up a cell by row & column

[Postal]

I'm using Excel to track experimental data collected on a daily basis
The data is formated with rows containing tracked experimenta
parameters, and columns corresponding to a specific run of th
experiment. The leftmost column containts the most recent data, an
columns to the right increasingly older data from previos runs. Fo
every new run of the experiment, a new lefmost column is inserted an
filled with the appropriate data. This is simple and works fine.

In a seperate sheet, I track the 10 most recent experiment runs fo
graphing, performance regressions, etc, by refering to the "master
sheet that contains all data ever collected. I need the entries in thi
second sheet to "mirror" the 10 leftmost columns in the "master" sheet
This again works fine, except...

When I add a new leftmost column to the master sheet, the second shee
that mirrors it no longer mirrors the 10 leftmost columns (so columns
through 9 in the excel sheet for example), but the rather it tracks th
same 10 columns it tracked before adding the new column (so columns
through 10 with the new added column).

What I need is for the second sheet to always mirror specific row an
column entries from the master table (columns 0 through 9 in thi
case), regardless of how I modify the master sheet by adding and/o
deleting columns. Currently, the second sheet always mirrors the sam
10 experimental runs, which is not what I need...

Any suggestions on how to do this?

Thanks in advance for any advice

 

[Bob Phillips]

Hi Postal,

On the second sheet, put this formula in A1, and copy across and down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Postal]

Hi Bob,

Thanks for replying, but the formula you're refering to seems to b
missing... or am I just misunderstanding what you want me to do

 

[Bob Phillips]

Ooops!

=INDIRECT("Sheet1!"&CHAR(COLUMN()+64)&ROW())

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Postal]

Hi again,

Almost there. The problem is that on my second "mirror" sheet, th
layout of the cells is not the same as the "master" sheet. I was hopin
to be able to use the ADDRESS command, but Excel doesn't allow me t
embed it into the command you provided. For example:

=INDIRECT("Sheet1!"&CHAR(COLUMN(ADDRESS(6, 7, 4))+64)&ROW(ADDRESS(6, 7
4)))

Where ADDRESS(6, 7, 4) returns the address of the start of the table i
the "master" sheet.

Any way to get this to work

 

[Bob Phillips]

That won't work because I was using the mirror layout as indexes back into
Sheet 1.

If you know the cell addresses, and just want to avoid the problem you
described, load INDIRECT explicitly

=INDIRECT("Sheet1!A1")

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)