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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|