Home |
Search |
Today's Posts |
#1
|
|||
|
|||
limiting cell value
Hi, I have a Foreign currency bill making sheet. In that I would like to sort the following problem ----- I----------------- J -------- ------ K ----------------------- L ----------------- M PURCHASE/SALE------- FCY----------QUANTITY------------------RATE-------------LOCALY CRRNCY in the fourth colomn(L-RATE)) there is vlookup formula that brought the currency rate from the RATE sheet. The formula is =IF(ISNA((VLOOKUP(J2,RATE!$A$1:C$32,IF(I2="P",2,3) ,FALSE))),"",VLOOKUP(J2,RATE!$A$1:C$32,IF(I2="P",2 ,3),FALSE)) Now i wanted to give the rate colomn some control limit by giving either if formula or some conditional formula . ie. If 'I' colomn is P(purchase),'J'colomn is USD, the 'L' colomn gets by vlook formula(above mentioned) as the rate 386. Now i can change the rate to any value, even zero also possible. Here is, my question: how can i prevent that 'L' colomn between 384 and 386 (any rate between that is ok) but cell should not accept less than 384 or more than 386. I know this is bit complicated issue. Still I know there are masters in this group they can make impossibles(what i think) to possible. Any suggestions or ideas are heartly welcome. thanks and regards NOWFAL -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=466200 |
#2
|
|||
|
|||
Does "data validation" not working in your case? Go to menu - Data - Input validation(?) At "settings" tab, select integer as your input type. Set your criteria here. -- hideki ------------------------------------------------------------------------ hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903 View this thread: http://www.excelforum.com/showthread...hreadid=466200 |
#3
|
|||
|
|||
Hi Hideki, Thanks for the reply. But data validation will be difficult b coz there are 32 currencies, how can i give the formula , if J2 is USD then the rate is between 384 and 386. Hope will get some idea near future. by nowfal -- nowfal ------------------------------------------------------------------------ nowfal's Profile: http://www.excelforum.com/member.php...o&userid=10003 View this thread: http://www.excelforum.com/showthread...hreadid=466200 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell color based upon cell value | Excel Discussion (Misc queries) | |||
cell color index comparison | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |