Keeping data updated between 2 sheets... novice needs help.

**FatMagic** · 10-14-2005, 11:59 AM

Good afternoon,

My question is a bit hard to explain.. but I will do my best to convey it.

I have two spreadsheets in an Excel file. In the first sheet I have "raw data" (hereon called RawData). The second sheet (hereon called DisplayData) contains basic formulas that just pull the data from RawData, and arranges them in the way I want them displayed (ultimately for printing). This excel spreadsheet will be used by laymen who don't have time (or the understanding) to fool with layouts for their data. So I am trying to take data, and arrange it into a easily updatable document. All they will need to do is update the data in RawData, and DisplayData will reflect the changes for printing. Basically I'm trying to reduce the redundancies that they've already created.

Creating the links between the two sheets are easy, just hit "=" and point it to the sheet, then the cell. I set the formula to not change by using the string prefix before the row and the cell number. (I.e. ='Raw Data'!$E$2). Thats not a problem, I can do THAT much.

The problem is occuring when I insert a row on RawData. It does not reflect that change on DisplayData. Instead it tries to act "smart" and updates all of the formulas in DisplayData to make sure that it retains the correct data and layout (i.e. ='Raw Data'!$E$2 becomes ='Raw Data'!$E$3 when I want it to stay the same). Well I don't WANT it to retain the look. When I insert a row in RawData, I want DisplayData to reflect that, and bump all the data down one row.

How can I stop this "smart" updating from happening?

Or is it not possible? I've heard that this is not possible.

Thanks ahead of time for any advice!

-Chris B

**swatsp0p** · 10-14-2005, 12:14 PM

You can use the INDEX function to refer to your RAWDATA sheet, as such:

=INDEX(RAWDATA!$A$1:$J$23,ROW(3),1)

This will return the data from A3 regardless of how many rows are inserted between A1 and A3.

There are various approaches to writing this formula, depending on your data layout, e.g.:

=INDEX(RAWDATA!$A$1:$J$23,ROW(),1) Will return the the value from the matching row that contains the formula and the row within the data range, e.g. if this formula is in G21 on DisplayData, it will return the value in A21 on RAWDATA. Similarly, =INDEX(RAWDATA!$A$1:$J$23,ROW(),COL()) in the same G21, would return the value from G21.

Will this meet your needs?

**FatMagic** · 10-14-2005, 12:42 PM

The solution you explain is exactly what I am looking for.

I tried to implement it, and I keep getting the "Formula you typed contains and error" pop-up. Here is what I modified in what you gave me:

=INDEX('Raw Data'!$A$1:$K$397,ROW(3),4)

This is how I broke it down -- 'Raw Data' is the name of the sheet. I assumed the $A$1:$K$397 was defining the area within Raw Data. I tried to access Row 3, column 4 -- hoping to access data within cell D4 -- but to no avail.

Would it be helpful if I attached an example of what I'm trying to accomplish? Or possibly you can attach an example of this formula working?

Thank you very very much.. we're almost there.

-Chris B

Todkerr · 10-14-2005, 01:05 PM

If you want the worksheet formula to ALLWAYS refer to the same cell - even if
you are inserting lines, etc....use "indirect." On your Disply tab make the
formula:

=indirect("rawdata!D4")

Note that there are quotes here. You need them. Indirect returns a
reference to the TEXT in its argument. The above formula will reference cell
D4 no matter what you do the rewdata sheet.

HTH

Tod

"FatMagic" wrote:

>
> Good afternoon,
>
> My question is a bit hard to explain.. but I will do my best to convey
> it.
>
> I have two spreadsheets in an Excel file. In the first sheet I have
> "raw data" (hereon called RawData). The second sheet (hereon called
> DisplayData) contains basic formulas that just pull the data from
> RawData, and arranges them in the way I want them displayed (ultimately
> for printing). This excel spreadsheet will be used by laymen who don't
> have time (or the understanding) to fool with layouts for their data.
> So I am trying to take data, and arrange it into a easily updatable
> document. All they will need to do is update the data in RawData, and
> DisplayData will reflect the changes for printing. Basically I'm trying
> to reduce the redundancies that they've already created.
>
> Creating the links between the two sheets are easy, just hit "=" and
> point it to the sheet, then the cell. I set the formula to not change
> by using the string prefix before the row and the cell number. (I.e.
> ='Raw Data'!$E$2). Thats not a problem, I can do THAT much.

>
> The problem is occuring when I insert a row on RawData. It does not
> reflect that change on DisplayData. Instead it tries to act "smart" and
> updates all of the formulas in DisplayData to make sure that it retains
> the correct data and layout (i.e. ='Raw Data'!$E$2 becomes ='Raw
> Data'!$E$3 when I want it to stay the same). Well I don't WANT it to
> retain the look. When I insert a row in RawData, I want DisplayData to
> reflect that, and bump all the data down one row.
>
> How can I stop this "smart" updating from happening?

Or is
> it not possible? I've heard that this is not possible.
>
> Thanks ahead of time for any advice!
>
> -Chris B
>
>
> --
> FatMagic
> ------------------------------------------------------------------------
> FatMagic's Profile: http://www.excelforum.com/member.php...o&userid=10701
> View this thread: http://www.excelforum.com/showthread...hreadid=476270
>
>

Dave O · 10-14-2005, 01:05 PM

You can't stop the "smart" updating, but there is a workaround. It
sounds like your RawData tab is not truly raw data, but instead has
some grouping or arranging that you maintain manually. (Not being
critical; truly raw data would be in the form of a database with many
records in any order that are subsequently arranged into a report or
other format.)

If RawData!E2 is the currently active number for Sales, for example,
and all the Sales data is grouped in RawData in a particular spot, then
you could insert a new column into RawData that includes the date.
Since all the Sales data is grouped and you need the most recent entry
to show up in DisplayData, you can write a formula in DisplayData that
looks in the rows pertaining to Sales, picks up the most recent date,
and returns the data from column E associated with it.

This is just an idea, based on guesses and speculation. Post an
example of real data and someone here will be able to point you in the
right direction.