+ Reply to Thread
Results 1 to 51 of 51

Reducing Rows & Columns

  1. #1
    Registered User
    Join Date
    02-08-2007
    Posts
    69

    Reducing Rows & Columns

    I was given an excel log file that has several sheets, 1 for each month plus a few others. The monthly sheets each have 80 rows and the columns go up to BL. I don't need anywhere near that many cells. How do I reduce the number of rows and columns? Will this reduction result in a snmaller file size? Thanks.

  2. #2
    Forum Moderator - RIP 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
    29,464
    Quote Originally Posted by Attila
    I was given an excel log file that has several sheets, 1 for each month plus a few others. The monthly sheets each have 80 rows and the columns go up to BL. I don't need anywhere near that many cells. How do I reduce the number of rows and columns? Will this reduction result in a snmaller file size? Thanks.

    Select the rows and columns and then Edit-->Delete from the menu.

    Deleting rows and columns will reduce the file size, although the reduction in size will not be in direct proportion to the numbers of rows/columns deleted.

    Rgds

  3. #3
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Thanks that worked perfectly. I can't believe I missed that, so simple? I have been updating the log file, corrected a date error, replaced the terminal options, and revalidated the lists. Took awhile but I figured it out.

    One thing I would like it to di but I haven't been able to figure how is automatic counting of meals. this file is to enter trips out of town and is used to track meals for income tax deductions among other things. Currently I enter mt departure time and my arrival time and the days and hours away are automatically calculated. I then put in the number on meals and that is kept as a running total per month and a yearly total on another sheet. The totals are calculated automatically.

    Would it be possible to have the number of meals enter automatically based on the days and hours away? There are a few other options would like but are way beyond my abilities, one at a time, I'll keep plugging away/

  4. #4
    Forum Moderator - RIP 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
    29,464
    Quote Originally Posted by Attila
    Thanks that worked perfectly. I can't believe I missed that, so simple? I have been updating the log file, corrected a date error, replaced the terminal options, and revalidated the lists. Took awhile but I figured it out.

    One thing I would like it to di but I haven't been able to figure how is automatic counting of meals. this file is to enter trips out of town and is used to track meals for income tax deductions among other things. Currently I enter mt departure time and my arrival time and the days and hours away are automatically calculated. I then put in the number on meals and that is kept as a running total per month and a yearly total on another sheet. The totals are calculated automatically.

    Would it be possible to have the number of meals enter automatically based on the days and hours away? There are a few other options would like but are way beyond my abilities, one at a time, I'll keep plugging away/
    Hi,

    Almost certainly: the powers of Excel are extremely wide. If you want to zip up your workbook and attach it here with a few notes about the rules you want to apply, I'm sure we can come up with something.

    Rgds

  5. #5
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    How powerful is excel? I don't know what the limits are? My wish list:

    -the meals column (J) auto fills based on the days (E) and hours (F) worked, 1 meal after 4 hrs, another at 8 hrs, and 12 hours, then 24, 28, 32, 48, 52, 56 and so on, the cycle restarts every 24 hrs.

    - another area in the Year Total sheet that counts the meals per destination per year, by the month doesn't matter for this

    - another area in the Year Total sheet that tracks miles and income per month but having a start date other than the 1st of the month, we don't work by the hour, it's miles instead to a max per month, eack guy has a differant pay period date, mine is the 7th, so from the 7th to the 7th I have a mile limit, at the the 7th of each month it restars.

    - last an area that tracks income based on the pay weeks, we get paid every 2 weeks, it is nice to be able to track the current pay periods earnings and defer excess for a pay period that is a little short.

    I never dreamed this app could do anything like this, besides I have enough trouble getting the new terminal list to work, after reading and some trial and error I figured out the validate function, neat trick!

    I would greatly appreciate any help or suggestions.
    Last edited by Attila; 04-14-2008 at 11:02 PM.

  6. #6
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I have figured out some of the small stuff with a little help from the tuts on this site (valuable resource) and some trial and error. Big stuff is still way beyond my abilities at this time. Any suggestions on the wish list from the above post, main ones are the auto meal counter and meals per destination tally, can formulas be made to do that?

  7. #7
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    I wasn't clear on exactly what you wanted for the income tracking, but I think I solved your other two issues. Look through them and let me know if you have any questions. If the meals formula in L7 of Jan works, you'll have to drag it down and copy paste to the next several pages. I didn't see the sense doing that part until I was sure it was what you wanted.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Thanks darkyam that is what I was looking for. The chart on the totals page is real good, organized and easy to follow. The auto tally on the Jan page worked as I had hoped with one glitch, the time between meals. The first three are right on, hour 4, 8 & 12 after that they are all off by 4 hours. Counting from the hour entry in column B the meals should be at 4, 8 & 12 as they are then they should go to hour 24, 28, 32 then 48, 52, 56 and so up to seven days. Can it be set to do that? the trigger times are as follows:

    start Jan 1 10:00 meal 1 14:00 meal 2 18:00 meal 3 22:00
    Jan 2 meal 4 10:00 meal 5 14:00 meal 6 18:00
    Jan 3 meal 7 10:00 meal 8 14:00 meal 9 18:00
    Jan 4 meal 10 10:00 meal 11 14:00 meal 12 18:00

    and so on. Is this easily modified?

  9. #9
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Please Login or Register  to view this content.
    Does this work?

  10. #10
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    That solved that problem, worked great. I did all the changes and made a few mods here and there everything seems to work fine on all the first rows (row 7).Now to make the rest of the rows work the formula has to be put in each meal boxand the corrosponding row numbr has to be changed. Is there a fast easy way to do that or does each cell have to be done individually and the formula modified for each?
    Attached Files Attached Files

  11. #11
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Select the cell that has the formula and move your mouse to the lower right-hand corner of it until it becomes a black cross. Click and hold the left button and drag down as far as you need. This will automatically change all non-absolute cell references.
    Cell references can be made absolute by putting a $ in front of either the column or row in the reference. $A1 will always refer to column A, though the row may change, A$1 will always refer to row 1, and $A$1 will always refer to cell A1. When you drag a formula down or across or copy and paste it, any absolute references will not change. To see an example of this, look at the formulas I put in the table I made on the first page. The formula was entered once, then dragged across and down.

  12. #12
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Thanks much, another excellant tip, worked like a charm!

    The enxt thing I was thinking about is an offset month. Can a formula detect an entered date range and post all entries from that range to the summary sheet? or example my mileage date is the 7th of each month. Can all entires between say Feb 7th and Mar 6th in the miles column be tallid on the summary page? If this is possible I will set up the summary page to accomdate this and post for your recomendations. Thanks again.

  13. #13
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    I think it's possible, using a combination of Sumif and Indirect. What would you want to happen in January and December, though, when you're not getting a full month of data in? Are you going to be adding tabs as the years go or starting over every January?

  14. #14
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I never really thought about Jan & Dec, I was intending on starting a new workbook for each year instead of adding sheets, I think it will be more organized that way. How about a manual carryover cell in Jan to pick up whatever was started in Dec of the previuos year? If this works the way I hope then it should be able to be modified slightly to pick up bi weekly pay periods in a similar manner. Might be easier as it would just restart every 14 days based on a set starting point?

    I was playing with my copy and I just added to the first chart on the summary page, column G became the monthly periods (static display) and H is where I would like the running totals to go to. I don't fully understand the Sumif and Indirect commands and the custom formula's from the format cells area mean very little to me? I have a lot to learn I can see.
    Attached Files Attached Files
    Last edited by Attila; 04-16-2008 at 10:32 AM.

  15. #15
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Could you please post the current version of your workbook?

  16. #16
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    oops sorry, see post above your request

  17. #17
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Two more questions:
    1. If the dates are 2/7 - 3/6, should the total for that period go under Feb or Mar?
    2. What is to be considered the entry date, Departure or Return?

  18. #18
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    2/7 - 3/6 should be Feb I guess

    as for question 2 that pesents a problem that I didn't anticipate. Again using the 7th as the start date it does sometimes happen that I go out on the sixth and return on the seventh which is 2 seperate trips, I combined them to make it easy to setup the time away counter but that will screws up this idea. Would it work for both if I set up another column pehaps going across I could have Date left- time left -- date left 2-time arrived-date arrived then same as is now. That way the time counter came work off the date and time left and the date and time arrived while the monthly mile and pay half counters could work off date left and date left 2? Any other thoughts?

  19. #19
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    It seems you're overcomplicating this. If you put each trip on a separate line, then you just have to choose whether to go off date left or date returned. Adding additional columns seems like a nuisance that will make the sheet harder to read and maintain, as well as make the formulas more difficult.

  20. #20
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    How would that work for a round trip? I leave on the 2nd get to destination that night, return on the third, it is actually 2 trips but the away from home goes from left home on 1st leg to arrived home on 2nd leg.

  21. #21
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    I would say that Trip 1, you "return" just before you start the second. The way I read "return" is simply end of a trip. If you go to Miami tomorrow, then New York the day after, and return the day after that, trip 1 starts tomorrow and ends just before your flight to New York. Trip 2 starts with the flight to New York and ends when you return.

  22. #22
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    right and with the meals calculated on a time away basis it would go from date/time left on trip 1 to date/time return on trip 2, would the formula not have to read from 2 differant lines? Then if I get a turn, out and back in a short time and counted as 1 trip it would be on 1 line and the formula would be messed?

  23. #23
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I played a little more with this log, moved the Total Meals down to the meals chart - housekeeping mostly. I added in a Monthly Miles column which is part of what was discussed above.

    The way I can see it working best would be to move column "C" (Arrive Date) after column "D" (Arrive Time) then add in anoth column before "Arrive Date" which would be the new "C" then setting the formula to pick up the first instance of the date from Column "A" (7th for example) and the last instance of the final date (6th for this exanple) from column "E", the formula would have to jump from one month to the next for this to work right. Can this be done?

    I know there is an overlap at the beginning and end of the year, if this can be done then I was thinking of just adding a "Carryover" cell to the top of the Totals sheet to be manually filled in at the beginning of each year.

    I also added in Pay Halves columns to the Totals sheet. Pay cut off is every second Thursday at midnight so the hales would need to tally from the first trip on Friday (Jan 4th for example) from column "A" to the last trip on Thurday (Jan 17 for this example) from Column "C". This should repeat for each half thruout the year.

    I did not move the column on the monthly sheet as I did not want to screw up the formula, I think I can do it if I'm careful, but I thought it best to leave it for now in case this is getting to complicated and can't be done.

    If you have a way to make it easier please let me know, this was the best that I could come up with knowing how he totals have to be tallied but not knowing how the formulas can be setup?
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I tried to get the average time per trip to calculate for each month using =Average(F7:F50) but 2 things screwed up, first a trip that exceeds 24 hours uses 2 columns which skewed the average as the formula only uses one column and the other thing is the formula averages all the fields in that column, not just the filled in fields. How do I get around these issues?

  25. #25
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    OK, I finally got my work load manageable again, so here goes:
    E7 can be simplified to =ROUNDDOWN(C7+D7-A7-B7,0). F7 can be =MOD(D7-B7,1). Your average can be
    Please Login or Register  to view this content.
    , and you will have to hit Ctrl+Shift+Enter after you type the formula. If done correctly, curly brackets will appear around the formula.
    For the multiple trips in one, you could just have a comments column to track the multiple trips. If the meals is the big issue, that only matters if the last trip is under 12 hours or the first trip is under one day. If neither of these is the case, the number of meals should come out the same even if the trips are on separate lines. You could add in two more columns for final return date and change the formulas for total trip time; if the new columns are E and F, (making the former E and F become G and H, respectively), then G7 would be
    Please Login or Register  to view this content.
    and H7 becomes
    Please Login or Register  to view this content.
    Now for the fun one: B5 of Yearly Totals could be
    Please Login or Register  to view this content.
    This formula assumes that you do indeed add two more columns for the end date; if you don't change K7:K50 to I7:I50. It also assumes that you use the start date as the date of record, rather than the end date (it was easier since there will only be one start date on a trip). This formula can be dragged down through November. For December, you will have to replace the last half with either a manually entered number from next year's sheet or, if you clear out all data at the end of December except for December and then roll forward, B16 can be:
    Please Login or Register  to view this content.
    Quick question on the pay halves: what would happen to the pay on Jan 1-3? Or if the month ended on a Monday, what would happen to the last Friday and Monday's pay? Would they carry over into the next month?

  26. #26
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Thanks again, is there any limit to your knowledge of this app! The way I have this thing running now is good for one group of guys but doesn't really help another group. The average time per trip should be broken down by destination much as the meals were and there should also be a running total of the number of trips per destination.

    I was thinking of doing a complete rewrite and expanding the summary sheet to include average hours away and total trips per destination. Each destination column would then have 3 sub columns or rows as I may turn the chart 90 degrees.

    I am also looking seriuosly at using your suggestion of running the monthly charts by the tour of duty instead of by the round trip.

    If I change it to tour of duty (TOD) entries can the meals still track accurately? through the course of a month the number of TOD's per round trip varies between 1 and 3 sometimes more. A breid example below:

    Jan 3 04:00 - Jan 3 19:00 MJ - SC - MJ 1 TOD = 1 round trip

    Jan 5 02:00 - Jan 5 08:30 MJ - SC
    Jan 5 13:45 - Jan 5 18:15 SC - MJ 2 TOD's = 1 round trip

    Jan 8 06:00 - Jan 8 10:15 MJ -SC
    Jan 8 15:20 - Jan 8 19:45 SC - CH - SC
    Jan 8 21:35 - Jan 9 02:00 SC - MJ 3 TOD's = 1 round trip

    In each example above the time away and the number of meals would have to track from the first time in the first entry to the last time in the last entry to be accurate. Can a sheet be programmed to track like that?

  27. #27
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Would it simplify thing if the time away cells were combined into one cell that counts hours & mins, the day count serves no real purpose? 36 hrs is the same as 1 day 12 hrs?

  28. #28
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Here is a quick rewrite, I only set up Jan and Yearly Totals sheets for now as the formulas are screwed in columns F and L on Jan sheet. I eliminated the days column which appears to limit F to 24 hours which in turn screwed up L? I also updated the list sheet but not everything shows up in the dropdowns after I validated?

    I think this is all I am going to want now as it covers pretty much everything for my personal recordkeeping, and tracking as well as tax deductions.

    Any suggestions are welcome.
    Attached Files Attached Files
    Last edited by Attila; 04-29-2008 at 05:45 PM.

  29. #29
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Hi darkyam

    When you get a minute can you please have a look at this work in progress? The meal counter is out of whack, it wants to count at 4 hr 1 min, 8 hr 1 min, 12 hr 1 min, 24 hr, 28 hr, 32 hr, 48, hr, 52 hr, 56 hr, etc....

    And my dropdown terminals are not picking up the complete list? Thanks.
    Attached Files Attached Files

  30. #30
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Use this for meals:
    Please Login or Register  to view this content.
    It picks grants meals at 4hrs, 8hrs, 12hrs on day 1 and at 0hrs, 4hrs, and 8hrs after that.

    For your list, go to the Name Manager and change the reference for terminal to be
    Please Login or Register  to view this content.
    This will allow up to 50 terminals (you can adjust $B$60 to allow for more if needed), but will only show as many as exist. Using the Offset function in a named range makes it a dynamic named range; in other words, it grows automatically as you add to the list.

  31. #31
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Thanks I will give that a try. With the meals formula is it possible to have the first day pick up at 4 hrs 1 min, 8 hrs 1 min and 12 hours 1 min? The reason for this is that we have assigned 8 and 12 hour shifts, for an 8 hr shift we are only permitted to claim 1 meal but the counter is showing 2, say thing with 12 hour. It only applies to day one, but wouldn't matter if it extended.

  32. #32
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Sure, remove the latter +1/1440 in the formula.

  33. #33
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Tried this: =IF(F6>=1,3*INT(F6)+LOOKUP(MOD(F6,1)*6+1/1440,{0,1,2},{1,2,3}),LOOKUP(MOD(F6,1)*6,{0,1,2,3})) but it still changed from 1 meal to 2 at 8 hours? I'm missing what?

    With the terminal list. was there a limit with the original list? Where is the name manager?

  34. #34
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    It's more what I was missing:
    Please Login or Register  to view this content.

  35. #35
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    The location of the name manager depends on your version. In 2007, it is under the Formulas tab. In 2003, it is under Insert->Name->Define...

  36. #36
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Thanks both suggestions were right on, version thing was causing the confusion with name manager. I can now set up the rest of the year and redo the summary page.

  37. #37
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    How do I get the cells to show nothing when no data is entered? When I format them there is a sample box but I can't change the value? I would like F6 and down, L6 and down and M6 and down to show nothing, be empty when no data is present. I would also like M3 to display 0 not #N/A as it is now?

  38. #38
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    The last upload you gave me had L3 as the total meals. Have you added a column since then and, if so, where? If F still has the time, you can change F6 to =IF(D6="","",D6+E6-B6-C6) and begin the other two formulas with =IF(F6="","",<current formula>. This will automatically change the total Meals.

  39. #39
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I must have uploaded the wrong rewrite? The lastest and last I hope has another time column to track the total time at the away terminal. I also added an away list for those who work out of other terminals. That litle list upgrade you suggested works perfectly, makes updating a breeze!

    F6 remained the same and the formula worked as intended, however the other one already has an if prefix, the changed screwed everything up? Just trying to keep things tidy and reduce unwanted entries.
    Attached Files Attached Files

  40. #40
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    The change should not have screwed up anything. Consider the formula I gave you for F6. It was =D6+E6-B6-C6; after the change it is =IF(D6="","",D6+E6-B6-C6). All that changed was the addition of IF(D6="","", and a ) was added on the end. Follow that for the other formulas and nothing should go wrong.

  41. #41
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Ok that cleared the M column (I forgot the end bracket), altho I don't understand the referance back to the D column in the formula? I have found 2 other issues now, the time away (F) does not go beyond 24 hours and when I try to format the date columns to display Mar 01 format all dates come up as Jan 08? To clear the L column sample which is a simple sum would I use the same prefix as for F & M?

  42. #42
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    It does go beyond 24 hours, you just have the format set to military time. Put it back to a custom format of [h]:mm. D was chosen because logically, the meals and time of a trip cannot be recorded until the trip has ended. I could have chosen B as well, but if they are not entered at the same time, using B would result in an error. You can use either D or F for L and M.
    I don't know what you mean with your Mar 01 format question. What exactly is happening? When I put (just for example) 1/1 and 1/3 in B6 and D6, they show just like that. Why would you want them to change to March?

  43. #43
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Oops missed that time? Format must have changed with the formula? So the porpose of referring to D is to ensure it is filled in before the following columns tally, seems reasonable. Date thing not important, was just fiddling, tried to get it to show as Mar 01 or Jan 04 but it didn't work. I play with things to learn why and how, the 1/1440 thing in a peviuos formula I learned was the number of mins in a day, by changing it to 15/1440 I changed the trigger time to 8 hrs 15 mins for example.

  44. #44
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    I think I understand now what you meant by the dates. I thought you wanted them changed somehow. You can format the cells by going to Format Cells, then Custom, then entering mmm dd in the box.

  45. #45
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    Everything seems to be custom? I was selecting from the format selection box which wasn't working.

    Should I be locking the cells that auto calculate? I have noticed that the can be changed very easily screwing up the formulas?

    Now on to the tough stuff? The Wait Time column is to track the down time at the Away Terminal. It will generally be the differance between the end time in one row and the start time in the next row.
    Last edited by Attila; 05-16-2008 at 11:42 AM.

  46. #46
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Quote Originally Posted by Attila
    Everything seems to be custom?
    Not really sure what you mean by everything being custom. The formulas I have given you so far have all been built into Excel, so it's just a matter of figuring out how to use them. With user-defined functions and macros, you could get Excel to do almost anything you wanted, so this spreadsheet is actually very un-customized. I think only the formats have been custom so far and formats often will be, as clients and managers often have very particular ways they want to see the data.
    Quote Originally Posted by Attila
    Should I be locking the cells that auto calculate? I have noticed that the can be changed very easily screwing up the formulas?
    You can if you want. I suppose it depends on how many people are going to be using the spreadsheet. Once you have the formulas the way you want them, though, you should have no real reason to go into the formulas and edit them. It should be a simple matter of data entry.

  47. #47
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    By custom I wasn't meaning the formulas, more the fomatting. For example the date thing i asked about, if I select a cell, right click and select Format Cell I get a list of options, I want date so I select date and choose how I want it to display, Mar 01 from the list in this case but it does not work the way I think it should. If I select custom and enter it manually it works?

    As for locking I was thinking more of the new computer users. This workbook will be made avaliable to our local union members to track our trips and such. Most have trouble with cut and paste and I have noticed even hi-liting a formula can cause an error. I think I will secure the cells that do not require data entry just to prevent accidental changes.

    I added to my last post on page 3.

  48. #48
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    Have you tried a formula for wait time yet? Look at the trip time formula and I'm sure you can figure it out.
    If you have a bunch of new users, then yes, it might be a good idea to lock the sheet. Highlighting a formula only causes changes to array formulas, which require Ctrl+Shift+Enter to confirm.
    I don't know what you mean about the dates. Once you enter the custom format, it should come up as Mar 01, regardless of whether you put it in as 3/1 or Mar 01 or any other acceptable date format in Excel. Please start using actual examples in your post of what you are entering, what you expect to happen, and what is actually happening.

  49. #49
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I got the date thing figured out, just use custom and all is good as you suggested.

    I got a lot ahead of myself, I had intended on using one row per trip as you had suggested earlier but did not get to the formulas. I hope this doesn't screw everything up? I attached a sample of an actual trip, several things are wrong.

    Trip time should be the total round trip time so F6 could be blank and F7 should be 18:45 ((C6-E7 total). Wait time should be G6 blank and G7 9:10 (E6-C7 diff). Meals column should be M6 blank and M7 based on F7 and in this case be 3.

    This goes way back in this thread, really sorry if all this current stuff is useless due to this oversight. I was able to figure out the waait time as you thought but I don't know how to account for the varibles.

    Round trips are not always to the same destination and sometiMes there are short trips within a round trip which would use 3 rows, for exampl row 1 would be MJ - SC, row 2 SC-SC, row 3 SC - MJ, so the trip time would go from row 1 start to row 3 end and the meals would be based on the total round trip time.

    In the second example F9 & F10 could be blank, F11 should be 19:30 (C9-E11 total). G9 would be blank, G10 should be 4:00 (E9-C10 diff), G11 should be 4:00 (E10-C11 diff). M9 & M10 should be blank and M11 should be 3 (from F11).
    Attached Files Attached Files
    Last edited by Attila; 05-16-2008 at 01:00 PM.

  50. #50
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191
    My suggestion was to count each two-part trip as a separate trip. The formulas get considerably more complex than they are now if you try to have multiple rows on a single trip. Please see my post about how this doesn't affect meals except under certain and (assumedly) rather rare instances.
    I would suggest you start a new thread rather than continue this one. This will get more experts (and better ones) than me involved and they can help you with this far more efficiently than it seems I've been able to.

  51. #51
    Registered User
    Join Date
    02-08-2007
    Posts
    69
    I thank you for your help, I have learned a lot. You have been most patient and have some great ideas. I will start another thread from this point if you think that would be best. I really appreciate all you have done for me Darkyam!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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