# Posts by EgoProwler

• ## Data Validation - Formula Trouble

Re: Data Validation - Formula Trouble

Quote from norie

EgoProwler

In your first post you mentioned that only whole numbers should be allowed.

Is that not applicable now?

Yes, no longer applies. Sorry for the confusion

• ## 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

Re: Data Validation - Formula Trouble

Quote from norie

Then again I may be wrong.:)

I'm not sure if you can either, but I figured I'd give someone here on the answer. If it can't be done by Data Validation what would the formula be?

• ## 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

• ## Cell References - Absolute

Re: Cell References - Absolute

Worked ABSOLUTELY perfect! Thanks a ton, Batman! You da man!

• ## 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

Been messing around with OFFSET to see if I this might work in this particular case. Not having much luck -- am I way off base here trying to use OFFSET?

• ## 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

Re: Table Lookup - 2 Input Values

You, sir, are a god! Thank you SOOO much. This is exactly why I love this forum

• ## 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.