Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Keeping data updated between 2 sheets... novice needs help.
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 |
#2
|
|||
|
|||
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? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=476270 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 -- FatMagic ------------------------------------------------------------------------ FatMagic's Profile: http://www.excelforum.com/member.php...o&userid=10701 View this thread: http://www.excelforum.com/showthread...hreadid=476270 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
Thanks a million Tod! The INDIRECT function worked perfectly :) -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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Sorting Data from 2 sheets, one sheet which is already sorted | Excel Worksheet Functions | |||
Chart sheets cause data sheets to be blank | Charts and Charting in Excel | |||
Summary of data from 20 sheets | Excel Discussion (Misc queries) | |||
To data apearing in other sheets I can use =SUM(. How I can have . | Excel Worksheet Functions |