Copy Rows in one tab to other tabs within same file and allow for changes.

Ok excel professionals I have a new challenge for you. I am going to attach a file that is a copy of the master I use. I have had you all help me with other formulas within it before and wonderful success.

The way I use this file is in tab one (named MASTER) I input the info you will see in the sample. This page is fluid on an everyday basis from new info, changed data within the row, rows moved up and down within that page, or even deleted rows with data. Example: John Doe 66 came in today to be seen. all of his info would be put into the next open row in master tab and the date in would be today. (date in hardly changes but sometimes has to) then about 8 more John Does all come in after the first one. again all done the same way and in order to start. as the process works out John Doe 69 might be completed and his date out is before John Doe 66 so I will copy and paste his row (within the master) above John Doe 66 and mark him complete. and do that each time one is complete until they all are. (make sense?)

In the past once the end of each month was up I would copy and paste all the data in the master that fit within the "Date In" column and paste that data in the corresponding tab at the bottom. so if the date in was between 01/01/17 and 01/31/17 then everything would be pasted in the JAN tab. this worked however it moved the already set up formulas within each monthly tab which then changed all of the totals and everything else the rest of the way.
I would have to move it back up and change the numbers within the formula etc.. it just didn't work like I'm sure it can. **side note I want all the formulas that are set within each tab to never change if that's possible

What I would like is once I type in data in a row in the master tab and type in a date in the "Date In" row that data within that row is also copied into the tab named for that month. so 1/05/18 would be copied into JAN tab in the next open row. then as I change the data within that row again in the mater it also changes it in the JAN tab to match the same thing. (the kicker here is as they move to complete I typical move them to the top of the master sheet so that they are all in a row by the date out and I change the font color from black to red.) You will be able to see this in the sample I am attaching. So like I said the data in the master sheet will change everyday. and i would like for the data within each tab to change with it automatically and go off of the "date in" as to where that data is.

Ok I will stop there and if you have questions which I'm sure you will because I know I didn't explain this very well. thanks again everyone you have always been such a huge help to me.

So no one is helping... Am i asking for something that can NOT be done? if I am then please someone just tell me and I will delete the question. I am starting to feel like what I am asking is impossible or I didn't explain it at all!

You can do that with PowerQuery (free add-in for Ex2013 Pro Plus from MS site)

I did it for Jan, Feb and March
but if you want to see how it works you need download and install PowerQuery add-in

i am also facing a problem.Now it is solve with this post.
Thanks

Originally Posted by JeteMc

If I understand correctly, the following array entered formula* fills in the information on the monthly sheets (Jan and Feb are shown):

Formula:

Please Login or Register  to view this content.

Zero values are hidden using conditional formatting > number > custom > ;;;
I made a few other changes to the sheets:
1) T.O.T. column formula: =IF(E2=0,"N/A",NETWORKDAYS(D2,E2))
2) Duplicate ID# column: =IF(B2=0,0,COUNTIF(B:B,B2))
3) Duplicate Names column: =IF(C2=0,0,COUNTIF(C:C,C2))
4) Font formatting on the Jan sheet is controlled by conditional formatting based on whether or not the word "Complete" is in column F.
5) Fill formatting on the Jan sheet is controlled by conditional formatting based on the value in column A.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Ok I just got back to work today so let me see how this works. I'm not sure I understand how the CTRL+SHIFT+ENTER works. mostly when I need to use that. but let me save it to here and play with it and see if i can figure it out. I will not be starting with january until the 3rd. so hopefully I can figure it out by then. If I have questions I will come back here. thank you!

Originally Posted by sandy666

You can do that with PowerQuery (free add-in for Ex2013 Pro Plus from MS site)

I did it for Jan, Feb and March
but if you want to see how it works you need download and install PowerQuery add-in

Ok I will look into that download as well. if thats the route i go then I will need help using it once I get it. But it looks like the reply above has it done without an add on. let me play with that first because i will have to get owner approval to download an addon. thank you!

