09-05-2019 07:36 AM
09-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
When I created each individual sheet I copied my original formulas to new sheets and renamed the sheets. I
Thank you so much.
09-06-2019 06:07 AM
09-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.
09-10-2019 02:41 PMSolution
Hello 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:
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.
09-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.