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

[FatMagic]

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]

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 ar
inserted between A1 and A3.

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

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

Will this meet your needs

 

[Todkerr]

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]

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

I tried to implement it, and I keep getting the "Formula you type
contains and error" pop-up. Here is what I modified in what you gav
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.
assumed the $A$1:$K$397 was defining the area within Raw Data. I trie
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 t
accomplish? Or possibly you can attach an example of this formul
working?

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

-Chris

 

[Dave O]

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.

 

[FatMagic]

Thanks a million Tod!

The INDIRECT function worked perfectly

-Chris