Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-31-2015, 03:37 PM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default Complex calculation from multiple sheets to master sheet

Here is what I'm trying to accomplish.



I have an Excel document with multiple sheets (tabs).

On each sheet there are 2 columns I am concerned with.

One column is a list of INCIDENT TYPES the other column is a list of CITIES

The INCIDENT TYPES and CITIES may vary from sheet to sheet but there is definitely overlap.

The first thing I need to do I have accomplished. I created a column that finds unique values in the INCIDENT TYPES and then puts only the unique incident types in this new column. I then made another new column and I have that totaling each occurrence for each INCIDENT TYPE.

I now am going to create a Master sheet for each city. I know all the possible cities so each city will have its own sheet.

I now need to find a way to go through each original sheet, find each unique incident type for each unique city and then place that on the city's corresponding master total sheet.

So, for example, the city of Buffalo will likely be on most sheets. On each sheet there will be different incident types associated with Buffalo. I need to list all the unique incident types associated with Buffalo, for each sheet, and place them on Buffalo's master sheet. Then, I need to go total how many of each incident type there are on each sheet and put the grand total for all sheets on the master sheet for Buffalo.

I know this might be a little confusing so I will likely attach screen shots sometime next week. I wanted to get this out now though in case someone can help push me in the right direction.

Thank you for your time.
Reply With Quote
  #2  
Old 12-31-2015, 03:55 PM
NoSparks NoSparks is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

The last line of your post is very, very important.
Nobody wants to waste their time re-creating, from a picture, something you've already got.
So I think the best advice at this time would be to attach an Excel file rather than screen shots.
Reply With Quote
  #3  
Old 01-04-2016, 07:45 AM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default Attached is a sample spreadsheet.

The attached spreadsheet shows 4 tabs. The master tab where I need to calculate the totals for the other tabs. Relatively straight forward except for the fact that the amount of incidents is dynamic and if additional entries are made in the monthly tabs I need the master tab to update accordingly.

Thanks again for anyone that is willing to assist.
Attached Files
File Type: xlsx Sample.xlsx (15.4 KB, 13 views)
Reply With Quote
  #4  
Old 01-04-2016, 12:48 PM
gebobs gebobs is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Go Buffalo! LOL Is that Buffalo NY?

IMHO, I think you should have one master sheet of data covering all cities and dates. Splitting the data among sheets by month is probably going to create more issues than not and likely unnecessary.

Then I think you can just use a pivot table to extract your data. See attached if this works for you. You can filter by jurisdiction on top or run it for all jurisdictions. I changed the months from your tabs to Jan, Feb, and Mar merely for illustrative purposes. If you want you can enter specific dates (e.g. m/d/yy) on the Data tab too and set up the Pivot to group by months, quarters, years, etc.

Anyhoo...that's my suggestion. Let me know if this looks like it might work.
Attached Files
File Type: xlsx Sample v2.xlsx (17.5 KB, 10 views)
Reply With Quote
  #5  
Old 01-04-2016, 02:18 PM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default Gebobs..........

Thanks for the info.

I will look at this and see if I can make use of it.

I need to figure out Pivot Tables....... I guess that might be a good first step.

I'm trying to cleanup something for someone and make it actually useful.....the person I'm helping currently tracks a ton of info in a spreadsheet and then manually counts rows and columns to get totals.

I thought the purpose of a spreadsheet was to get totals!

Of course, the calculations he wants are not simple ones and the spreadsheet is a mess so I'm trying to figure out the best way to tackle it.

Thanks again for your time.........
Reply With Quote
  #6  
Old 01-04-2016, 02:24 PM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default Gebobs..

Another thing of note, the way I sent the original spreadsheet is how it is already setup.....with multiple tabs. Each tab is used to track one individual investigator and then how many cases they work. So each investigator will have different types of incidents for different jurisdictions but they will overlap and thus the need to get totals across multiple tabs.

So, I will look to see if I can set it up with one master sheet that will pull all Incident Types across all tabs for specific cities (jurisdictions).

I'm guessing this will be a little more involved than getting totals from one tab like you have setup.

The original spreadsheet has many other columns that I did not show because those are columns that don't need to be sorted or counted.

Someone at work also mentioned Pivot Tables but they don't know enough about them to help......they just know enough to throw the term out at me.
Reply With Quote
  #7  
Old 01-04-2016, 03:22 PM
gebobs gebobs is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Quote:
Originally Posted by inreality01 View Post
Another thing of note, the way I sent the original spreadsheet is how it is already setup.....with multiple tabs.
Based on what you've said about how they use Excel, that's probably a sure indication that there's a better way. IMHO, pivots are your best bet and incredibly simple to set up and use. If you want to email me your original file, I can look at it and see if we can work something up. I promise full confidentiality.
Reply With Quote
  #8  
Old 01-05-2016, 07:34 AM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default Gebobs.........

I have attached a working copy with all sensitive information removed.

I had to delete several tabs/worksheets in order to get this file to attach, so ultimately the calculations will need to encompass several more tabs.

Everything from Column "N" and on are changes I made.

