+ Reply to Thread
Results 1 to 24 of 24

How do I add data to a sheet without messing up the formatting for the whole workbook?

  1. #1
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Angry How do I add data to a sheet without messing up the formatting for the whole workbook?

    I have to maintain a record of community service hours owed and worked by my clients. Sometimes I have new clients and have to add them to the sheet, sometimes clients finish or move away and I don't need them on the next months sheet. As a third problem, sometimes clients will have hours added to what they owe. How do I do these things at the start of a new month? How do I do it in the middle of a month?
    Is there a way to lock data to a clients name across several sheets? Ie. Becky Johnson becomes Becky Smith, if I move her does it mess up the whole workbook?
    "Problem" sheet would be October below the names are some examples of actions I need to take.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Hi
    What is the A A B B in line 1?
    what is the numbers in line 2? is it dates?
    How will you add the 48 hours for Charles Bronson, for example? why do you think it would mess up the file?

    Thanks.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Hello and welcome to the forum.

    A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

    You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
    You will also throw open the whole wonderful world of the powerful Pivot table functionality.

    So before you get too far with this I'd create a single sheet database that contains the following columns
    Date
    Person
    Client Hours

    The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

    Reporting then becomes a trivial matter of summarising the Hours for the period in question, either with standard Excel functions or better still a Pivot Table.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    + may I suggest a formula in C13 :J13 -
    =COUNT(C3:C12)
    You can drag it instead of creating a customized formula for each column....

  5. #5
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    A and B are examples of the Jobs we are working on, the numbers below are dates.
    in my real world example I added a column that is specific for adding hours.
    if I add a row, it messes up the rest of the rows. Let me see if I can make an example and answer your and Richard Buttrey's Points. Ill upload it under his comment.

  6. #6
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Thumbs up Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Quote Originally Posted by belinda200 View Post
    + may I suggest a formula in C13 :J13 -
    =COUNT(C3:C12)
    You can drag it instead of creating a customized formula for each column....
    This is the genius I was looking for!

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Are you serious? This is what you were looking for?
    why didnt you say that in the first place?

  8. #8
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    I am not sure how to incorporate the list of names now. Also I'm 15+ years out of any formal training in Excel and need to take a class, I don't so much run reports at this point but could see how that would be helpful in the future.

    I think my real world problem came when I realized in October that Dolly Pardon had worked 16 hours in August that hadn't previously been recorded. Now when I go into August to add those, it messes up Susan's totals for September and October as the references are now not correct.
    Attached Files Attached Files
    Last edited by CSW Supervisor; 08-29-2020 at 02:20 PM.

  9. #9
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    I meant this kind of intellectual nugget. You're solving a problem I didn't realize I had.
    Last edited by CSW Supervisor; 08-29-2020 at 02:20 PM.

  10. #10
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Richard, I think I understand what you are saying, I'm just not sure I am smart enough to incorporate it. When I took this position in January, everything was hand written so I am starting from scratch. I need to have the following information for my "reports" at the end of the month I bill the jobs based on how many people were there over how many days. I need to know who was on site any given day incase something comes up ie something found missing after we worked there two days ago, someone tests positive for COVID and now we need to know who they worked with etc. I also need to know how many hours each person has remaining and how many they have completed so I can schedule them out. I can't schedule Bob to work the next 5 days if he only owes 24 hours for example. I have designed this workbook with those goals in mind but if there is a better format, I would be willing to start over.
    Last edited by CSW Supervisor; 08-29-2020 at 02:20 PM.

  11. #11
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    You're welcome (but please don't quote former messages, it just doubles the display and floats [or floods?] the forum).

  12. #12
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Thanks, I recently got off Reddit and am unfamiliar with format and etiquette here.

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    So -please look at the attached.
    1st of all you need to keep a systematic orders of all workers throughout the months.
    I numbered them in column A, you must keep this order (dont delete employees who left), and add new ones in the next available number, after the last none-empty row (not in the middle).

    So - in your example Dolly Pardon is employee # 9 and she will be such in August as well.

    Column M contains below formula taken from August tab, when you open Oct. you will have to change it to Sep.:
    =IFNA(INDEX(August!N:N,MATCH(B3&C3,August!$B$1:$B$13&August!$C$1:$C$13,0)),"")


    Column N will contain the below formula:
    =IF(M3="",SUM(D3:K3)+O3,IFERROR(M3-SUM(D3:K3)+O3,""))
    Attached Files Attached Files
    Last edited by belinda200; 08-29-2020 at 02:42 PM.

  14. #14
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Ok, wow! I need to get into a class ASAP. I see that it works, I need to get a book and learn WHY so that I don't have to come back and figure out how/ when I screw it up.
    So I understand, I need to start over, make a master list of all of the clients each of which will have an assigned number. copy your formulae into the propper cells, and then duplicate as necessary for my first "September" sheet. (Book now has "Master List" and "September") at the end of September I can add a sheet "October" any new persons who work will be added to the master sheet, then I can add them to the "October" sheet at the bottom, but will be able to sort the list alphabetically as long as I don't change the master sheet, correct?

  15. #15
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Yup! I'm lost. I am going to monkey with it for a while trying to figure out and implement this magic formulae and come back later with whatever new questions or problems I find. Thank You guys for being so incredibly helpful!

  16. #16
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    you described it well, your English is better than mine
    In fact you dont need a master sheet at all, Just take August as your starting point, each month you will add a tab (and add your new clients in new lines), carry forward the balance in former month to "M" (new clients will have 0 balance), and calculate the end balance in "N".

    Though the formula knows to get the opening balance by name no matter if you change its position within the table - I would suggest you to maintain the order, and give each clients its unique number, this way it would be easier for you track their history.

    Good Luck.

  17. #17
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    If I wasn't embarrassed and impressed enough, you just disclosed the fact that you were smarter than me in at least 2 languages! If I create a "master list" I can sort the clients by alphabetical order each month to prevent any lost or duplicated persons. I could also use that master list "August" in this case and create columns that will track each months ending totals on the same sheet correct? i would just a column for each month and import the ending total from that month into the column. I could then have column A and B be their name, Column C be their total hours owed, D January, E February etc. correct? and by using your "MATCH" formulae it will find "Dolly Pardon" wherever she is on each sheet?

  18. #18
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Hey, this sounds like a good direction!
    you can post the draft file here when prepared in order to review, and help where needed.


    Quote Originally Posted by CSW Supervisor View Post
    and by using your "MATCH" formulae it will find "Dolly Pardon" wherever she is on each sheet?
    Regarding this one - you will have to reference the sheet name of each month to direct your search to the relevant tab. I would suggest the INDIRECT function to accomplish that, but if you find it too complicated - you can post your draft and ask for assistance with that.

    Good Luck.

  19. #19
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Red face Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Not Sure I got anywhere. Not sure how to read your formula to replicate it, and Not sure how to copy it from the other book. UGH! I do recognize that the first page ie "september" is going to be different formulas than "October." I will have to spend more time on this tomorrow as well as in the formulas tab with the name manager portion?
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Found out how to make A6 be Smith, Bob. Do I have to go into the name manager for every one of the 200+ Clients? or is it like a formula I can copy and paste that automatically applies whenever I add a new client.

  21. #21
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,205

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Hi
    See attached,
    The master file is basically merely formulas (except the names that are typed manually)

    In D3:
    =IF($A3="","",INDEX(INDIRECT("'"&D$1&"'!"&"V1:V200"),MATCH(Master!$A3&Master!$B3,INDIRECT("'"&D$1&"'!"&"A1:A200")&INDIRECT("'"&D$1&"'!"&"B1:B200"),0)))
    (You can copy it to all oranges)

    In E3:
    =INDEX(INDIRECT("'"&D$1&"'!"&"U1:U200"),MATCH(Master!$A3&Master!$B3,INDIRECT("'"&D$1&"'!"&"A1:A200")&INDIRECT("'"&D$1&"'!"&"B1:B200"),0))
    (You can copy it to all blue)

    Sep Beg. balances are typed manually, and then in Oct. you bring forward the opening balance from Sept. with this formula (you need the name of the tab to be similar to the months you enter in the "master" file)
    =INDEX(INDIRECT("'"&$V$1&"'!"&"W:W"),MATCH(A4&B4,INDIRECT("'"&$V$1&"'!"&"A:A")&INDIRECT("'"&$V$1&"'!"&"B:B"),0))

    In Oct sheet , cell V1 - you type the value of the former month for which you calculate the opening balance, and the formula should capture it.
    In Nov. you will type Oct., and the formula will bring you the opening balances from Oct. tab

    Regarding the names - i made them a formula in tabs Sep/Oct - and dragged the formula down to leave space for new clients when added in the "Master" file, so you dont have to add them manually anywhere except in the Master file.

    Hope this help.
    Attached Files Attached Files

  22. #22
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Does the attached help at all.

    I've attached a Pivot Table to the new Data sheet which would replace your individual monthly tabs.
    You can easily filter the PT by Date/Person to see totals per date or month.

    If you let me know what you now want to do with that data in terms of reporting gaps or unused time then no doubt we can work something out.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    08-29-2020
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013
    Posts
    13

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Richard, this makes a whole lot of sense to me, it is literally just data entry day to day, can add whatever days I missed back at any time and then extrapolate that data to fit whatever I need? I am giddy with excitement. my columns would just become, Name, date, job site comments and hours due. I could even add multiple columns if people owed hours for multiple cases. I could then find out how many hours john doe owes by pulling up his total due minus however many he had completed.

  24. #24
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,145

    Re: How do I add data to a sheet without messing up the formatting for the whole workbook?

    Hi,

    Glad it's prompted some thoughts.
    Just to develop this a bit, the way I usually use a database like this is have a data entry row above the database table. When that's filled in I then click a button which runs a single line of macro code that adds the data to the bottom, (or top) of the table. It's a database so the order of dates doesn't actually matter too much although obviously you can always sort the data as necessary.

    You can of course start adding additional columns for different fields of information. However make sure the fields are unique. Don't for instance start adding columns for say different job/case types or you'll start to lose the benefits of a PT. If you have say different case types then have a column field for 'Case Type' in which you would record stuff like the Hours for Case 1, Case 2, Case...etc, on different rows i.e. the Column Field is the unique thing and kit contains 'Case 1, Case 2, etc.. i.e. the values within the column are of course multiple and different.

    With PTs you can also calculate and show totals for stuff that doesn't exist in the data - but we may be getting ahead a bit at the moment

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-09-2020, 11:32 AM
  2. [SOLVED] Report Formatting Messing up Vlookup?
    By lipper79 in forum Excel General
    Replies: 4
    Last Post: 01-17-2017, 03:42 PM
  3. Replies: 2
    Last Post: 11-21-2016, 07:53 AM
  4. [SOLVED] Formatting TextBox While Not Messing Up Calculations?
    By t0mps in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-17-2014, 05:26 PM
  5. [SOLVED] Conditional Formatting Messing Up When Delete Cells
    By jamiek47 in forum Excel General
    Replies: 3
    Last Post: 11-12-2013, 08:33 AM
  6. Conditional Formatting Messing up in Macros
    By rayuken in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2013, 10:10 AM
  7. sort messing up second workbook
    By benjii19 in forum Excel General
    Replies: 5
    Last Post: 03-17-2011, 06:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1