• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating auto-populating workbooks

V

videsupra

New Member
Hey all,


So I've been googling for a few hours and can't find anything, thought I'd come here to ask.


I am trying to build two sheets, lets call them Original and Distribution.


The end goal is to have the Distribution sheet pull out relevant data from the original so as to present it in a different way.


My biggest hurdle is how do I set it to auto-insert new rows/new data into Distribution when said data is added to Original?


Example: I insert a row into Original and fill in all of the data. Distribution automatically recognizes the action and inserts a new row into the Distribution sheet, pulling all the selected data.


I can get it to pull the selected data, but I can't get it to recognize additions and pull those (ie. the formula linking to the Original cell auto-updates to its new position whenever I insert a new row in Original).


Also, and this is a tangent, is there a way to have the data transcribed in reverse? Meaning whatever I put into Original is presented in reverse order in Distribution? I imagine it is just setting the formula up correctly but I am a complete noob to excel intricacies.


Any and all help is appreciated, thank you!
 
To have the cell formulas ignore the adding/deleting or rows, I'd suggest using the INDIRECT function. Something like:

=INDIRECT("Original!R"&ROW(A1)&"C"&COLUMN(A1),FALSE)

Copied down would always pull data from cell A1. Note that you can copy that formula down as needed. This formula uses R1C1 style referencing, so change the cell reference within the COLUMN function to change the column reference, and the cell within the ROW function to change the row reference.


Reverse order? I suppose you might be able to do something like this:

=INDIRECT("Original!R"&COUNTA(Original!$A:$A)-ROW(A1)&"C"&COLUMN(A1),FALSE)


If you need to handle errors on that, could exand that to:

IF(ROW(A1)>COUNTA(Original!$A:$A),"",INDIRECT("Original!R"&COUNTA(Original!$A:$A)-ROW(A1)&"C"&COLUMN(A1),FALSE))
 
So the formulas still link to the original.


Ex - when I insert a new row, the formula adjusts accordingly. Is there a way to set it to NOT update and stay static? I believe it has to do with $ signs in the formula but I can't get it to work right.


I guess, let me try and explain it this way.


If I have a cell at A1 on Original, I want it to copy to A1 on Distribution

If I insert a new row with a red cell (color just for illustrative purposes) above A1, I now want A1 on Distribution to show the red cell and for the old A1 to now be displayed in its new position at A2. Does that make sense?
 
Hmm, your desired effect is what should be happening. Did you copy the formulas exactly? All active cell references should only be pointing the the Distribution sheet, except for the one COUNTA function (which, since it's looking at an entire column, shouldn't matter).


Testing on my computer, the formula I posted is working correctly. Could you copy the formula you are using directly to the forum so we can see if something else is going wrong?
 
=INDIRECT("Original!R"&ROW(Original!A19)&"C"&COLUMN(A1),FALSE)


Is the second Original what is causing the problem? Testing without now.


Edit - Seems to be working properly, must have gotten that inserted incorrectly. Will adjust the spreadsheet and update accordingly. Thanks!
 
You must log in or register to reply here.
Back
Top