+ Reply to Thread
Results 1 to 95 of 95

Adjacent cells

  1. #1
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Adjacent cells

    Does anyone know of a method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.

    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Adjacent cells

    You can probably do it with SUMPRODUCT.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Not terribly familiar with that function. Can you elaborate? And to clarify, I'm not looking to get a SUM of the values, I want to know how many times ANY value appears in a cell where an "X" is immediately to the right or left.
    Last edited by tapsmiled; 01-30-2014 at 09:11 PM.

  4. #4
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Here is a very quick completed example of what I would ultimately like it to show.
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Adjacent cells

    Here is a tutorial on SumProduct.

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adjacent cells

    This works on the samples in your file.

    =SUMPRODUCT(--(C3:I3="x"),--(B3:H3<>""))+SUMPRODUCT(--(B3:H3="x"),--(C3:I3<>""))

    Copy down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I will give it a shot. Thanks everyone for the responses!!!

  8. #8
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    @Tony Valko (or anyone else that understands this)-----can you explain how this works please. It did exactly what I wanted to do, but I don't quite understand why at some points you are starting at C3 and other are B3. Because I will eventually be adapting this to a much larger scale, it would help me to understand it a little more so that I can make the necessary changes.

    Thank you so much!!

  9. #9
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    upon closer inspection, its not quite there yet. If I change the location of the "X" or add additional "X", I am getting different results. I put an "X" in B3, and it threw the entire thing off. Could this be a result of the question I asked in my prior message?

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Adjacent cells

    Did you read the tutorial that I posted a link to? It explains in great detail how Sum Product works.

  11. #11
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Yes and thank you. It does explain it; my comprehension of it, however, is what may be an issue.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adjacent cells

    Can you post a file with more examples and tell us what results you expect for each example.

  13. #13
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I posted another thread with a 2-part problem. I solved the first problem, but I never got an answer on the second part.

    Basically, this is going to be a sheet with roughly 200 employees (A column) and 7 columns (days of the week B:I). The X represents a day off, and the sick time usage (in 4 hour increments) can be recorded in any of the 7 cells. I am tasked with tracking sick days that are in conjunction with days off. So, I do not need to count X's (since there will be 2 for each employee); I need to count total sick time used (J column) and how many times sick time usage is recorded next to an X (K column).

    I hope this clarifies things a bit.


    EDITED TO ADD: Days off (X) are not necessarily static, so the form needs to have the flexibility to account for when the X's are anywhere between B:I.

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adjacent cells

    To avoid misunderstanding, it would be a good idea to post several examples and tell us what result you expect for each example.

  15. #15
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Okay, give me 5 minutes

  16. #16
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Okay, here it goes...

    We have to show an 8-hour shift as 2 4-hour blocks, which is why this may seem like overkill. Column K represents how I would like the data to appear. If you look at each employee, you will see occurences where S is next to an X. Employee D, for example, has sick time recorded for 1/22 (column D) and for 1/25 (column G). These 2 instances are in conjunction with days off (Column E:F). So, he utilized 16 hours of total sick time (calculated properly in Column J) and had 2 occurences (showing, but not calculated, in column K). If you look at employee F, there was sick time used, but that sick time was not in conjunction with a day off, so column K equals 0. I am looking for the calculation for the K column.

    Of course, there will be an issue when dealing with previous weeks' days off, so to account for this, I may be forced to do this on a month-by-month basis rather than weekly.
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Tapsmiled, See your file and see if this would work for you! It uses an array formula and looks more at adding symbols (i.e. your "F" and "S" and I added a "P") but you could use other symbols as well like ▲ ▼ , etc. So it really isn't a matter of being next to an "X" it is the symbol you use that will allow the formula to count. Though you would need a number in this scenario along with the symbol, i.e. "4S" for 4 hrs sick. Anyway... just a thought. It works well for me.
    Attached Files Attached Files
    Last edited by lilsnoop; 02-01-2014 at 07:47 PM.

  18. #18
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thanks! Here is the question. I have to make this somewhat dummy-proof because more than 40 people will have access to this form to make changes. As such, I want it to be simple. For my purposes, F=FMLA, S=Sick (I dont know that there will be the need to determine when the person called in sick per se). Is your formula just looking for which spaces are not blank (in relation to those that have an X)? This seems to be on the right track so far, but now that I think of it, my version goes by the assumption that sick time is ONLY used in 4 or 8 hour increments. Am I bound by 4 hour increments? Could I put 2S, for example, and have it calculate 2 hours?

  19. #19
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Oh, I just changed one of your numbers and it does affect the total. I will keep fooling with it.

  20. #20
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Could you adjust the columns to:

    Total Sick Time Total FMLA Time Total Time Used in Conjunction

  21. #21
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    tapsmiled,

    I edited my earlier post and actually updated the attachment file too. So you might want to look at the new file. Afterwards you should be able to lock the template page as well as your formula columns so they can't be tampered with and just leave your schedule part available to be changed, etc. Yes, regarding the total sick time, FMLA. I'll edit my file to do that for you.

  22. #22
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    On your example, I changed E4 to 1P, and it correctly made the adjustment; however, when I added 1P to C4, it changed the value in your last column (I deleted my TOTAL USED IN CONJUCTION column).

  23. #23
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    The new file still looks the same. By the way, THANK YOU!!!

  24. #24
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    How is this? I also password protected the main sheet (not template) just so you can see the protective qualities. The password to un-protect is password.
    Attached Files Attached Files

  25. #25
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    It's close...real close. Ideally, it would not tabulate the total number of hours in conjunction but rather the total instances. While the top and bottom are correct, the L4/5 shows 1.25 (which you calculated from the other columns), but the actual needed calculation there would be 2---derived from E4 and H5.

    EDITED TO ADD: I can definitely protect the finalized version, so it shouldn't be a problem. I just don't think I will be using the P variable. Also, to throw another wrench into the whole thing, if I added V for vacation and wanted to calculate how many days were in conjunction with Vacation time, could I check for both issues. In this case, INCONJUNCTION WITH SICK would not necessarily have to be a separate column from IN CONJUNCTION WITH VACATION.
    Last edited by tapsmiled; 02-01-2014 at 08:15 PM.

  26. #26
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Glad I can help. Regarding the "1P" edit you discussed above, you can just format those cells to two decimal points, but that column divides by 8 as it counts the number of days. That employee hadn't totaled a full day yet so it would make into a decimal.

  27. #27
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    But with the 1P, it changed the In Conjunction total when it shouldn't have. The sick time was used on a cell that was not next to an X

  28. #28
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    You can do whatever.. comp time, personal leave time, etc I added vacation for you.
    Attached Files Attached Files

  29. #29
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Employee B: By formatting the number to no decimal points, it shows 1 day in conjunction (for the 1.250) but the correct number should be 2.

    And the N:Q columns could, ideally, just be hidden right/ Since they are not crucial for the user to see...

  30. #30
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Basically, any sick time or FMLA (regardless of the increment) used next to a vacation or day off is the calculation I need. Because you are dividing, I think that is where the problem arises.

  31. #31
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Regarding the 1P issue... I guess I'm having difficulty understanding what the issue is. I used "P" as a symbol for whomever is doing the schedule to place the hours (i.e. any # of hours in addition to the "P") so that it will add those symbols up. It really isn't looking for that symbol which is next to an "X". So if someone who is doing the schedule uses "1P" and doesn't have it next a scheduled day off (in this case an "X") it would still add it up and would then be the schedulers error; meaning they should have used "1S" and put only use P's and F's for sick time pass day tracking. Why I like this method personally, is what if an employee is taking two vacation days but calls in sick the day before his/her vacation day. It isn't an "X" but with this method you could put "4P" in your schedule for each 4 hr block and note that similarity to the "X".. Hope that makes sense or I'm just not really understanding the issue.

    edited: correct hide those columns & I was dividing by 8 thinking that was # of days not incidents no matter the duration of time.
    Last edited by lilsnoop; 02-01-2014 at 08:38 PM.

  32. #32
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Employee B: E4 and H5 are 2 separate incidents of sick time usage. It calculates the time as 1.25, which I changed to no decimal points so it shows as 1. But...it should show as 2. If you go to H6 and add sick time there, it should change the IN CONJUNCTION total since I6 is an X.
    Last edited by tapsmiled; 02-01-2014 at 08:44 PM.

  33. #33
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Quote Originally Posted by lilsnoop View Post
    Regarding the 1P issue... I guess I'm having difficulty understanding what the issue is.
    Adding 1P at the place where I added it should not have had an affect on the IN CONJUNCTION total

  34. #34
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    I'm thinking of an alternate formula...

    For clarification in your sample workbook SICK TIME (SAMPLE) - you have "S" in a 4hr block (cell E10) and it gives 1 in the conjunction total. Should that be zero.
    You want the # of total incidents where someone left work or didn't work at all that have symbols "P" or "F" no matter the time duration. So a 1P would be equivalent to a 1 in your conjunction total?
    Last edited by lilsnoop; 02-01-2014 at 09:00 PM.

  35. #35
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thank you very much. Your help is greatly appreciated. I even thought of, on another sheet like your template, something like an IF statement just to get a TRUE/FALSE and then counting the total number of T/F.

    in D2: =IF(D2="X",IF(A$1,C$1="X","TRUE","")-----something like that, but I haven't really tried to see if it would work. I'm a complete noob, so I have no idea if that would actually work.

    EDITED: The A$1,C$1 part says I have too many arguements.

  36. #36
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Try it like: =IF(D2="X",IF(A$1:C$1="X","TRUE","")

    edit to add: try this and see if this is what you are looking for -for your conjunction box. It should go in the top one: =COUNTIF(C2:I3,"*P*")+COUNTIF(C2:I3,"*F*")
    Last edited by lilsnoop; 02-01-2014 at 09:06 PM.

  37. #37
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I mistyped. Should be C$2 and E$2.

  38. #38
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Here's what i am thinking. Why couldn't I use the formula we just came up with on another sheet, basically mimicing the first sheet. The first sheet would be where the information is physically added, and the second sheet is where the calculations occur.

  39. #39
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Not working. perhaps a series of IF statements.

    Something like this for example: =IF(E2="X",IF(D2,F2="4P"),"") This doesn't work, but perhaps you can see my train of thought. I used 4P as a sample because I don't know how to tell it to look for "anything with a S or F"

  40. #40
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    If you would attach the last sample I uploaded but put in the conjunction box field what it should calculate to, it might help me out. Feel free to include explanation comments too. Thanks.
    Last edited by lilsnoop; 02-01-2014 at 09:31 PM.

  41. #41
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Here it is. The cells that are blue are days in conjunction with sick time. The purple cell M4/5 should read 2 (like I changed it) because if you look to the left of it, you will see 2 separate days where sick time (blue) was used next to X.

    In the space below it, I tried to work it a different way. Maybe if you look at those formulas, you can somehow use amend them to do the same thing.
    Attached Files Attached Files

  42. #42
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Ok, thanks. I'm looking at this issue. What makes it difficult is the 4 hr blocks; meaning that if the employee has the whole 8 hr day it appears to have two "4P's" where as if there is another day with just half of the shift off with a "4P" it wants to add that as one incident too. This may get real complicated, but I'll try and think of an alternative if I can.

  43. #43
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I tried this. I'm running into an issue with the VALUE IF FALSE.

    =IF(H$2="","",IF(H$2="X","X",IF(H$2="4F",IF(G$2:I$2="X"," "),1)))

  44. #44
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Quote Originally Posted by lilsnoop View Post
    Ok, thanks. I'm looking at this issue. What makes it difficult is the 4 hr blocks; meaning that if the employee has the whole 8 hr day it appears to have two "4P's" where as if there is another day with just half of the shift off with a "4P" it wants to add that as one incident too. This may get real complicated, but I'll try and think of an alternative if I can.
    But that second 4P is considered another separate incident.

    If an employee takes the entire 8 hour shift off (Monday for example), it is one incident. If that same employee then has 2 days off (Tuesday and Wednesday) and takes 4 hours sick on his next scheduled workday (Thursday), that would be a separate incident for a total of 2 instances in conjunction with days off.

  45. #45
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I'm stepping away but will be back tomorrow to check on the progress. Thank you so much for your help so far!!!

  46. #46
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    tapsmiled,

    After much thought and time.... I believe I've figured a way to accomplish what you need. Unfortunately it requires more formulas, which I added a formula page that can be hidden in the future, etc.
    So hopefully you'll understand the formulas to adjust them in the future if needed. Best of luck to you!
    Attached Files Attached Files
    Last edited by lilsnoop; 02-02-2014 at 12:33 AM. Reason: correction made in workbook

  47. #47
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thank you so much for all of your efforts.

    I took your sheet, removed all of the S,P,V amounts from the 7 days (leaving only the X's) then started to add new amounts for sick time, and none of the calculations changed when I added only S (sick time), but they did change when I added F (FMLA) time.

    To simplify, the P variable can be removed. Here are the ones that we are likely to use:

    S=Sick
    F=FMLA
    V=Vacation

    Sorry for being so difficult.

  48. #48
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Okay, I see what you did. I changed *P* to *S* in each of the white columns on the Formula page.

    I think I see how it works, and I think you got it. If I wanted to expand this out to a whole month, what is the easiest way to do it? Do I just have to go the long route and copy/paste everything or is there a simple way to add 24 more columns?

    EDITED TO ASK: If I add columns in the middle of the sheet, will the formulas adjust?
    Last edited by tapsmiled; 02-02-2014 at 03:35 PM.

  49. #49
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    and to confirm, on Sheet 1, can I now completely remove N:Q, and is the Template page necessary now, or can that also be removed?
    Last edited by tapsmiled; 02-02-2014 at 03:41 PM.

  50. #50
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Actually, I see where N:Q are needed.

  51. #51
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Hi tapsmiled,
    You will always need the template sheet as those symbols are what your array formulas are looking for: The array formulas are currently your N:Q columns. As those formulas are now also on your "Formula" sheet you could delete the one's on sheet1, but then you'd have to amend your J,K, & L columns as they currently sum the N:Q Columns. Regarding adding columns in the middle-as long as they are days of the week type columns I don't think you'll have any issues. You'll just have to try it and see. Just save a backup before doing so though for the "just in case". And.. if you add more columns you will definitely have to add more formula based columns similar to the ones I have on the "formula sheet", specifically A thru N. That is the only way that I know of that you'll get your conjunction summary to add correctly. Hope I've answered your questions.

  52. #52
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    You are truly fantastic. I can't thank you enough for sticking with this and solving it in the end. I'm going to leave this thread open for now just in case I run into any issues, but when I'm certain it is perfect, I will mark it as solved.

    THANK YOU SO MUCH FOR YOUR HELP!!!

  53. #53
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    No problem, If you extend it out to a month long and run into issues. I'd be more than glad to try and help you out.

  54. #54
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thank you! Working on that right now.

  55. #55
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    lilsnoop,

    UGH!!! I went and expanded the entire workbook, making all of the adjustments, and somewhere along the way, I lost the calculation. I attached my near-completed version. Could you take a look at it and see if you can find where I lost the final calculation? You can put X's anywhere you want then fill in sick time wherever necessary. It still calculates total sick but its not showing the conjunction totals.


    Also, is there an easy way to add "*V*" to this?


    =COUNTIFS(Sheet1!C141,"X",Sheet1!D141,"*S*")+COUNTIFS(Sheet1!C141,"*S*",Sheet1!D141,"X")+COUNTIFS(Sheet1!C141,"*F*",Sheet1!D141,"X")+COUNTIFS(Sheet1!C141,"X",Sheet1!D141,"*F*")
    Attached Files Attached Files

  56. #56
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    As far as the error in calculations, I'm guessing that I'm using the wrong reference cells on the formula page, which probably occ'd as I added columns. Since I was a bit confused, can you mostly check the column headings (B/C Column) and make sure I am referring to the proper cells on Sheet1. If the first row of calculations is okay, I can copy them down for all the employees.

    PS---I know the Column headings are basically irrelevant to the calculation, but it helps to keep things straight

    EDITED TO ADD: Upon further inspection, I am noticing that when I put a value as "4S", it is tracking the 4 (adding it to the sick total) but it is not calculating the in conjunction. If I remove the "4", it does calculate the in conjunction field.
    Last edited by tapsmiled; 02-02-2014 at 07:27 PM.

  57. #57
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I think I figured out the issue. On the formula page, I didn't extend down the BP:BS formulas. I'm still checking but I think the disaster has been averted for now.

  58. #58
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Due to Superbowl I probably won't be on this site to much tonight, but your formula to add vacation time would be for cell A2 in formula sheet: =COUNTIFS(Sheet1!C2,"X",Sheet1!D2,"*S*")+COUNTIFS(Sheet1!C2,"*S*",Sheet1!D2,"X")+COUNTIFS(Sheet1!C2,"*F*",Sheet1!D2,"X")+COUNTIFS(Sheet1!C2,"X",Sheet1!D2,"*F*")+COUNTIFS(Sheet1!C2,"*V*",Sheet1!D2,"X")+COUNTIFS(Sheet1!C2,"X",Sheet1!D2,"*V*")

  59. #59
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Also. Look at the formula in the "conjunction box" as you will need to extend those out due to you adding a whole month worth of data not just 7 days as before.

  60. #60
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Nope. Still not working. It looks like it is only recognizing when sick is after an X but not when it is before. I think I messed up the 2nd half of your equation.
    Last edited by tapsmiled; 02-02-2014 at 08:49 PM.

  61. #61
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I realize that i did not adjust the TOTALS IN CONJUNCTION column to reflect all of the other columns I added. So this is the formula I added to that column (AK now):

    =SUM(Formula!B2+Formula!D2+Formula!F2+Formula!H2+Formula!J2+Formula!L2+Formula!N2+ Formula!P2+Formula!R2+Formula!T2+Formula!V2+Formula!X2+Formula!Z2+Formula!AB2+ Formula!AD2+Formula!AF2+Formula!AH2+Formula!AJ2+Formula!AL2+Formula!AN2+Formula!AP2+ Formula!AR2+Formula!AT2+Formula!AV2+Formula!AX2+Formula!AZ2+Formula!BB2+Formula!BD2+ Formula!BF2+Formula!BH2+Formula!BJ2+Formula!BL2)


    BUT...it still is not calculating sick time prior to a day off and it is calculating sick time after a day off twice.

    I attached the file again with some of the changes I have made since the last time I posted it. I added values in the AC:AE fields so you could see the calculations to the right. AK2 is calculated correctly, but AK6 and AK10 are not. For it to be correct, they would all indicate a value of 1
    Attached Files Attached Files

  62. #62
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    I went back to the Formula sheet and made sure that I copied the formulas all the way down, and of course, I hadn't done it properly. Now that I did, it seems to be working.

    Now, if I can continue checking and not screw it up further, I think it may be perfect.

  63. #63
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Well I just popped back on the site and at first glance I see in your template sheet you still have "P" and not "S" under sick time Passday. That will make a big difference if you are tracking what you want. Otherwise, I'll look at the rest to see if I see any other issues.

  64. #64
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thanks...I really appreciate it. Also, waaaaay back about a thousand posts ago, I asked if there was a way to add the V option so that the IN CONJUCTION tracks all sick time used in conjunction with any time off.

    I'm really making you work on this LOL

  65. #65
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    The formula is:
    Please Login or Register  to view this content.
    edited to add: Yeah, I saw you had failed to drag a formula down and you were getting duplicate counts. Glad you caught it. The bold part of the formula above is what you need to your existing formulas in the white rows in the "formula page". Is there any other issues you need assistance with?
    Last edited by lilsnoop; 02-02-2014 at 10:38 PM.

  66. #66
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Just so you know. If you put a "4S" in any cell that isn't next to an "X" or "V" it may not be added into your "Conjunction" total, but it will throw off your total sick time count as it stands currently. You would want to go to your "template sheet" and remove "S" from under your sick time passday column (cell A3). I don't believe by doing this you'll mess up anything based on how we worked the formulas in the formula sheet. Test it out though! It was this circumstance that I personally use a "P" for passday. But I think all will be well if you remove the S.
    Last edited by lilsnoop; 02-02-2014 at 10:48 PM.

  67. #67
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Actually, the idea is that I do want to record all sick time used, but I also want to specify which of those hours are used in conjunction with scheduled days off and vacation days. Basically, it is an administrative tool to track patterns of abuse. There are over 400 employees, and there are many personnel issues that we are required to track. This will dramatically simplify that task. Plus, it will give me, hopefully, some brownie points since I was not directed to do this. So the issue you just brought up is an advantage, not a problem.

  68. #68
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Quote Originally Posted by lilsnoop View Post
    The formula is:
    Please Login or Register  to view this content.
    edited to add: Yeah, I saw you had failed to drag a formula down and you were getting duplicate counts. Glad you caught it. The bold part of the formula above is what you need to your existing formulas in the white rows in the "formula page". Is there any other issues you need assistance with?
    I will check this tomorrow when I get back to work. Thank you so much!! You're a champ!!!

  69. #69
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    I understand completely! I see abuses in that area as well (unfortunately). But with that "S" added in the template page as specified above it will not calculate accordingly. For example: put a 4s in cell AE6 and AE7 and now put a 4F in cell AC7. If you do that your total sick in AH it will show a total of 20 not 12 like it should. But if you remove the S from the template page under the 1st column only it will calculate properly and I don't believe affect anything else you were trying to accomplish.
    Last edited by lilsnoop; 02-02-2014 at 11:04 PM.

  70. #70
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    S and F are both values to track since people abuse FMLA time as well, so it is important to calculate those times together.

  71. #71
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Well it is..but I think the way we adjusted the formulas in the "formula" sheet i.e. it looks for those combinations it kind of does that anyway. Take the S out. and run some sample scenarios and I think you will still accomplish everything you want. Let me know!

    edited to add: the array formulas just look for a symbol as designated by in the template sheet. Because you will be using an 4S potentially on days that won't necessarily be next to a vacation day or an X that is where you will have problems; specifically this will affect your total in column AM on sheet1. This is why it is nice to use something different for days next to a vacation day or an X; like "4P" or "4S/P" (the later being what I use). There might be an alternate way to sum the total hours though from the formula page. Let me see....
    Last edited by lilsnoop; 02-02-2014 at 11:19 PM.

  72. #72
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Every time I tackle one if the projects, I learn so much about excel. This forum is really amazing.

  73. #73
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Quote Originally Posted by lilsnoop View Post
    Well it is..but I think the way we adjusted the formulas in the "formula" sheet i.e. it looks for those combinations it kind of does that anyway. Take the S out. and run some sample scenarios and I think you will still accomplish everything you want. Let me know!
    Will do!! Just out of curiosity, if I added a column immediately after the Employee Name, would it throw off every calculation, or would those calculations automatically adjust? And if this is ultimately viewed as overkill, splitting the shift as we have done, would I just need to remove the 1900-2300 row and each of the Formula sheet calculations resulting from those rows?

  74. #74
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    I think you'll be fine adding the new column.... but.. your headers columns in your formula sheet will have to be adjusted (i.e. B/C Column will become C/D Column, etc) I added some additional comments in my earlier post in case you didn't see them too.

  75. #75
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    My apologies..but I discovered that to add the Sick time against a Vacation day, etc my formula was off. you would need to add this section to your existing formula:
    Please Login or Register  to view this content.
    and your whole formula for column A would look like this in your formula sheet:
    Please Login or Register  to view this content.

  76. #76
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    And..unfortunately. I'm still back to the same conclusion if you want to be able to add the total hours of sick time an employee used either immediately before or after a vacation day or scheduled day off (ie "X") then you would need to have another code "P" or "S/P" to distinguish it by. You would then have to put that symbol in the template in column "A" and then adjust those formulas taking out the "S" and replacing it with the "P" or "S/P" again. Sorry but this is the only way to distinguish for that summary only how many hours were used in that type of context. I hope that makes sense.

  77. #77
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thanks. I will check it tomorrow

  78. #78
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Ok. I've attached your workbook and I fixed the column headers in your formula sheet. I also added all of the formulas to now associate the sick time next to a vacation day, etc. I did add the special code of "S/P" on the template sheet to be affiliated to any sick time taken immediately before an "X" day or a "Vacation" day. I also added a column on sheet one to total your sick time hours taken before or after a "X" day or "Vac" day, etc. I added one additional feature you may like or not like. Your sheet now has a vba coding which you will have to enable to use. The vba will allow a drop down menu which I've added some values like "4V", "4S", "4S/P", etc. I use it my schedule and it makes it nice, specifically as it will only allow the things in the drop down menu to be added into a particular cell. This will avoid other people from putting codes in that you don't want. If you don't like it, just right click on the sheet1 tab and click the "view code" a project window will appear and you will see the vba coding. just delete it and your automatic drop down selection will go away. If you like it, you can add more items you want in the drop down by continuing the pattern in the code; i.e. "1S, 2S, 3S, etc." Just to make it clear the "4S/P" symbol should only be used when a person calls in sick immediately before an X day or vacation day, anytime else it would just be 4S. The same problem as we experienced with the "S" issue could happen with the "F" (FMLA) if you use it on a day that doesn't come immediately before or after a vacation day or an X day. If so, you may have to go to a "S/F" symbol and alter the formulas in the formulas page for that; not to mention change the "F" to "S/F" on the template sheet. Otherwise what happens when a person is on FMLA time for 5 weeks? or two days between days off, etc. (Just something for you to think about). Anyway see attached sheet. You'll still have some work to do but I tried to help out to give you an idea.
    Attached Files Attached Files

  79. #79
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Wow!! That is truly fantastic!!

    When I opened it, I received a run-time error, but I was able to move around with no issues. I will probably spend the next few hours toying with it.

    I did show it to a co-worker, and despite the fact that our schedules are done on a 4 hour basis, she expressed concern over splitting the shift (as we have it here) rather than just listing it as an 8 hour day. With your drop-down, I don't know if splitting the shift is necessary. Is this a massive adjustment or could it be changed "somewhat" easily? I realize that it would entail altering the formula page substantially; if you explain what would need to happen, I will make the changes.

    Also, because I never know when to stop, I am thinking of merging this with other tracking devices that we have (evaluation dates, 90 day notifications, etc.) into a single, all-encompassing dashboard. If I copied the Shift and Formula pages and renamed them for each month, I shouldn't run into any problems, right? All of the formulas, drop-downs, etc would change as the sheet names change I would assume.

    You are a freaking rock star!!! If you were here, I would take you out and buy you a beer.....or some other beverage of your choice. Thank you so much for all of your time!!!!!

  80. #80
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    The part that I am not understanding is the need for s/p. Is there a reason why it wouldn't just calculate S's and F's? We don't use P right now, and having to differentiate when to use it and when not to use it could become a chore. I want people to just be able to go in, when someone is out sick, and just record that.

  81. #81
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Not sure why you are getting the run-time error. hmm... Making it an 8 hr shift would require revamping your formulas, but the principle would be the same. You would still require the template page and something to distinguish those types of sick time that are next to a vacation day or scheduled day off like in my earlier attachment. Glad you like the drop down. We could even add a separate one for your employee list if you want.

  82. #82
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    The employee list would be great.

    In looking at the formula page, is it as simple as removing the odd cells, since they refer to the second half (1900-2300) of the shift then altering the calculation so as not to look for A3,A5,etc.

    I'm also assuming that unmerging everything on all pages is the way to start. I know it is an extensive project now to go and remove half of our data, but I don't mind putting in the time.
    Last edited by tapsmiled; 02-03-2014 at 03:31 PM.

  83. #83
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    I'm attaching a sample schedule that I use. Each sheet would be its own month, but for this example I just have February. Not that you have to do it like this, but just another option. My array formulas are also present but I hide them as I do column B and row 1. Otherwise.. make your draft schedule and we can figure out what we need to. I probably won't be online until after 8pm.
    Attached Files Attached Files

  84. #84
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thanks. I was able to completely adjust the sheet, I believe properly, to eliminate the 4 hour blocks. I adjusted all of the formulas as well, and it is working.

    When you get back online, can you explain the difference between S and S/P?

  85. #85
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    By the way, here is the revised edition. I automated the dates, and I added the 1S,2S,3S,etc variables to the drop-down. Seems to be working fine right now, so I'm going to work on expanding it to the full year.
    Attached Files Attached Files

  86. #86
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    tapsmiled, Your schedule looks good! Regarding your question about the difference between "S" and "S/P" is that the only way you will be able to sum the hours of sick time affiliated with sick hours used immediately before or after a vacation day or day off is by using a drop down with "S/P"; i.e. 1S/P, 2S/P, etc. For instance if you put a "8S" in cell AC2 next to an "X" your sick time total will be correct, your conjunction box will remain at 1 (not 2 like you want it to) and your "sick tracked" total for that employee will be incorrect. So whoever is doing your scheduling should only use any number (1S,2S,3S,4S,etc) when the sick time does not fall immediately before or after a vacation day or day off (X). If the employee's sick day is immediately before or after a vacation day or day off the scheduler should use the (1S/P, 2S/P, 3S/P, 4S/P, etc). That is the only way you will be able to distinguish or rather track suspected sick time abuse. As stated previously in this thread, you should also consider changing or rather adding an S/F symbol too and use that as your method to track the FMLA time used immediately before or after a vacation day or day off. Just use the "F" for those occasions when an employee calls in sick for FMLA related reasons that don't fall immediately before or after a vacation day or scheduled day off. I don't know what you need your schedule to do in its totality, so I don't want to presume anything. But nice job so far!

  87. #87
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Thanks, though you've done all of the difficult parts. I have added sheets for every month and I am now converting the Formula page to properly refer the values.

    Regarding the S/P...if the SICK TIME TOTAL is properly being calculated (recognizing the difference between 4S and 6S for example), then the IN CONJUNCTION should be calculated differently....a single instance for each one. Rather than have "*S/P*" in the formulas, couldn't I just have "*S*", since, if I understand correctly, the formula is designed to check one cell before and one cell after?

  88. #88
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    tapsmiled,

    Well before you adjust all your columns putting it back to "S" instead of "S/P" , I would test out every scenario and see if your calculations come out in every Column from AI to AM. I think you'll find your going to run into issues; which I know were addressed earlier. I'm attaching your workbook with some additional vba if you want to look at. it will now allow you to have a drop down for the names (which you'll have to change) and one for your shift (also can be edited). I also put that 8S in as stated in my earlier post so you can see the summary columns and what happens.
    Attached Files Attached Files

  89. #89
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    You read my mind. I'm still adjusting the monthly pages. Man, I wish I would've remembered Find And Replace about an hour ago....

  90. #90
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Funny. Off to the Gym be back later.

  91. #91
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    Enjoy!

    When you see this again, hopefully, I will be closer to finished. I am getting a circular reference though. I've made a sheet for every month, added the dates, and am going through and removing the "extra" days (since i initially made 31 columns and not every month is cooperative. The reason I am doing this is because I plan to do a summary page.

    Also, can you think of a way, in a summary page, to
    a) calculate the total from each of the sheets (easy)
    b) check the occurences where days are taken in conjuction and determine the most frequently occuring day of the week----days/dates are in there as Sat 02/01, so it would be great, on a summary page, to list Saturday as the day of most frequent occurence.

    I am going to probably employ the Employee Name drop down on the Summary page then have it auto-fill the appropriate rows on each of the months. I will also, on the Format sheet, list their names and CCN (ID #'s), and Dates of Hire so that those can auto fill. I am getting a lot of circular references all of a sudden. This started when I added all of the sheets then started removing excess days. If you look at AS:AT, I'm not sure what those are supposed to refer to, so I haven't done anything with them. The IN CONJUNCTION calculation went off somewhere b/c it is not working at the moment.

  92. #92
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    It is saying the file is too big now. I'm noticing that it is running noticeably slower too.

  93. #93
    Forum Contributor
    Join Date
    11-13-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    464

    Re: Adjacent cells

    By the way, I realize the Most Frequently Occurring Day is probably unrealistic at this point.

  94. #94
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    Not really, Once you get your schedule down I would recommend you going over to the VBA-Macro part of the forumn and see if someone might be able to write some code that would analyze each worksheet and give you a summary report for sick time, vacation time, etc. That's what I did and it makes it extremely nice. So for instance if you wanted a sick time report you'd click a button on the summary page and it would print out a list of each employee documenting each day they were sick by day of week, date, the sick time coding (i.e. "S", "S/P", "F", etc), and hours used as sick in each category. Mine also then sums the total for each employee. I'm not that versed in writing that complex of code though, so I can't help you there.

    I'm looking at your worksheet. Have you gotten any of your other sheets (i.e. March, April, etc) formulas working?

  95. #95
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Adjacent cells

    In your summary sheet put the following formula in cell "I2" then scroll down:
    Please Login or Register  to view this content.
    Then in your D2 cell in your summary sheet put this formula:
    Please Login or Register  to view this content.
    You can always hide the "I" column, but this will at least eliminate your circular reference issue on that sheet. The private message will tell you were your other circular references are.

+ 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. [SOLVED] Concatenating adjacent and non adjacent cells depending on entries
    By TBHusker84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2013, 10:27 AM
  2. Formula needed for dividing 2 sets of cells and excluding adjacent text cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2013, 06:45 PM
  3. Replies: 3
    Last Post: 07-30-2012, 11:20 AM
  4. Replies: 3
    Last Post: 02-21-2012, 12:14 PM
  5. Adjacent cells in non-adjacent list?
    By anakeimai in forum Excel General
    Replies: 3
    Last Post: 09-10-2008, 05:49 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