Posts by Rowddawg

    Re: Creating child-rows that can be expanded


    You aren't limited to what I've done. You can group as many sets together as you'd like. See example. You'd just need to add rows for each level where you needed more subtasks. If this isn't what you're looking for, can you make a dummy sheet that shows what is is supposed to look like, and we can try to get there.


    EDIT: The yellow cells beside each subtask are what need to be changed to done to show complete for all tasks.


    Thanks.

    Re: Creating child-rows that can be expanded


    Can you give an example of what types of tasks/sub-tasks you want done?


    Here's my take on what I think you're asking. Task 1 (highlighted cell) in the attachment can be renamed and is set up as a group. There is a formula to the right of the yellow cell (in B1), that, when all the tasks have 'done' (without ') written beside them in cells C2:C6, it shows the task as being completed and it can be hidden by clicking the + button to the left of the group. It's an IF formula that uses AND to make sure all sub tasks show 'done' before calling the complete task done.


    Hope this is the direction you're looking. If not, let me know and I'll see about adjusting.

    Re: Replacing strings in forumulas


    Here's some basic vba code to do what the post is saying... just change the range in the vba code to match where your formulas will be. (i.e. instead of d9, a1:a100) All I did to create it was use the macro recorder, so I'm sure there's a more efficient way, but it should do what you're looking for.


    *edit: If your formulas do not have =, substitute what they do have in common, like the + in your example


    Re: deleting and changing order of words


    Lol... that is so much easier than what I came up with... which would definitely help over 9000 lines


    =IF(IF(ISERROR(SEARCH("DISCHARGE LINE",C5,1)),FALSE,TRUE)=TRUE,CONCATENATE((LEFT(C5,((SEARCH("DISCHARGE LINE",C5,1))-2))),((RIGHT(C5,(SEARCH("DISCHARGE LINE",C5,1))+(LEN("DISCHARGE LINE")))))),IF((IF(ISERROR(SEARCH("SUCTION LINE",C5,1)),FALSE,TRUE))=TRUE,CONCATENATE((LEFT(C5,((SEARCH("SUCTION LINE",C5,1))-2))),((RIGHT(C5,(SEARCH("SUCTION LINE",C5,1))+(LEN("SUCTION LINE")))))),c5))

    Re: Rank function ignoring only zero values


    Take your sample worksheet and place this where d5 starts and copy across the entire row. Should give you what you're looking for.


    =IF(D4>0,(IF(D4=0,0,RANK(D4,$D$4:$AY$4,0))),IF(D4=0,0,(IF(D4=0,0,RANK(D4,$D$4:$AY$4,0)))-(COUNTIF($D$4:$AY$4,"=0"))))


    Assuming that works, this should work then on your actual sheet...


    =IF(D94>0,(IF(D94=0,0,RANK(D94,$D$94:$AY$94,0))),IF(D94=0,0,(IF(D94=0,0,RANK(D94,$D$94:$AY$94,0)))-(COUNTIF($D$94:$AY$94,"=0"))))


    Have to say this stumped me for a while. Enjoyed it.
    Let me know if it works for you please.

    Re: sum if no blanks in matching cells


    Glad it worked out. I used to use ""'s a ton and had to work around it to create sums/etc until I read (on this site) that it was a poor practice and why. Actually helps keep things significantly cleaner in my opinion to just hide the 0's... Worse problem for me though was nesting multiple vlookups in an if statement (makes excel work twice as hard... once to figure out if it is needed or not, then again once it realized it is needed...) but that's another matter...


    As for the ;'s, I know they are separators between the different number types (positive and negative, for instance) and shows how excel treats all, but specifically I can't tell you. Someone else here I'm sure knows why.

    Re: sum if no blanks in matching cells


    Any time you insert ""'s into a cell, it no longer is viewed as having a number, which is why excel isn't giving you the result you want I think... From thereon out, you'll have to get excel to see it as one if you somehow revert it back to one, usually by using value(cell)...


    By using 0's instead of the blank it keeps this problem from showing up to begin with.


    Hope that helps.

    Re: Protect / Unprotect Removal VBA


    If you want to simply remove the specific password:


    Remove everything after password in the code...
    So

    Code
    ActiveSheet.Unprotect Password:="todd"


    Becomes

    Code
    ActiveSheet.Unprotect Password


    Etc...


    This should remove the specific password while allowing you to lock/unlock the sheet by clicking the protect worksheet button.


    If, more specifically, you want to remove the need to have the sheet protected/unprotected, I will defer to someone else's expertise... I think it would be more complex than simply removing those lines of code that contain password, but you could give it a shot...


    Hope this helps...

    Re: Conditional Fomatting - Not working for all cells


    Wonder if it's an adjustment to 2003 then... it worked in the dummy sheet I downloaded from you. Sorry that I'm not more knowledgeable on 2003. I'll try to look up some stuff later in the day if no one else has responded.


    Thanks.