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

Posts
3
Registration date
Friday July 12, 2019
Status
Member
Last seen
July 13, 2019
- - Latest reply: vcoolio
Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
- 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!!!
See more 

3 replies

Best answer
Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
201
1
Thank you
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.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6219 users have said thank you to us this month

cmigoya
Posts
3
Registration date
Friday July 12, 2019
Status
Member
Last seen
July 13, 2019
-
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!!!!!
Respond to vcoolio
Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
201
1
Thank you
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.

Say "Thank you" 1

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6219 users have said thank you to us this month

cmigoya
Posts
3
Registration date
Friday July 12, 2019
Status
Member
Last seen
July 13, 2019
-
Perfect!!!! Thanks for breaking it down
Respond to vcoolio
Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
201
0
Thank you
You're welcome Cmigoya. I'm glad to have been able to assist.

Cheerio,
vcoolio.
Respond to vcoolio