#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Maybe like the attached.
|
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
Did my answer help, or are you still confused?
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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.
|
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
SORRY... I mean sorry
|
#10
|
|||
|
|||
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. |
#11
|
|||
|
|||
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!! |
#12
|
|||
|
|||
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. |
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 |
How to share cell data between tabs of workbook? | littlekeith | Excel | 1 | 03-23-2015 06:21 AM |
Need help consolidating data from separate sheets | tiwas | Excel | 1 | 10-07-2014 04:57 AM |
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 |