SOLVED

New here. Need formula help

Copper Contributor

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.  

9 Replies

@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?

Thank you. I apologize for my awkward description. A short recap: I have created 90 individual sheets for people in a group. The first column is a year's worth of dates for the meetings. The next six or so columns denote various opportunities for the individuals to earns points each week. Those points are entered weekly and I have each column adding up by column in a row across the bottom. That all seems to work well. I will add I copied the first page I created and pasted into each new sheet (before any points were awarded). Next I wanted to take the bottom row that tallies all of the columns and copy and paste to a new sheet showing by individual their tally from the bottom row. I used the paste with the link when I copied to the total sheet. About halfway through my creation of the total sheet I saw when I added some individuals their row was affecting the totals on some of the already rows I already added. Thank you for your consideration. Marsha

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

@Ingeborg Forgive me, but I don't see where I add the file.

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

 

2019-09-10_09-33-41.png

@Ingeborg Hawighorst 

 

Thank you.  I appreciate your sticking with me through my confusion. 

 

 

best response confirmed by MarshaP (Copper Contributor)
Solution

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.

 

 

 

 

@Ingeborg Hawighorst 

 

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

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

 

 

1 best response

Accepted Solutions
best response confirmed by MarshaP (Copper Contributor)
Solution

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.

 

 

 

 

View solution in original post