Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-24-2016, 09:19 AM
ianm2431 ianm2431 is offline Breaking down data in separate tabs Windows 7 32bit Breaking down data in separate tabs Office 2010 32bit
Novice
Breaking down data in separate tabs
 
Join Date: Aug 2016
Posts: 6
ianm2431 is on a distinguished road
Default Breaking down data in separate tabs

My first tab is the master page with all the data. Some of this data is old, but i will be adding to it over the next few weeks.

I want to be able to break down the data by the criteria in column A. in column A there is 3 outcomes "FRI", "TESTING" OR "AUDIT". I want to be able to separate each outcome in separate tabs. The tabs relate to the 3 outcomes in column A.

You will see from the image there is other data in column B to H that needs to be copied with the info in column A.

I want to set up a document that automatically moves my data as per the criteria outlined in column A into the correct tab.



Hopefully you can help
Attached Images
File Type: jpg Untitled1.jpg (12.7 KB, 23 views)
File Type: jpg Untitled.jpg (50.4 KB, 23 views)
Reply With Quote
  #2  
Old 08-24-2016, 12:00 PM
xor xor is offline Breaking down data in separate tabs Windows 10 Breaking down data in separate tabs Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like the attached.
Attached Files
File Type: xlsx SeparateTabs.xlsx (168.5 KB, 11 views)
Reply With Quote
  #3  
Old 08-25-2016, 04:49 AM
ianm2431 ianm2431 is offline Breaking down data in separate tabs Windows 7 32bit Breaking down data in separate tabs Office 2010 32bit
Novice
Breaking down data in separate tabs
 
Join Date: Aug 2016
Posts: 6
ianm2431 is on a distinguished road
Default

Thank you.

When i bring this formula into my spreadsheet it will only bring the info for the criteria of the audit and nothing else. i have checked the formula in terms of ensuring it is the correct one in terms of the criteria it needs to pick up.

Little confused now!
Reply With Quote
  #4  
Old 08-25-2016, 05:28 AM
xor xor is offline Breaking down data in separate tabs Windows 10 Breaking down data in separate tabs Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

It is an array formula so when entering you must hold down Ctrl and Shift before pressing Enter (instead of just Enter). If you do it correct Excel will automatically put braces around the formula. Don't try to enter these manually. When you have entered one array formula correct you can copy it to other cells the normal way. If you edit an array formula you must again hold down Ctrl and Shift before pressing Enter.
Reply With Quote
  #5  
Old 08-30-2016, 08:04 AM
xor xor is offline Breaking down data in separate tabs Windows 10 Breaking down data in separate tabs Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Did my answer help, or are you still confused?
Reply With Quote
  #6  
Old 09-15-2016, 02:32 AM
ianm2431 ianm2431 is offline Breaking down data in separate tabs Windows 7 32bit Breaking down data in separate tabs Office 2010 32bit
Novice
Breaking down data in separate tabs
 
Join Date: Aug 2016
Posts: 6
ianm2431 is on a distinguished road
Default

It is almost working. When i enter the data into the "data" tab, it is not correctly sub dividing into the correct tabs. can you have a look to see if you can knock it out.
i am able to manual check the figures from the data page but when i get the summary page it doesnt match.
Reply With Quote
  #7  
Old 09-15-2016, 03:01 AM
ianm2431 ianm2431 is offline Breaking down data in separate tabs Windows 7 32bit Breaking down data in separate tabs Office 2010 32bit
Novice
Breaking down data in separate tabs
 
Join Date: Aug 2016
Posts: 6
ianm2431 is on a distinguished road
Default

I HAVE ALSO NOTICED THAT WHEN I CHANGE THE FILTER IN THE DATA TAB IT EFFECTS MY VALUES ON THE OTHER TABS. let me know if there is another way to complete this task or if a small change to my spreadsheet makes it work as i want it to.
Attached Files
File Type: xlsx Web Example.xlsx (116.8 KB, 9 views)
Reply With Quote
  #8  
Old 09-15-2016, 04:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Breaking down data in separate tabs Windows 7 64bit Breaking down data in separate tabs Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

No need to shout, we're all friends here
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 09-15-2016, 04:42 AM
ianm2431 ianm2431 is offline Breaking down data in separate tabs Windows 7 32bit Breaking down data in separate tabs Office 2010 32bit
Novice
Breaking down data in separate tabs
 
Join Date: Aug 2016
Posts: 6
ianm2431 is on a distinguished road
Smile

SORRY... I mean sorry
Reply With Quote
  #10  
Old 09-15-2016, 09:07 AM
xor xor is offline Breaking down data in separate tabs Windows 10 Breaking down data in separate tabs Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Can't you be somewhat more specific as to what goes wrong. It may be completely obvious to you but not to me.

Your naming of d and Activity is wrong. Those names span rows 3:211. It should be 2:210.
Reply With Quote
  #11  
Old 09-15-2016, 09:26 AM
ianm2431 ianm2431 is offline Breaking down data in separate tabs Windows 7 32bit Breaking down data in separate tabs Office 2010 32bit
Novice
Breaking down data in separate tabs
 
Join Date: Aug 2016
Posts: 6
ianm2431 is on a distinguished road
Default

Ok Firstly, when i add in a new row of information onto the "Data" tab i want to to update automatically in the next relevant tab as derived by the type of activity it is. This is currently not working for me now, but it did in the past.

The second issue is when i use a column to arrange the information on the "Data" tab, for example arranging it by amount or by date or by customer, it changes the information in the other tabs. I would have thought this shouldn't happen as the information is still derived by the type of activity.

When you say "Your naming of d and Activity is wrong. Those names span rows 3:211. It should be 2:210." i cant figure out how to change it back, can you see where i am going wrong.

Ideally when i sum the column D in the "Data" tab, it should equal the sum of "C11 +C21+C30" ONTHE "OVER VIEW" TAB. I cant figure out why this is happening!!
Reply With Quote
  #12  
Old 09-15-2016, 11:06 AM
xor xor is offline Breaking down data in separate tabs Windows 10 Breaking down data in separate tabs Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

You must adjust the names d and Activity in the Name Manager so that they have sufficient rows. I have changed Activity to refer to A2:A1000 and d to refer to A2:H1000.

I can't replicate your second issue.

C11+C21+C30 (in Over View) is now equal to Data!D2210. There was a fault in the formula in Audit INFO!D9.
Attached Files
File Type: xlsx Web Example_2.xlsx (110.6 KB, 10 views)
Reply With Quote
Reply

Tags
automatic document, data, help a novice

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populating Data in New TABs? Sueade Excel 2 06-02-2015 09:12 PM
Breaking down data in separate tabs How to share cell data between tabs of workbook? littlekeith Excel 1 03-23-2015 06:21 AM
Breaking down data in separate tabs Need help consolidating data from separate sheets tiwas Excel 1 10-07-2014 04:57 AM
Breaking down data in separate tabs Checking data in separate files ksigcajun Excel Programming 7 05-22-2014 08:54 AM
How to separate specific columns from the data kumar Word 1 06-04-2012 01:46 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:02 AM.


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