No problem

have a nice day

Originally Posted by JeteMc

If I understand correctly, the following array entered formula* fills in the information on the monthly sheets (Jan and Feb are shown):

Formula:

Please Login or Register  to view this content.

Zero values are hidden using conditional formatting > number > custom > ;;;
I made a few other changes to the sheets:
1) T.O.T. column formula: =IF(E2=0,"N/A",NETWORKDAYS(D2,E2))
2) Duplicate ID# column: =IF(B2=0,0,COUNTIF(B:B,B2))
3) Duplicate Names column: =IF(C2=0,0,COUNTIF(C:C,C2))
4) Font formatting on the Jan sheet is controlled by conditional formatting based on whether or not the word "Complete" is in column F.
5) Fill formatting on the Jan sheet is controlled by conditional formatting based on the value in column A.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

I tried playing with it by adding new rows in the master but it removed those in jan tab. and didn;t add one in March. So I am clearly not using the C+S+E right. please explain. I am attaching what i did.

Ok after going to download It seems i have it already. at least that what the site told me. its in the data tab and when i click that I do see a link for Queries and connections. So now what?

Originally Posted by Robbyvictoryop

its in the data tab and when i click that I do see a link for Queries and connections.

so it means you've Ex2016 or 365 ?

Sorry got busy... we have EX2016 yes.

so 1st change excel version in your profile
2nd: add new row in Master table with any date from January then go to Jan tab, right click on the blue table and select refresh

is that what you want to achieve?

Ok I tried it again with all new data. its close to the current data that we have now. I am attaching the sample
below with the new file.

I did as you said (or as I understood it to say) I input the new data, made changes as i would any given day,
went to Jan tap. selected all of the blue section as if I were to copy it. and then clicked refresh.

What you will see in the sample is what i got. It didn't move over all of the lines of data. It moved some but not all that i was wanting

1.) I would like for it to move all of the data from master tab that reflects the month in the DATE OUT column. and is marked as COMPLETE.
so everything that had 1/??/18 should have been moved over to the JAN tab. only 7 were moved and none of the completes.

2.) How do i set this up for each month? basically can you explain how this is set up or can you do it for me? I would like to now how but if its
complicated i understand.

3.) did I do it right? do i just go into the month tab I want to refresh and select rows and hit refresh?