Column "N" is where I'm pulling out unique incident types.
Column "O" are totals for each incident type.
Column "P" is where I'm pulling unique cities out (the color of the font was changed so the user doesn't see these items as I thought it would be easier to look at the following column totals)
Columns "Q" and on are totals for each incident type for each city.

Now, I'm just pondering how I get the totals on the master sheet(s) for each city for all tabs, for each incident type.

Everything I have done so far was done so that the spreadsheet can be dynamic and everything updates..... as new rows are added for a new incident.

The individual using this when I'm done has basic skills so I need everything to be simple.

So, if you have time to help me out that would be great. I'm going to be plugging away at this again today to see what I can come up with.

I started doing a pivot table but so far it's not working and Excel crashed a few times when I tried it for some reason. Uggggggg.
Attached Files
File Type: xlsx Working Sample.xlsx (64.3 KB, 11 views)
Reply With Quote
  #9  
Old 01-05-2016, 07:41 AM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default Gebobs......

One additional note......

If I can find a way to search through columns "Q" through "AQ", look at row 1 and determine which column relates to a particular city then I could make a formula to go through each tab/worksheet and add up the totals for each incident type. Well, I would also need to publish all incident types as well so I guess this could also be a bit tricky..........once again, I defer to an expert where my skills are limited on Excel.
Reply With Quote
  #10  
Old 01-05-2016, 09:48 AM
gebobs gebobs is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

I have to head out for an appointment. I will look into this later today.
Reply With Quote
  #11  
Old 01-05-2016, 10:19 AM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default

I can't complain.... I'm getting free help. At least, I haven't seen an invoice yet.

Thanks again, chat later.
Reply With Quote
  #12  
Old 01-05-2016, 12:49 PM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default

Can you also tell me how to do the following.......... which I know should be super easy I'm assuming.

I want to consolidate / join / "add" two text columns together so that a 3rd column contains all the data of the first two columns.

For the life of me I can't figure this out. I would think Excel would have a simple way to do this. I might try to do it with an Index function although it seems like there should be an easier way.

By the way, I'm looking for this to be dynamic so if either Column A or B change then Column C is updated without having to do anything.

This will be my last question.
Reply With Quote
  #13  
Old 01-05-2016, 01:19 PM
inreality01 inreality01 is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2013
Advanced Beginner
Complex calculation from multiple sheets to master sheet
 
Join Date: Dec 2015
Posts: 34
inreality01 is on a distinguished road
Default

I may have found an answer to combining 2 columns. Looks like I need to tweak the following because I have column headers so I need to get this command to start at row 2. It almost works as is.

=IF(ROW()<=COUNTA(A:A), INDEX(A:A,ROW()), IF(ROW()>COUNTA(A:B), "", INDEX(B:B,ROW()-COUNTA(A:A))))
Reply With Quote
  #14  
Old 01-05-2016, 02:50 PM
gebobs gebobs is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Quote:
Originally Posted by inreality01 View Post
Column "P" is where I'm pulling unique cities out (the color of the font was changed so the user doesn't see these items as I thought it would be easier to look at the following column totals)
What is "unique city"? The unique city for "assault" is St. Cloud. What does that mean?

Quote:
Now, I'm just pondering how I get the totals on the master sheet(s) for each city for all tabs, for each incident type.
I assume there is good reason to separate the tabs. Again, consider there are probably better reasons for consolidation that may be able to account for the reasons for separation. How about combining all the data into a master data sheet with the additional field for investigator.

Furthermore, I would forgo splitting the tables further with the "case review" partitions and add another date field for when a case review is complete.

Quote:
I started doing a pivot table but so far it's not working and Excel crashed a few times when I tried it for some reason. Uggggggg.
I'm not sure pivot tables can accommodate more than one table, let alone multiple sheets of tables.

To me, it looks like you are doing contortions to make the square peg that is this person's cobbled sheet fit into the round hole that is robust Excel design. IMHO, a tear down is begging to be made.

If there's any chance you think my suggestions might work, let me know and I'll gin up something from this for you. I hesitate to proceed if there is some important reason for your partition by investigator. It will take me an hour or so to come up with the first cut. If not, I'll gladly whip something up.
Reply With Quote
  #15  
Old 01-05-2016, 02:53 PM
gebobs gebobs is offline Complex calculation from multiple sheets to master sheet Windows 7 64bit Complex calculation from multiple sheets to master sheet Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

Quote:
Originally Posted by inreality01 View Post
I want to consolidate / join / "add" two text columns together so that a 3rd column contains all the data of the first two columns.
Which two columns?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Complex calculation from multiple sheets to master sheet Excel, transfer data from Master Sheet to sub sheets, using key word from column anvqui Excel Programming 9 06-16-2015 01:35 PM
Need help in automating data from multiple sheets into a single sheet veedee Excel Programming 8 06-12-2014 10:04 PM
Complex calculation from multiple sheets to master sheet Need help in automating data from multiple sheets into a single sheet veedee Excel 1 06-11-2014 04:01 AM
Combine Data From 2 Sheets into a new sheet bremen22 Excel 1 09-11-2013 12:59 PM
Complex calculation from multiple sheets to master sheet How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:32 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft