Relative Reference to another sheet?

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I have what I think is or should be a relatively straightforward question.

I have a data sheet that is designed for a very small screen, i.e. an IPAQ.
From the various and voluminous amounts of data on this sheet I would like
to pull a "Report" in a new sheet, hence the "Report Sheet". I would like to
format the new sheet such that all I have to do in input a "Single
Reference" in a single cell on the "Report Sheet". This "Single Reference"
would point to the data sheet. The "Report Sheet", by way of some kind of
relative referencing to the "Single Reference", would then spontaneously
populate the entire report. My data sheet is organized such that I can
always use relative references to my "Single Reference". I hope I have
iterated this well enough so that someone will be able to assist me. Thanks!

An Example:

ReportSheet!:

Contents of cell A1:
=DataSheet!C14

Contents of cell A2 <interpretation necessary>:
=<referenced location in cell A1, e.g. DataSheet!C14> <Add one column to
this
location, e.g. DataSheet!D14> <indicate value at DataSheet!D14>

Contents of cell B5 <interpretation necessary>:
=<referenced location in cell A1, e.g. DataSheet!C14> <Add ten rows to this
location,
e.g. DataSheet!C24> <indicate value at DataSheet!C24>

Contents of cell F2 <interpretation necessary>:
=<referenced location in cell A1, e.g. DataSheet!C14> <Subtract two rows and
add one
column to this location, e.g. DataSheet!D12> <indicate value at
DataSheet!D12>

And so on.....


Your help is appreciated Thanks!

-Gary

P.S. I have played for hours with the following functions, to no avail:
INDIRECT, OFFSET, ADDRESS, CELL, and T. I believe that one or more of these
are involved in the solution, but cannot discern which.
 
Hi Gary
not quite sure but try the following:
In A1 just enter the base reference as TEXT (not as formula). so simply
enter
DataSheet!C14
without the equation sign. This has to be a valid reference!

in A2 enter
=OFFSET(INDIRECT(A1),0,1)

B5:
=OFFSET(INDIRECT(A1),10,0)

F2:
=OFFSET(INDIRECT(A1),-2,1)
 
I'm not sure I understand exactly what you need. However,
let's say you want to pull row 1 from your "Data Sheet"
data. If you put a one in Cell A1, the following formula
would pull the value in A1 from the Data Sheet.

=OFFSET('Data Sheet'!$A$1,0,'Report Sheet'!$A1-1)

Conversely, if you just put the words "Data Sheet" in cell
A1, the following would use that to pull in the contents
of A1 from the Data Sheet:

=OFFSET(INDIRECT("'"&A$1&"'!$A$1"),0,0)

I hope that helps or at least gives you some direction.

Eric
 
Hi Gary,
If your collection of data is in the form of a database with collum
headings and rows being specific to each record then DGET() works wel
and will not use quite as much memory when storing the file
 
Thank you all for your timely and valuable responses! It seems that Eric
suggestion worked out nicely for me. I will give examples of my formulas
that you helped to create:

A1:
c18

A2:
=OFFSET(INDIRECT("Sheet1!"&$A1&""),0,-2)

I will post an example of the spreedsheet if anyone has an interest.

-Gary
 
Back
Top