I added new "data" to row 37 on Master tab.
then refresh table on January tab (btw. you don't need select whole table, enough click on single cell inside the table then refresh)
and I saw new data on the end of January table

I added one row (38) for February then refresh Feb table

maybe the problem lies in the dates: US style / UK style
try to change dates to UK style: dd/mm/yyyy temporary and check again how it works
(select columns: D & E and change style
datestyle.jpg

after all you can back to your US date style

Ok, here is file with US style. Hope it will work

I tried it but no luck. attaching file after my data input and clicking refresh.

after new input there should be 17 rows moved from master tab to Jan tab. but only 9 were
moved and most are wrong dates. 2 for january dates (01) the rest were february and march
dates. (02 and 03)

in the FEB tap where there should have been 11 rows with (02) dates it moved 8 rows and only one marked
(02) the other 7 do not even have a date yet.

In March tab nothing got moved.


is this user error or am i not explaining what i want well enough? SO SORRY!!!!

Yes lets...

1. My goal here is to track every job that is completed each month. I use the Master tab to be a running sheet that changes every day, multiple times a day. I keep some "semi completed" rows ready at the bottom of the master sheet to speed up data entry. The color rows indicate a certain type for quick visual only on the master tab. and the red font is to show all completed also in the master tab. Now within this master tab data can change, move, copied, pasted...etc. at any given time HOWEVER once I move it to complete I do not remove it. I may copy and paste it somewhere within the master tab to reuse the data but once it is moved to complete that row will stay in that spot. (unless I had the date wrong) then its moved to the right date row. there are NO formulas in the master tab. not in my original anyways and this tab will end with around 2000-2200 rows used by the end of the year. each month averages around 200 completed

2. Once a job is complete I will open this file, open the mater tab, find the row that matches a completed job, I will select that row and CUT. I then insert that row by selecting the last or bottom of the rows with red font in the list, I will click insert cut row here. so now that complete row is below the last completed job. I then change the "date out" date in column E to that days date, and change the whole rows font to bold red. This keeps me a running list of completed jobs by "date out" going in the master tab with the last job that was completed showing as red font in the full list.

3. At the end of each month I then (in the past) would select all rows, in red, that have a "Date Out" that fits that month. let's say that January had 300 jobs that were moved to red and fit between 01/1/17 and 01/31/17. I would select those and copy. open tab JAN and insert those copied rows into that tab. I do this for each month as we go through the year. Because it was a copy and paste all of the colors would also move but that is not important. I use the monthly tabs to also break down each month and gaither additional info about each job for each month. those formulas are found in each month and the totals at the end of the list.

4. the problem is the way this whole file was set up from the start (me and my wife working from nothing just trial and error) each month tab (jan, feb, march, apr, etc...) has formulas that are in columns N, O, P, and Q. when I paste 300 rows of data into a given monthly tab those formulas move down 300 rows and the formulas change by those numbers. I have to go in and move them back up (don;t have to really but it makes it easier) and change the numbers in the formulas so it will find the data i want it to find.

5. My hope was to find a way to move those 300 rows from the master to the correct monthly tab without it moving or changing the formulas in N,O, P, and Q. The simplest way would be to finish a month. (have everything in its correct order within the master tab) and then open the Jan tab and click refresh and it pull all 300 rows of completed that fit within the january "date out". then do the same for February, at the end of february I open that tab and click refresh and it copies all of those into it. march, april, etc...

Did you check attached file from post #18 ?

if you want to move your data ( cut&paste) you will need vba
but if you want keep all your data on Master tab and show data from Jan on Jan tab, Feb on Feb tab etc,,, you just got it in this file (filtered by Date-out)

First I did not see that file in post 18. for some reason i overlooked it.

I do NOT want to move the data from the master. just copy it. sorry for the bad wording in explain that.

I checked that file and it seems to work. I will remove the test data and add in our current data and see if it still works. I know if I do it now that everything that shows a date out in january will be copied rather they are complete or not. which is fine for this test. But we will see at the end of the month how that works when I start into February dates.

It looks like you set up JAN, FEB, and MAR, tabs correct? what or how to I set up the rest to match what you did. I would like to understand what you did. That way if I can come up with another way to help me do something else in the future.

and THANK YOU for all your help.

OK I will give this a try and see how they work. I think I understand what you said about the headers can not change in the master tab. the bight yellow row at the top that has the label for each row can not be change at all for the future correct?

I also think I understand on the case and space sensitive of all data. However I am not sure I understand what or why it could cause some issue as I don't alway type the names right ever time. Is it not just looking at the "date out" column, but all data from columns A to columns L ? and any difference in any of this data will effect the formula? let's say I have a Jon Doe. also a John Doe. and a Jon, Doe. will it miss one or all because they are typed and spelled different? what about something in the other columns that might not match up?

and last Should I mark this as solved now?

Originally Posted by sandy666

Table has columns. Columns has Headers. If you call them Labels - ok
First row of table cannot be changed after you define any queries with PowerQuery. (honestly this is not true but if you want to change Header you should know how to do it and where you can do it - but with this level of PQ knowledge they cannot be changed )

And yes - these are the Headers
Attachment 555127

In the same column:

Pedorthic work Order
Pedorthic Work Order

these are two different things, so better way is use DataValidation List where you can choose items without mistakes.

If there will be mistake, in your case, it doesn't matter because you not transforming data but only copying.
But it is important for the future if, for example, you want to count something so J Doe and j Doe will be counted as different values.

I can prepare example based on this example (with my point of view) how it should looks in my opinion. Of course my opinion can be different than yours

edit:
one more question
colors of rows in table are dependent becasue of : items, status or what?

Ok got it Headers (labels as i called them) are the ones you sent an attachment here. And in looking at the file from the past
I can see that they have a arrow or dropdown for the set up you did. So got it these can't be changed and shouldn't be changed moving FWD. I might add new ones
in the future if I come up with another way to track something (tracking redo's has been discussed) but not sure where or how to do that yet.

Color or rows.. Background color difference tells us at a quick glance the item type orange = pedorthic, Purple = Orthotic, Green = Prosthetic.
Font color tells us what stage that job is in. Black = currently active (work in some stage of progress) Red = Complete

Something I do need to inform you as we are going along. Some new things are being added for 2018 this includes new practitioner names and some new styles or types of jobs that right now only if affecting the
totals of my current set up. I was about to add another question on how to do these in a mass style change and not affect the whole file.
but for this the master will not change. Each month will change over in column N as I will be asking it to look for newer things.

unfortunately this is an evolving process as we learn what all we can do with it. I do want to say that you have been a huge help and I am using what you set up for me as a sample test file right now.

I do need to ask what or how the spelling will affect this to be clear. you said. ""But it is important for the future if, for example, you want to count something so J Doe and j Doe will be counted as different values.""

Please keep in mind that the j Doe were just so I am not using real patient names here. My working file has the name types out in this format. Doe, Jane As you know some people have matching names. but none of them will have a matching patient ID#. so if I do have a two Doe, Jane but that are different people with different PT ID#'s and even are in a different location with different job type. how do we keep them seperate?

And last.... am i asking to much for this file to do for me? It has worked in its current state in the past to give me numbers. I was just looking for a quicker and easier way to do it.

Ok. we can add the patient ID# as well. it's already in the header and used with every one anyways. I am now attaching my updated file that I have been working with. I did change all the names and to do that quick and simple i just made them all different by count. so the ID number and name should be different for each one on the master tab now. Below is what you will see that's not working in the newly updated file.

1. In JAN and FEB tab the dates did not work all the way down.
2. In JAN and FEB tab, Column K and L did not go all the way down and it did not do a correct count
3. In JAN and FEB tab Column 0 is not getting the correct counts for the data that is present. all the totals should be even but they are not. In Jan it should all equal 139 and in FEB it should equal 58
4. I will need to all changed for each remaining month at some point.
5. I'm not sure I mentioned this, but I do not need the color backgrounds and fonts in each month tab. just in the master for quick reference. each month tab is for getting the data for that certain month.
6. I have updated the changes in column N for JAN, FEB, and MAR. these new additions are all marked with yellow highlight.
7. I have also updated the totals tab with the new additions (marked in yellow)
8. Practitioners tab is not counting anything from JAN, FEB, MAR, tab at all. I also updated this tab (marked in yellow) the new additions. but only for JAN only and none of the formulas.
9. Technician tab is also updated (marked in yellow) but is not working right either.

10. #8 and #9 Above leads me to my next issues. how do I add these new changes in? if you look at the way i have the practitioner and technician tab done they get their info by each months tab, column and row. (sorry this is just how I figured out how to do this) Like this year we are always adding new things, adding or removing practitioners or tech's and causing these two to be a pain to update. I would like to add in new info but not have to retype each formula for each cell in each row for each month. Is there a way to do select changes or edits?

Ok I am going to see if I can answer your questions as well as add in my new ones. It's clear I am not explaining this well and I think I am making it harder on you because of this. The updated file I am attaching will be different. this is the file style I want to use for this year however each monthly tab and all the totals are not fully updated with correct formulas yet. (something I need help with) Jan, Feb, and March tabs are good I think except for some bad calculations that I will go into below.


1.)The file an am attaching to this reply is as updated as we can get and has the issues I will try and convey. The names have changed for obvious reasons and the ID numbers may or may not match and i will explain in #2.

2.) Like any medical company every person in our system has their own unique ID# the number is what separates a john doe and a john doe jr. So if I am understanding this right that should be the key to account for. Date in and date out matters for my end and time is not a factor. we are not seeing the actual person here. we are only seeing something that belongs to that specific person and want to track when it arrives and leaves. in the file I have attached the ID numbers will be the same over and over again as sometimes we do multiple jobs for the same person until it is finished. so because I changed the names (HIPPA) and not the ID#'s that part of the file attached is not fully matching what i am using.

3.) Column K and L are not that important other then to possible see how many times we saw a certain in a given time period. it is not used in my totals. I was asked to have the info upon request so I made those to help me keep an accurate track. To be honest for what I am trying to get (totals for each month / year) the name and ID# is not important but it is used to have a quick view of what and who is currently in process if asked a status update.

