Posts by widgetwonka

    Re: Linking checkboxes and radio buttons


    Add a control radio button, and write a macro for it's click action that writes 1 or zero to your linked ranges depending on your desired settings.

    Something like this (replace the ranges as needs and names as needed):

    Re: INDEX Function within IF Formula


    This will find if any of your conditions are true:

    =IF(OR(ISERROR(VLOOKUP("*AAA*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*BBB*",$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*CCC*",$A2,1,FALSE))=FALSE)=TRUE,$B$1,"No Match")

    Re: Compare Two Columns to Receive a Specific Result


    I can think of two ways to do this.

    1. use IF(AND(C2="whatever",D2="whatever"),10,something else
    you can nest 7 IFs that way

    2. Concatenate the field, then use a lookup to return the value you want
    so, Cell E2 formula is =C2&D2
    and J2 = IF(ISERROR(VLOOKUP(E2,LOOKUP_RANGE,2,FALSE)),"New String",VLOOKUP(E2,LOOKUP_RANGE,2,FALSE))
    Where LOOKUP_RANGE has the combinations you want

    Re: Find row with closest value


    I found one way to do it. It involves sorting the row in descending order, then copying the row and sorting in ascending order.

    My Desc row starts in D16. My Asc row starts in E16. My formula to find the row value is:

    =MATCH(IF(ABS(G16-INDEX(E16:E33,MATCH(G16,E16:E33,1)))<ABS(G16-INDEX(D16:D33,MATCH(G16,D16:D33,-1))),INDEX(E16:E33,MATCH(G16,E16:E33,1)),INDEX(D16:D33,MATCH(G16,D16:D33,-1))),E16:E33,0)+15

    where G16 has your average.

    It is an inelegant solution, so maybe someone else can get you closer...

    Re: Auto Scaling &amp; auto formating major/minor grid lines




    This is getting close to what you want I believe.

    Re: Auto Scaling &amp; auto formating major/minor grid lines


    Where does this get you:



    replacing the [f1] obviously

    Re: Deny save if cell empty


    You will need to put the worksheet name that you want to drive the action in place of Sheet1. You will have to put this in the Worksheet section of the VBE.


    Re: too many vlookups in a formula


    Take a look: forum.ozgrid.com/index.php?attachment/37445/


    I didn't change you vlookups...I just made it so you don't have to update anything. Just add the Rd# sheets and the sheet will auto populate.


    The trick here is to use INDIRECT().


    Also, you could easily add 6 to all your lookups. Don't do it in the cell, do it via a reference. That is what I did for your formulas in this workbook. I didn't have to, but I figured it would be illustrative..

    Re: Formula to deduct value from one cell and add to a different one


    B3
    =IF(AND(Details!A2="Apples",Details!B2="A",Details!D2="ordered"),0,-Details!C2)


    E3
    =IF(AND(Details!A2="Apples",Details!B2="A",Details!D2="Ordered"),Details!C2,-B3)


    You could substitute "apples for whatever you want, like a link a cell that has a dynamic value.


    You can also add more tests to the AND().

    Re: Copying New First/Last names from Roster and Inserting into New Rows in diff shee


    application.screenupdating = false
    application.screenupdating = true


    The 0s and 1s are for determining if a value is in the list. for the delete list, it determines if the value is in the imported data. for the addNew, it determines if the the entry is already in the caseload sheet.


    The formatting is in reference to your desire to format the rows afterwards. You can record a macro to do it, and copy down any formulas that you have.


    I used the offset function so that when you delete a row, you don't create a reference error in the mechanics sheet.

    Re: Logic Process


    I added a sheet called mechanics that will do the sorting for you. I have written the code to compare the old and delete and then add the new:



    So, just run compareAndContrast and it will do the rest.


    I ran out of steam for the format...I'll let someone else tackle that


    forum.ozgrid.com/index.php?attachment/37420/


    I had to zip it...I added an arbitrary number of comparative rows, you can adjust if you want it bigger/smaller...

    Re: Checkbox activating sub function


    OK, I have changed your code somewhat. The problem was with how you were using the form. While segregating code is a good idea, skipping from the form to the modules, etc and using the same variable names defined several times was causing the form to think that one of your check boxes was always on, and the other was always off, because FEthernet was used for both boxes.


    Also, I changed the directory structure so I could use it on my computer. You will need to change it back in your worksheet.


    Here is the code:


    Sheet12 private sub:

    Code
    Private Sub BtnCreate_Click()
       Call Generate
       
    End Sub


    frmstart code:


    principalmod code:


    FastEthernetmod was redundant at this point, so I deleted it.


    forum.ozgrid.com/index.php?attachment/37418/ -- here is the workbook as well. Don't forget to change the directories back.


    In summary, when using a button on a form, drive the actions within the button code. Don't reference back to a module that also uses the same variable.


    Hope this helped. Sorry if I inadvertently deleted some functionality. You can add it back, just do it in line with the form.

    Re: Get a cell value based on multiple criteria


    Steps:

    1. Make a pivot table of the Source data. Include all the rows you want, just de-select blank in the drop down later.
    2. Use GETPIVOTDATA for the rest:

    Original formula:
    =GETPIVOTDATA("Sum of Product",$A$3,"Company","Astrix"," Service"," Posting ")

    Formula that is driven by Customer Name:

    =GETPIVOTDATA("Sum of Product",$A$3,"Company",B3," Service"," Posting ")

    The GETPIVOTDATA formula can be manipulated a number of ways. So, if you don't want posting, just change "posting" to the service reference: you are done. Go have a beer and tell the boss you worked on it all night.