Newbie question

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

-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.
 
It occurred to me that an example might be helpful:

ReportSheet!:

Contents of cell A1:
=DataSheet!C14

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

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

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

And so on.....


Your help is appreciated Thanks!

-Gary
 
Hi Gary,

If I understand you right, the problem is how to get a reference to the cell that A1 on the ReportSheet points to on the DataSheet. AFAIK that is not directly possible in Excel. Also, I don't think it is possible to read the formula in another cell (oddly?).

Anyway, here are two suggestions on how to solve this:

----

First,

If the IPAQ supports Excel VBA you can use a User Defined Function to get the formula of a cell.
Hit Alt+F11 to open the VBA editor, do Insert>Module and paste the following code (the function has no error checking, there is an extra charge for that):

Function getFormula(cRef As Range) As String
getFormula = cRef.Formula
End Function

Then you can use the following formulas (as per your example) where the last two arguments are the offset for Row and Column, respectively:

A1: =DataSheet!C15
A2: =OFFSET(INDIRECT(MID(getformula(A1),2,1000)),0,1)
B5: =OFFSET(INDIRECT(MID(getformula(A1),2,1000)),10,0)
F2: =OFFSET(INDIRECT(MID(getformula(A1),2,1000)),-2,1)

----

Second,

You can use a separate cell to enter the "base" reference on the DataSheet (C15 in your example), I used H1:

A1: =INDIRECT("DataSheet!"&H1)
A2: =OFFSET(INDIRECT("DataSheet!"&H1),0,1)
B5: =OFFSET(INDIRECT("DataSheet!"&H1),10,0)
F2: =OFFSET(INDIRECT("DataSheet!"&H1),-2,1)
 
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
Hi Gary,

If I understand you right, the problem is how to get a reference to the cell
that A1 on the ReportSheet points to on the DataSheet. AFAIK that is not
directly possible in Excel. Also, I don't think it is possible to read the
formula in another cell (oddly?).

Anyway, here are two suggestions on how to solve this:

----

First,

If the IPAQ supports Excel VBA you can use a User Defined Function to get
the formula of a cell.
Hit Alt+F11 to open the VBA editor, do Insert>Module and paste the following
code (the function has no error checking, there is an extra charge for
that):

Function getFormula(cRef As Range) As String
getFormula = cRef.Formula
End Function

Then you can use the following formulas (as per your example) where the last
two arguments are the offset for Row and Column, respectively:

A1: =DataSheet!C15
A2: =OFFSET(INDIRECT(MID(getformula(A1),2,1000)),0,1)
B5: =OFFSET(INDIRECT(MID(getformula(A1),2,1000)),10,0)
F2: =OFFSET(INDIRECT(MID(getformula(A1),2,1000)),-2,1)

----

Second,

You can use a separate cell to enter the "base" reference on the DataSheet
(C15 in your example), I used H1:

A1: =INDIRECT("DataSheet!"&H1)
A2: =OFFSET(INDIRECT("DataSheet!"&H1),0,1)
B5: =OFFSET(INDIRECT("DataSheet!"&H1),10,0)
F2: =OFFSET(INDIRECT("DataSheet!"&H1),-2,1)
 
Back
Top