Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm not entirely sure how to properly explain this, but here we go...

I'm trying to create a single budgeting document that allows me to manage purchasing and reconciliation for multiple projects. I would like to create separate sheets per project and have purchased items populate on a master sheet.

Using conditional formatting, I've set one of the columns to display an item's status (waiting for approval, approved, ordered, received). I would like the contents of an entire row to populate in a new sheet table once the status is set to "Received." The sheet should update descendingly.

screenshot

Any help is greatly appreciated.

share|improve this question
    
Mike, for the most part it sounds like everything you want to do is achievable, and with simple excel formulas. In lieu of a picture, can you give us an example of the project worksheet and an example of the master sheet? All I'm looking for is the column headings and an example row. If the data format is not uniform, just provide the format of the data you want. Also provide a reference for where the data is starting (ie first cell is A1) –  N8sBug May 29 '14 at 9:39
    
@N8sBug Thanks for the response. Having trouble formatting in the comment. I took a screen shot using some dummy data that should be helpful: lh4.googleusercontent.com/-2urKIld6n0M/U4cyuqI8UEI/AAAAAAAAD9Q/… –  Mike Bodes May 29 '14 at 13:16
    
Mike, thanks for this. Because of the way you want to do this, I think a macro would be best suited for it. However, it looks like you might be using google spreadsheets is that correct? –  N8sBug May 29 '14 at 13:19
    
The reason I ask is that the scripts will be radically different if google spreadsheets is the intended target application to run the calculation. –  N8sBug May 29 '14 at 13:31
    
@N8sBug that's correct, I would like to use Google Spreadsheets if possible because the document is shared and updated by my team. If this function isn't possible in Google Spreadsheets, I could use Excel. Thanks for your help with this. –  Mike Bodes May 29 '14 at 14:53

1 Answer 1

up vote 0 down vote accepted

After reviewing your question a little closer, I determined that you would need to write a google script to accomplish this.

All you need to do is: open up the google script editor, make a new project, paste this in the bottom of the script, rename the "Reconciliation" to be whatever the name of your spreadsheet is, and then save it. The onEdit() is a trigger built into google scripts that does the work of updating after an edit, and the checkReceived() is a hand rolled example that does the work you seek. I tailored this "example" to your image provided. You will see that you can add as many projects as you want and this will cover them just the same. You can change the particulars to match whatever else you need (ie you should also note that I called the master sheet "Master"), but this should work for you. Please let me know how it goes.

function checkReceived() {


  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  //The following to ensures you don't run this on another spreadsheet
  if(spreadsheet.getName()=="Reconciliation") //<--This is the name of the spreadsheet I used, change it for yours.
  {  

    var maxIntervalSS = spreadsheet.getNumSheets();
    var theMasterSheet = spreadsheet.getSheetByName("Master");
    var masterSheetID = theMasterSheet.getIndex();
    var sheets = spreadsheet.getSheets();
    var thisRow = theMasterSheet.getRange(1,1); //Arbitrary for initialization

    //Need to Clear existing master data.
    theMasterSheet.getDataRange().clear();

    //Will use a flag to repopulate header.
    var firsttime= true;

    //Iterates through each spreadsheet
    for(var checkingSheetIterator = 0; checkingSheetIterator<maxIntervalSS; checkingSheetIterator++)
    {

      var currentSheet = sheets[checkingSheetIterator];

      //Ignores the iteration if ID equals the master sheet
      if(currentSheet.getIndex()!=masterSheetID)
      {




        //Getting currentSheet's data
        var currentRange = currentSheet.getDataRange();

        //Iterates through the currentSheet's data
        for(var rows = 1; rows<=currentSheet.getLastRow(); rows++)
        {
          //Repopulating header on first time.              
          //"8" for column H, containing the Status
          if(currentRange.getCell(rows,8).getValue()=="Received"|| firsttime)  //<-Note this is your keyword and specified location; "8".
          {  //Add to bottom of sheet
            theMasterSheet.appendRow([currentRange.getCell(rows,1).getValue(),currentRange.getCell(rows,2).getValue(),
                                      currentRange.getCell(rows,3).getValue(),currentRange.getCell(rows,4).getValue(),
                                      currentRange.getCell(rows,5).getValue(),currentRange.getCell(rows,6).getValue(),
                                      currentRange.getCell(rows,7).getValue(),currentRange.getCell(rows,8).getValue(),
                                      currentRange.getCell(rows,9).getValue(),currentRange.getCell(rows,10).getValue(),
                                      currentRange.getCell(rows,11).getValue()]);
            firsttime=false;
          }
        }
      }
    }
  }
};  

function onEdit(){

  checkReceived();

};

The following is an example of the spreadsheet setup. Notice the name of the spreadsheet is Reconciliation (top of the picture), and the tabs (ie sheets) at the bottom of the page are named "Master", "Project1", and "Project2". Please note, that I didn't bother with the conditional formatting as it shouldn't have any bearing on the outcome. enter image description here

share|improve this answer
    
Thanks so much. I'll give this a shot and let you know how it goes! –  Mike Bodes May 30 '14 at 18:37
    
No problem! Again, just remember to edit the sheet name(I used "Master") to match the sheet name of you master sheet, and to replace the spreadsheet's name (I used "Reconciliation") with your spreadsheet name. –  N8sBug May 30 '14 at 18:40
    
If I'm pulling multiple spreadsheets into that Master sheet, can I add them into this expression: if(spreadsheet.getName()=="Reconciliation")? I.e. if(spreadsheet.getName()=="Reconciliation", "Sheet2", "Sheet3") –  Mike Bodes May 30 '14 at 18:43
    
So, we need to be clear about terminology. Are you talking about adding in new individual sheets, or are you implying that are going to copy and paste tables of data into the master sheet? Or using this function on separate collections of sheets (in excel they are called "Books")? If you use this function on new Spreadsheets, you will need to make sure the master sheet is named the same as your original. –  N8sBug May 30 '14 at 18:50
    
I added a photo to be clear about what is going on. I believe this is how you are doing it. One sheet (ie "Project1) per project and a master (ie "Master") sheet that summarizes the received orders. Please let me know if this is not what you meant. –  N8sBug May 30 '14 at 19:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.