4.) so with all that said i think if you use the ID# as the key along with the date OUT that will pull in all the names that fall into a certain month as far as which ones were completed (date out) during that month. even if they started (date in) the month before, the count I am looking for is the Date out.

5.) The type job is important as i keep track of each type for each month, I also use the dates to figure out how long it took each job, the practitioner and technician are also important as that tracks who sent and did what for each month. and at the end of each month / year it needs to equal out the same.

6.) Practitioners (red) tab. I looked again at the formula and yes it was wrong (because of additions I made which affected the numbers) but I went in and I changed the first one as you mentioned in your reply. I new I could change the numbers and then select the box in the lower right corner and pull it down and it would change all of them in that area ***HOWEVER*** is also changes the "type" to be that same type for each one. (example: =COUNTIFS(JAN!$A$2:$A$500,"Orthotic Work Order",JAN!$H$2:$H$500,"ZS") is now the same for every one in JAN but they need to be different to match what they are looking for in that month) **I change the second one to =COUNTIFS(JAN!$A$2:$A$500,"~Orthotic Work Order",JAN!$H$2:$H$500,"ZS") the third one down is supposed to be =COUNTIFS(JAN!$A$2:$A$500,"Pedorthic Work Order",JAN!$H$2:$H$500,"ZS") and the forth is supposed to be =COUNTIFS(JAN!$A$2:$A$500,"~Pedorthic Work Order",JAN!$H$2:$H$500,"ZS") and so on. then it needs to be done for each month!, Each name, each type, etc... what is the best (quickest) way to change all of them?

