Let in a google sheet all cell changes value according to F2 cell whose value can be 1,2,3,4,5,6,7,8,9,10,11. Now write a formula to find the value of B49 cell when F2=1. The formula should work on another sheet also.
F2 and B49 is in a sheet named ‘SchoolWiseList’. I am going to use the funcion in a cell of a sheet named ‘ContactPerson’.
If it is possible then please find the sum of all values of B49 when F2=1 to 11.
B49 also changes when F2 changes and it already has function assigned to it.
B49 has the function =MAX(A5:A48)
A5,A6,..A48 has function like =IF(COUNTA($B5:$E5)>0, IFERROR(INDEX($A$4:$A4, MATCH(1, --($B$4:$B4=$B5)*--($D$4:$D4=$D5), 0)), IFERROR(MAX($A$4:$A4), 0)+1), "")
I just want to get the values of B49 when F2 changes it value.
More Explanation:
This table is the details of competitors in a school event. F2 represents the school no. C2 represents the school name. A column represent the serial number of the competitors. B,C,D,E columns represent the details of the competitors. So when F2 changes C2 changes i.e. school name changes and all fields changes accordingly. That is how A column changes and the value of B49 changes. I want to get the total no of competitors for a particular school. Do you need more explanation? Please help me.
Kurumba Daspara F P School is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
7
I have added this function to the apps script and named it total.gs
function recordB49Values() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SchoolWiseList');
const resultSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Total1');
const originalF2Value = sheet.getRange('F2').getValue();
for (let i = 1; i <= 11; i++) {
sheet.getRange('F2').setValue(i);
SpreadsheetApp.flush();
const B49Value = sheet.getRange('B49').getValue();
resultSheet.getRange(i + 1, 1).setValue(i);
resultSheet.getRange(i + 1, 2).setValue(B49Value);
}
sheet.getRange('F2').setValue(originalF2Value);
SpreadsheetApp.flush();
}
Then I have added a sheet named Total1. In the Total1 sheet,I set up the headers:
Cell A1: School Number
Cell B1: Total Competitors
Then I run the function recordB49Values from the apps script.
Kurumba Daspara F P School is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1