I am aware of putting the input text, but according to mukesh, if d13 is
grater then k12 then b13 should not accept ay data. In this scenario the
error message that mukesh wants to display is "pls enter the value less then
k12" is sounds illogical to me. Because the data validation condition for
b13 is not decided by the data you are entering in cell b13 but it is
decided by the comparison of 2 other cells d13 and k12. So in order to
accept the data by cell b13 the d13 has to be = to or less then k12. In this
condition b13 can accept any value no matter grater or less then k12. I hope
I was clear enough to explain my argument.
zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala
----- Original Message -----
From: "Zujar Shabbir Kanchwala" <[email protected]>
To: <[email protected]>
Sent: Saturday, November 10, 2012 10:31 PM
Subject: Re: [AI] Excel formula
Mukeshji - Autosum is an Excel feature that helps inserting the SUM()
function in the active cell. It cannot be used in a formula. As far you
need
to implement a formula on cell ranges of different sizes you may select a
larger range while entering the formula for the first time and then copy
it
at various locations. Anyway this method is not foolproof and you may need
to verify the results and rewrite the formula with the correct range where
there are issues. Time consuming and tedious - but that's what comes to my
mind at present.
Zoher - The Data Validation dialog box has three tabs - Validation, Input
Message and Error Alert. You specify the validation on the first tab -
mostly people just use this one and don't explore the others. The Input
Message is displayed near the cell when the cell is highlighted. JAWS
speaks
it well and is useful to hint about what valid input that the cell can
accept. Error Alert is used to specify a custom error message in case the
user enters an invalid value in the cell.
To specify an Input Message, open the Data Validation dialog box, switch
to
the Input Message tab using CTRL+TAB, specify the Heading for the Input
Message and the Message itself and hit the OK button.
--
Regards,
Zujar...
An optimist laughs to forget, whereas a pessimist forgets to laugh!
-----Original Message-----
From: AccessIndia [mailto:[email protected]] On
Behalf
Of Mukesh
Sent: Saturday, November 10, 2012 5:21 PM
To: [email protected]
Subject: Re: [AI] Excel formula
Hello Johar,
Thank you. My first problem is solved. In second problem I have a little
modification as follows because each time the cell renge is different. The
data entries may more or less each time. So I don't want to stick to a
particular cell range. Therefore I want to use auto sum formula so that
the
problem of different cell range will solve. Is it possible to give a auto
sum formula in this case?
Require a formula as follows:
if cell b13 has a text 'Closing balance' the cell c13 should have a auto
sum
formula.
Example: =if(b13="Closing balance",sum())
This formula is giving error. Please correct it.
Thank you.
Regards
Mukesh Baviskar
Mobile: 0943161157
--------------------------------------------------
From: "zoher kheriwala" <[email protected]>
Sent: Saturday, November 10, 2012 12:13 PM
To: <[email protected]>
Subject: Re: [AI] Excel formula
You can achieve your target by following formulas.
Q1: If cell b13 has a text 'Closing balance', The cell k13 should have
a formula 'j13-i13'
Answer: =if(b13="closing balance",j13-i13)
Q 2: If cell b13 has a text 'Closing balance', The cell i13 should
have a formula 'sum(i5:i12)'
Answer: =if(b13="closing balance",sum(i5:i12))
Q: 3 a: If cell d13 is greater than k12, The cell d13 should not
accept the value
Answer: step 1: go to b13
Step 2: press alt+v, v
Step 3: from allow combo box select custom.
Step 4: press tab and type the formula =k12<=d13
It means it will not accept the value if d 13 is > then k12.
step 5: press tab and go to ok button.
Q 3b: show a inpute message 'enter a value less than k12'
Answer: This part is not clear to me, because if the value of d13 is
>then k12, then it will not allow the data to be entered. No matter
what you want to enter in cell b 13. If you want to enter the data,
then you must have the value of d13 = to or < then k12.
zoher kheriwala taheri menswear mumbai. skype id zoher.kheriwala
----- Original Message -----
From: "mukesh baviskar" <[email protected]>
To: "accessindia" <[email protected]>
Sent: Friday, November 09, 2012 10:48 PM
Subject: [AI] Excel formula
Hello Friends,
I want to create a formula for following:
column b13: Closing balance
Column i13: withdrawal
Column j13: deposit
Formula required:
1. If cell b13 has a text 'Closing balance', The cell k13 should have
a formula 'j13-i13'
2, If cell b13 has a text 'Closing balance', The cell i13 should have
a formula 'sum(i5:i12)'
3. If cell d13 is greater than k12, The cell d13 should not accept
the value and show a inpute message 'enter a value less than k12'
Please suggest me a formula to to do this. Thanking you in anticipation.
Regards
Mukesh Baviskar
Mobile: 09403161157
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.or
g.in
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org
.in
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes, please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
