Posts by GoCavs

    Re: $15 USD: Data Validation formulas to restrict data being entered in a cell


    Wigi...could you help me with one more thing with this file? Hopefully something really simple. Could you adjust the code you gave me to fit my latest file (see attached). There are more columns and I have some existing Worksheet change code in the file (see below) that I would like to still use. Would it be possible to integrate this with the code you provide? The columns I want the Data Validation code to work on now BA and BB.


    Thanks!



    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, Range("c2")) Is Nothing Then Exit Sub Else Range("a6:a35").AutoFilter 1, "<>0", , , 0
    2. End Sub

    I am looking for some Data Validation formulas to prevent users to enter a data in a cell if there is a value present in another cell. I'm not sure if this can be done by formulas or VBA. I attached the file for reference. Here is the criteria:


    Data Validation criteria




    • Do not want users to be able to input data in both Col E and Col F. Only a value allowed in one of the 2 columns per row (Option 1 or Option 2)
    • So if a value is present in Col E and someone tries to input a value in Col F in the same row, I would a like a pop-up message to say "Invalid Entry: Please remove amount from Column F to enter %"
    • So if a value is present in Col F and someone tries to input a value in Col E in the same row, I would a like a pop-up message to say "Invalid Entry: Please remove amount from Column E to enter Amount"


    Thank you!


    10% has been sent via PayPal

    Re: $100 USD: Calculate Monthly Forecast Values


    I just realized the 52 days is for the 2nd line. The correct duration for line 1 that I have is 61 days. For this I used: =EDATE(14-Sep-13, 2) = 14-Nov-13. Then used =DATEDIF(14-Sep-13,14-Nov-13,"D") = 61. Daily Rate then becomes 61/1,000 = 16.39


    [TABLE="class: cms_table_grid, width: 100, align: left"]

    [tr]


    [td]

    Calc Start Date

    [/td]


    [TD="align: center"]Wtd.Value ('000s)[/TD]

    [td]

    Months

    [/td]


    [td]

    Sep-2013

    [/td]


    [td]

    Oct-2013

    [/td]


    [td]

    Nov-2013

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]14-Sep-13[/TD]
    [TD="align: center"]1,000[/TD]

    [td]

    2

    [/td]


    [td]

    262

    [/td]


    [td]

    508

    [/td]


    [td]

    230

    [/td]


    [/tr]


    [tr]


    [/tr]


    [/TABLE]

    I am looking for some formulas that will calculate monthly project forecast totals based on 3 data elements [Start Date, Duration (Months), Weighted $ Value]. The template I have contains rows of project info and contains rolling 12 monthly values across the top with Month 1 being the current Month (e.g. Sep-2013). Would like to have formulas for these monthly columns that would calculate the monthly breakout values based on the 3 data elements. The part I am struggling with is that I would like to apply the amount proportionate to time of the project is contained in each applicable month.


    For example, in the 1st row in the table below, the project started on Sep 14, 2013 and the duration in months is 2. I calculate the duration in days to be 52 daily rate of $19.23 (1,000/52). I want to apply ONLY the portion (16 days out of 30 total for Sep: 16 * 19.23 = 308) applied to the Sep-2013 column. The for Oct-2013 column, apply the entire 31 days of the month (31 * 19.23 = 596). And for Nov-2013 (the remaining 5 days: 5 * 19.23 = 96).



    [TABLE="class: grid, width: 100, align: left"]

    [tr]


    [td]

    Calc Start Date

    [/td]


    [TD="align: center"]Wtd.Value ('000s) [/TD]

    [td]

    Months

    [/td]


    [td]

    Sep-2013

    [/td]


    [td]

    Oct-2013

    [/td]


    [td]

    Nov-2013

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]14-Sep-13[/TD]
    [TD="align: center"]1,000[/TD]

    [td]

    2

    [/td]


    [td]

    308

    [/td]


    [td]

    596

    [/td]


    [td]

    96

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]05-Oct-13[/TD]
    [TD="align: center"]2,000[/TD]

    [td]

    1

    [/td]


    [td]

    0

    [/td]


    [td]

    1,677

    [/td]


    [td]

    323

    [/td]


    [/tr]


    [tr]


    [TD="align: center"]22-Jul-13[/TD]
    [TD="align: center"]500[/TD]

    [td]

    4

    [/td]


    [td]

    122

    [/td]


    [td]

    126

    [/td]


    [td]

    89

    [/td]


    [/tr]


    [/TABLE]










    I attached my file to use for reference. Also, another criteria would be IF there is a NULL value in the Calc Start Date column (Col S), then the monthly values should be set to 0.


    I hope this makes sense. If this can be done with formulas that would be great. But if it has to be done with VB code, that works as well. Feel free to add any helper columns or rows as needed.



    Thank you in advance for your help.

    I have a worksheet that automatically generated that I would like to add another tab that will summarize the totals from it. Please refer to the attached file. The report given to me contains the wanted value (Vesting Shares) a row below the searched variable (ie. Common Interests 0 (Single Trigger)). I would to sum the values of the Vesting Share column (Col E) one row below each Search Variable (Common Interests 0 (Single Trigger), CUPI 0 (Single Trigger), etc.) in Col A. My color shading in the file will help with understanding what I'm looking to do. I will have over 100 of these individual files created and wanted an easy way to summarize each one.


    Let me know if you have any ?'s.


    (pre-payment sent)



    Re: $20 USD: Macro to sort multiple columns with dynamic range


    I tried adding on like this but I keep gettting a "Compile error. Expected List Separator or )" message


    I have a sheet that I would like to add a button that will perform an customized sort on multiple columns. The amount of rows will keep changing, so I was looking for it to be dynamic in that the range would end at the last value in Column D as there will be no blanks in that column. The Header row will be on Row 4 and the data begins on Row 5. The sequence for the sorting will be as follows:


    Column C - Ascending
    Column B - Ascending
    Column G - Decending
    Column K - Decending


    Thank you! Pre-payment already sent.