I have a Google Spreadsheets with a list of names in Column A of Sheet 4. The names are automatically pulled from sheet 5 with the following formula:
=IMPORTRANGE("url", "Setup!b2:b1000")
I would like to automatically populate the formulas in columns B - F to use the name of the guide in that specific row.
The formula in cell B52 in the example would be something like the following:
=AVERAGEIF('Overnight Tours'!A:A, "<<< name in cell A52 >>>>", 'Overnight Tours'!I:I)
Is there a way to do this without having to drag the cells down or copy/paste them?
Ideally I would like to be able to add the name of a new guide to the list, and have it automatically added to the results tab (so far that works) and then have the formulas to calculate the average scores for each item inserted without having to modify the sheet.