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 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Jul 13, 2019 at 08:08 PM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Jul 13, 2019 at 08:08 PM
Related:
- Replace 'sheet' reference in a formula (not copy data, but get averages)
- How to transfer data from one sheet to another in excel using formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Little alchemy cheat sheet - Guide
- Mark sheet grade formula in excel download - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
3 responses
vcoolio
Posts
1410
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Jul 13, 2019 at 09:19 AM
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:-
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.
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.
vcoolio
Posts
1410
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Jul 13, 2019 at 12:22 PM
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.
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.
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.
cmigoya
Posts
3
Registration date
Friday July 12, 2019
Status
Member
Last seen
July 13, 2019
Jul 13, 2019 at 12:44 PM
Jul 13, 2019 at 12:44 PM
Perfect!!!! Thanks for breaking it down
vcoolio
Posts
1410
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Jul 13, 2019 at 08:08 PM
Jul 13, 2019 at 08:08 PM
You're welcome Cmigoya. I'm glad to have been able to assist.
Cheerio,
vcoolio.
Cheerio,
vcoolio.
Jul 13, 2019 at 11:08 AM
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!!!!!