Sep 05 2019 07:36 AM
Hi. Thank you for taking the time to help me.
I have created about 90 sheets in excel, each sheet for a different person. This is a service group where first column is weekly dates for a year (each a weekly meeting 5 points). Eleven headers across the top listing various events where the person can receive points (for example "attendance"). Those numbers will accumulate down the columns throughout the year to show yearly totals. I also totaled the weekly horizontal row to show what the member did in a week (5 points for attendance that week and 10 points that week for giving blood).
So, here is my problem. I have totaled each column at the bottom vertically to show cumulative points and then totaled each row to show weekly totals for each sheet.
Now, we are wanting to link each grand total on a separate sheet by name to show the total row from the bottom of each individual sheet. My problem (and I hope I'm explaining this well enough for you to understand :) is on my total sheet where I am linking the totals on some of the rows I'm getting an error message that changes the totals for other people already on my total sheet.
When I created each individual sheet I copied my original formulas to new sheets and renamed the sheets. I
=Rudolph!B57
Please help.
Thank you so much.
Sep 05 2019 09:36 PM
@MarshaP Your question seems to end abruptly, in the middle of a sentence. Can you please edit it and finish the description of the problem?
Sep 06 2019 06:07 AM
Sep 08 2019 03:03 PM
@MarshaP Sorry, I can't really tell what the problem is.
Can you post a sample file with just a few (like three) sheets and explain in context? Replace real names with dummy names. It does not have to be the original layout. Simplify it to a few rows of data. It's about the principle, not the specifics.
Sep 09 2019 06:24 AM
Sep 09 2019 02:35 PM
@MarshaP When you reply, there is a paper clip icon below the main text box. Above and to the left of the Cancel/Post buttons.
Sep 10 2019 05:51 AM
Sep 10 2019 02:41 PM
SolutionHello again @MarshaP ,
the data structure in this workbook is not ideal. Reporting across sheets is never really simple. I will give you a formula that solves your immediate problem, but I will also give you a recommendation for a better approach.
First, the formula solution. Excel does not have an easy way to use sheet names in cells and then reference the data in the respective sheet. The Indirect function can be used, but in many cases it is messy and confusing. In the attached file, I have used the following formula in cell B2 of the Totals sheet:
=INDEX(INDIRECT("'"&$A2&"'!57:57"),COLUMN())
The formula has been copied across to column M and then down to row 4.
The Indirect() function uses the text in cell A2 to create a reference to row 57 of that sheet. This is fed to the Index function, which retrieves the value in that row, from the column that corresponds to the current column. Doesn't look pretty, but it works.
You will notice that the formula throws errors in row 4. That is because of a typo in the person's name. It is not the same as in the Sheet tab. I don't know which one is the correct name. You need to ensure that the names in the Totals sheet are identical with the Sheet names.
Now to a better approach.
You're currently entering data into sheets that look like reports already. The data entry grid is designed to contain many blank cells. It is quite clear that not every person will have entries for each date, but the grid has rows for each date and columns for each entry type. You're also entering data into over 90 different sheets. The latter fact rings all my alarm bells. It is always difficult to report across sheets. It is always better to enter data into only ONE sheet only and then use Excel's tools to report on that data.
I have added a data entry sheet with just four columns: Name, Date, Category, and Points. Category is a data validation list from the helper table next to the data entry table. The points are looked up with a Vlookup formula from the helper table. The data entry table is an Excel Table with tools in the columnn headers to sort and filter as you require. All formulas, formatting, and pick lists will automatically be applied to new rows of data.
I have entered the data from the three sample sheets.
With this data entry table, there are no empty cells. Data is only entered for people and dates and categories that actually happened.
Then there is the Report sheet. It has a pivot table that looks at the data in the data entry table. All names from the data entry table and all categories from the data entry table are listed in the pivot table report and the points are totaled accordingly. If new data is added to the data entry table, just click the Data ribbon > Refresh All, and the pivot table will show the updated results.
There is also a People Sheet report with a pivot table filtered to a particular person. It details each event that the person got points for. You can click and change the filter to a different person and the same sheet will show the data for a different person.
You have collected the same data, produced the same report, with a lot less effort and a much smaller footprint (three sheets).
I hope this has given you a little taste of what Excel is capable of.
Let me know how you get on.
Sep 11 2019 06:01 AM
Thank you very much. I will apply your formula and information to my work. Your expertise and help is greatly appreciated. It might be next week before I can make the changes but I will certainly update you on my progress.
Again, thank you so much for your expertise and help.
Marsha
Sep 11 2019 02:48 PM
@MarshaP thanks for the feedback. Can you please mark my post above (the one with the formula) as the "Best Response"? Then it will be easier for others to find.
Sep 10 2019 02:41 PM
SolutionHello again @MarshaP ,
the data structure in this workbook is not ideal. Reporting across sheets is never really simple. I will give you a formula that solves your immediate problem, but I will also give you a recommendation for a better approach.
First, the formula solution. Excel does not have an easy way to use sheet names in cells and then reference the data in the respective sheet. The Indirect function can be used, but in many cases it is messy and confusing. In the attached file, I have used the following formula in cell B2 of the Totals sheet:
=INDEX(INDIRECT("'"&$A2&"'!57:57"),COLUMN())
The formula has been copied across to column M and then down to row 4.
The Indirect() function uses the text in cell A2 to create a reference to row 57 of that sheet. This is fed to the Index function, which retrieves the value in that row, from the column that corresponds to the current column. Doesn't look pretty, but it works.
You will notice that the formula throws errors in row 4. That is because of a typo in the person's name. It is not the same as in the Sheet tab. I don't know which one is the correct name. You need to ensure that the names in the Totals sheet are identical with the Sheet names.
Now to a better approach.
You're currently entering data into sheets that look like reports already. The data entry grid is designed to contain many blank cells. It is quite clear that not every person will have entries for each date, but the grid has rows for each date and columns for each entry type. You're also entering data into over 90 different sheets. The latter fact rings all my alarm bells. It is always difficult to report across sheets. It is always better to enter data into only ONE sheet only and then use Excel's tools to report on that data.
I have added a data entry sheet with just four columns: Name, Date, Category, and Points. Category is a data validation list from the helper table next to the data entry table. The points are looked up with a Vlookup formula from the helper table. The data entry table is an Excel Table with tools in the columnn headers to sort and filter as you require. All formulas, formatting, and pick lists will automatically be applied to new rows of data.
I have entered the data from the three sample sheets.
With this data entry table, there are no empty cells. Data is only entered for people and dates and categories that actually happened.
Then there is the Report sheet. It has a pivot table that looks at the data in the data entry table. All names from the data entry table and all categories from the data entry table are listed in the pivot table report and the points are totaled accordingly. If new data is added to the data entry table, just click the Data ribbon > Refresh All, and the pivot table will show the updated results.
There is also a People Sheet report with a pivot table filtered to a particular person. It details each event that the person got points for. You can click and change the filter to a different person and the same sheet will show the data for a different person.
You have collected the same data, produced the same report, with a lot less effort and a much smaller footprint (three sheets).
I hope this has given you a little taste of what Excel is capable of.
Let me know how you get on.