I have two sheets

Sheet1

and sheet2

What I want to do is one by one copy the values of each row from sheet 1 to sheet 2. whenever one row is copied from sheet1 to sheet2 (i.e values from cell A to F). the cell G2 and H2 (in sheet2) get values ( based on multiple different formulas filter function, pivit tables etc from other sheets ) When I get the updated values in sheet 2 I want to copy the values form G2 and H2 to the respective row back in sheet 1

basically I want to use the sheet2 as a custom function where cell A2, B2, C2, D2, H2, F2 are inputs and cell G2 and H2 are outputs.

I tried using macros but what is required is a combination of both Absolute and Relative referencing and that doesn't seem to be possible

gif of the process I am doing manually

CodePudding user response：

Yes, you can write VBA code for the same. That will automatically do these tasks and saves lots of time. Call me if you wanted VBA code for the same- 7388121138

CodePudding user response：

Did it with appscript and menue items

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('AERON')
      .addItem('Calculate Single Cabnets', 'menuItem1')
      .addSeparator()
      .addItem('Calculate Multiple Cabnets', 'menuItem2')
      .addToUi();
}
 
function menuItem2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selection = sheet.getSelection();
 
  if(selection.getActiveRange().getA1Notation()==null)
  ui.alert('No range selected');
 
  else{
    //get the inputs 
    var sheetname = SpreadsheetApp.getActiveSheet().getName();
    // SpreadsheetApp.getUi().alert(sheetname);
 
    var range = SpreadsheetApp.getActiveSpreadsheet().getRange(selection.getActiveRange().getA1Notation());
    var input = range.getValues();
 
    var ac = SpreadsheetApp.getActiveSpreadsheet().getActiveRange()
    SpreadsheetApp.getUi().alert(ac);
 
    // get sheet name 
    sheetname = SpreadsheetApp.getActiveSheet().getName()
 
    const numRows = ac.getNumRows();
    for (let i=1; i<= numRows;i   ){
      var cell = ac.getCell(i, 1).offset(0, 0,1,6).getA1Notation();
      var cell1 = ac.getCell(i, 1).offset(0, 7,1,2).getA1Notation();
      // SpreadsheetApp.getUi().alert(cell   " "  sheetname   " "   cell1);
      single_row(sheetname, cell, cell1 );
 
    }
 
    SpreadsheetApp.getUi().alert("All values updated")
 
  }
}
 
function single_row(sheetName, inputRange, outputRange ) {
  var fnSheetName = "Main";
  var fnInputRange = 'A2:F2';
  var fnOutputRange = 'G2:H2';
 
  var input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(inputRange).getValues();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(fnSheetName).getRange(fnInputRange).setValues(input);
  var output = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(fnSheetName).getRange(fnOutputRange).getValues();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(outputRange).setValues(output);
}

This creates a menue item on the top which one by one copies the values to the other sheet then copies the result back