A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Relative Reference to another sheet?



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2004, 07:12 PM
Gary
external usenet poster
 
Posts: n/a
Default Relative Reference to another sheet?

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.


  #2  
Old March 18th, 2004, 07:30 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Relative Reference to another sheet?

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)

--
Regards
Frank Kabel
Frankfurt, Germany


Gary wrote:
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.


  #3  
Old March 18th, 2004, 07:54 PM
Eric
external usenet poster
 
Posts: n/a
Default Relative Reference to another sheet?

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
  #4  
Old March 18th, 2004, 11:40 PM
Anders S
external usenet poster
 
Posts: n/a
Default Relative Reference to another sheet?

See also a reply in your earlier thread today.

Regards
Anders Silven


"Gary" skrev i meddelandet =
news:uEl6c.37416$Zp.9518@fed1read07...
I have what I think is or should be a relatively straightforward =

question.
=20
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!
=20
An Example:
=20
ReportSheet!:
=20
Contents of cell A1:
=3DDataSheet!C14
=20
Contents of cell A2 interpretation necessary:
=3Dreferenced location in cell A1, e.g. DataSheet!C14 Add one =

column to
this
location, e.g. DataSheet!D14 indicate value at DataSheet!D14
=20
Contents of cell B5 interpretation necessary:
=3Dreferenced location in cell A1, e.g. DataSheet!C14 Add ten rows =

to this
location,
e.g. DataSheet!C24 indicate value at DataSheet!C24
=20
Contents of cell F2 interpretation necessary:
=3Dreferenced 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
=20
And so on.....
=20
=20
Your help is appreciated Thanks!
=20
-Gary
=20
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.
=20

  #5  
Old March 19th, 2004, 12:18 AM
tcross
external usenet poster
 
Posts: n/a
Default Relative Reference to another sheet?

Hi Gary,
If your collection of data is in the form of a database with collumn
headings and rows being specific to each record then DGET() works well
and will not use quite as much memory when storing the file.


---
Message posted from http://www.ExcelForum.com/

  #6  
Old March 24th, 2004, 12:10 AM
Gary
external usenet poster
 
Posts: n/a
Default Relative Reference to another sheet?

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


"Eric" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.