7.) I will remove the colors on the master tab in this test file and add it back to mine once we have a finish product.

The master now has 261 rows with info entered in. after clicking refresh January has 139, february 97, And March has 25. total of 261 so it has pulled everything into each month as I had requested.

However if you look in column N and O those are not matching up. seems as though they are not counting any of the Shoe Work Orders and or ~Shoe Work Orders as well as the Pedorthic and ~Pedorthic work orders as well. I'm sure that is something simple I am overlooking.

Ok, this is my point of view
You can add/remove any stats if you want
You can change/add any formula if you want

But this is all what I can do for you

Get it or forget it

thank you for all of your help

You are welcome

That is a good point to

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)

If you did it already - ignore it.
Thank you.

Sandy, I tried to send you a PM asking one last question. did you receive it? I'm not sure I am doing that right as it's not showing in my sent messages. Anyways I am asking if there is a way to convert my current file (one i have been using since 1/1/18 into this new file that you made for me? I do not wan to type in each one over again and not sure I can cut and paste. thank you!!

You want me to do your job??????

WOW.....! if that's the way you took my question for help/advice then I feel sorry for the next person who ask for your help. You did so well helping me all the way up and to this point and then it just went off the rails. Maybe I asked for to much. maybe my excel ignorance become so clear it pushed you over the cliff. either way I'm sorry for asking.