New here. Need formula help

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:

=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.