Posts by EgoProwler

  • Data Validation - Formula Trouble

    Re: Data Validation - Formula Trouble


    Excellent! This works perfect! One other question that just came to mind.


    Is there any logic that can be added to address this scenario.


    Flat Paid is data entered into C2 when B2-A2 = 0%. If user changes the buy and/or sell rate to be > 0%, is there a formula that will wipe out the Flat Paid cell (C2)?

  • Data Validation - Formula Trouble

    Re: Data Validation - Formula Trouble


    Quote from carlmack

    I'm a little confused.


    OK, let me try to clear this up. Based on the example attached earlier in the thread:


    IF (B2-A2) = 0 then ALLOW data entry to C2 (Flat Paid)
    IF (B2-A2) > 0 then DISALLOW any data entry to C2 (Flat Paid)


    Sell Rate will ALWAYS be larger than Buy Rate.


    Basically, if the Buy Rate and Sell Rate are EVER the same, you should be ALLOWED to data enter to C2. If the Sum of B2-A2 is EVER > 0, you should be DISALLOWED to data enter to C2.

  • Data Validation - Formula Trouble

    Re: Data Validation - Formula Trouble


    Quote from carlmack

    Try this :


    Allow - Whole Number
    Data - Between
    Minimum - =IF(H1=I1,-9999,0)
    Maximum - =IF(H1=I1,9999,0)


    HTH


    Thanks - doesn't work though :(


    When the difference between the Buy and Sell rate is = 0, there should be no data entry allowed into Flat Paid. If the difference is > 0, data entry is allowed into Flat Paid.

  • Data Validation - Formula Trouble

    Having some trouble with this one.


    K1 needs Data Validation for following.


    IF I1-H1=0 then allow data entry of whole number into K1. IF I1-H1>0 then allow NO data entry.


    I've tried a standard IF statement in a custom Data Validation, but it's not working :(


    Thanks in advance.

  • Cell References - Absolute

    Re: Cell References - Absolute


    Quote from StefanG

    At some point you reference to H$11 (H11) and later H$12 (H12)[quote]


    It should be H11.


    [quote]If you only want to replace H$11 in block two with H$20 and in block three with H$29, I think the only way of adjusting it is to change that reference in the formulas by highlighting the respective block I.e. L20:L26 and 'CTRL-H' (Edit| Replace) and exchange accordingly.


    I know that's an option, but I'm looking for something that might be a formula related solution. Thanks for the feedback.

  • Cell References - Absolute

    Re: Cell References - Absolute


    Thanks for the reply. I should have been more clear. When the Region changes, the Target BTL (I typo'd it as Achieved) will change to match the selected reach.


    Since the BTL target changes, the calculation in colum M will be different for each of the 3 regions. Column L calculations use the value from the INDEX along with column M. The reference to the Target BTL's Region (which is a absolute value) changes every 9 rows. I need to be able to reference every 9th row for each block of calculations.


    For example,


    L11:L17 has to reference H12 in it's calculations
    L20:L26 has to reference H20 in it's calculations
    L29:L25 has to reference H29 in it's calculations


    Ignore the Target BTL cells below the Region, they are there as an illustration and not used to calculate.


    CORRECTED attachment.

  • Cell References - Absolute

    EXAMPLE attached


    I've run into an interesting problem concerning an absolute cell reference that I'm stuck on and not sure if there is even a work around for it.


    H11 is a Data Validation List


    Beginning with L11, an nested IF along with an INDEX statement are used to apply a Target Book to Look to the calculations that are used in Columns L and M based on the data in H11.


    The problem is that starting with L20, in this attached example, It uses the value in H11 and NOT H20. I understand this is because of the absolute cell value, but I need the reference to Region (H11, H20, H29) to copy down properly when I copy/paste down the sheet.


    Hope this makes sense and thanks in advance for the help. Any combination of cell reference formulas I can use to make this easier when I copy down the sheet?

  • Table Lookup - 2 Input Values

    Re: Table Lookup - 2 Input Values


    Quote from Barbarr

    Then it would be a good idea to set Data Validation to Allow Whole Number Between 0 and 800 on B3 and Between 0 and 300 on B4 to prevent errors on the index formula.


    Excellent point. I've already implemented Data Validation in other data entry fields of this sheet and this one will get the same treatement.


    Thanks again guys! I'm sure I'll have more questions here very soon for some other features I need to add to this sheet.

  • Table Lookup - 2 Input Values

    Re: Table Lookup - 2 Input Values


    Quote from Barbarr

    You might consider using data validation on these two cells to restrict to the ranges you indicated. You can also require that the number be a whole number, for example, 10 would be valid but 10.1 would not.


    The input cells in this example will always be a whole number.

  • Table Lookup - 2 Input Values

    I'm having a heck of a time figuring out how to perform this action in Excel. I have some understanding of VLOOKUP, but not enough to really do this properly. I've attached an example sheet so you can see what I'm talking about.


    The two yellow cells, B3 and B4, require user input:


    B3 can be a number from 0 to 800
    B4 can be a number from 0 to 300


    Based on the inputs in these two cells, I need to return the result of the lookup into cell B6 - Loss Experience.


    Thanks in advance for your help.