Replace 'sheet' reference in a formula (not copy data, but get averages)

Solved/Closed
cmigoya Posts 3 Registration date Friday July 12, 2019 Status Member Last seen July 13, 2019 - Jul 12, 2019 at 09:07 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 13, 2019 at 08:08 PM
Hello,

I'm working with a precipitation data base of multiple sheets. Each sheet is a year of measurements.

The thing is that I'm trying to get the average of several municipalities for each year. Is there a way I can automatically replace the 'sheet' in the formula so I don't have to change it manually?

Example: =AVERAGE('1996'!P$9:P$13)
Similar formulas run like this until row 57.
All sheets are numbered according to the year, Can I do a fast copy paste in a way that it replace the sheet name? Ex. '2003', '2006'

Thanks!!!
Related:

3 responses

vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jul 13, 2019 at 09:19 AM
Hello Cmigoya,

I'm assuming that you would like to reference the different sheets from a "Master" sheet.

In the "Master" sheet, place all your sheet names in one cell (e.g. cell X1) using data validation. Make sure that they are spelled exactly the same as the sheet names.

You can then use the INDIRECT function wrapped in the AVERAGE function to reference each sheet as follows:-

=AVERAGE(INDIRECT("'"&$X$1&"'!P:P"))


Select a sheet name from the data validation drop down list in X1 and you'll then notice that the formula will make calculations for that sheet.

The cell reference X1 has to be absolute so make sure that you use the $ symbols as shown in the formula above.

I hope that this helps.

Cheerio,
vcoolio.
1
cmigoya Posts 3 Registration date Friday July 12, 2019 Status Member Last seen July 13, 2019
Jul 13, 2019 at 11:08 AM
Hi vcoolio!!!

When I place all the sheet names in one cell, should I place comas? should I enter them just with a space in between?

Most importantly: will the outcome of that wrapped function be an average for EACH year (n= 20) or an average of all years? I'm looking for the first.

Thanks!!!!!
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jul 13, 2019 at 12:22 PM
Hello Cmigoya,

Select the cell in which you wish to place the drop down. In the ribbon at the top of the sheet, select "Data" then, in the "Data Tools" group select "Data Validation".
In the dialogue box that then appears, select "List" from the "Allow" drop down. Ensure that both the "Ignore blank" and the "In-cell drop down" boxes are ticked. Next, in the Source box that appears, type in your sheet names like this:-

Sheet1,Sheet2,Sheet3 (use your actual sheet names here).

with commas as shown but no spaces. Don't place a comma after the last sheet name. Click OK.

Your list of sheet names should now appear in the selected cell.

Most importantly: will the outcome of that wrapped function be an average for EACH year (n= 20) or an average of all years? I'm looking for the first. 

As I don't know the set out of your workbook, I can't answer your question. You'll have to try the function and see for yourself if the correct result appears. However, based on the formula that you supplied, as far as I can tell, it will average whatever numerical data you have in your selected range (Column P) for the selected year sheet.

Cheerio,
vcoolio.
1
cmigoya Posts 3 Registration date Friday July 12, 2019 Status Member Last seen July 13, 2019
Jul 13, 2019 at 12:44 PM
Perfect!!!! Thanks for breaking it down
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Jul 13, 2019 at 08:08 PM
You're welcome Cmigoya. I'm glad to have been able to assist.

Cheerio,
vcoolio.
0