Use value from another cell in formula

Question

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.

Answer 1

If I understood correctly, you should check out the VLOOKUP function.

If you have a chart with names at the first column and lets say height on the second one, you can use VLOOKUP to find the height of a person, by searching his name in the chart.

For example: Column A has a list of names: E11=Tom, E12=Ben, E13=Dan. Column B has their height in cms: F11=182, F12=169, F13=177.

In Cell D40 you have the name Ben. You want to display his height in cell D41. So, in cell D41 you should type:

=VLOOKUP(D40,E11:F13,2,FALSE)

Where D40 is the name you are looking for in the chart, E11:F13 is the chart you are searching in, 2 is the chart's column number from which you want to extract the value (which in this case will be column E), and FALSE means you want an exact match of the person's name.

You can combine this with IFNA - a function that gives you the value you want if it is available, and another value of your choice if the function returns #N/A.

In this case, it would be:

=IFNA(VLOOKUP(D40,E11:F13,2,FALSE),"No Record")

Answer 2

I think I now understand what you wanted to do, but I'm leaving my other answer in case VLOOKUP does have the potential to improve your sheet.

If having each row visually empty is enough, you could use IFNA in the other columns, like so:

=IFNA(AVERAGEIF('Overnight Tours'!A:A, "<<< name in cell A52 >>>>", 'Overnight Tours'!I:I)," ")

and drag them down much as you need in advance (let's say you won't have more than 300 guides - then drag it down to B352).

It basically means you will see a white space if there is no name in A52 (or if the function returns #N/A in case the data in 'Overnight Tours' doesn't fit the formula), and the result otherwise.

Now every time column A will have a new name in it, columns B to F will update accordingly from a white space to what you need it to show.