Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

IF Statement Worksheet

Hi All

I am new to this site and I need help on a couple of things in regards to Excel 2007. Your help would be very much appreciated.

I need to create an IF statement to show if there is enough inventory in stock.
In the can supply cell I need to insert an IF Statement that tests if the order is greater than or equal to the quantity then it states "not enough on hand" otherise it states "Enough in Stock".

On Hand Ordered Can Supply
40 25

Thank you.
Raylene


Post your answer or comment

comments powered by Disqus
i have a master worksheet which has a 3 dropdown lists i want use the if statement to copy data to relevent worksheets

I am trying to insert an If statement using the

	VB:
	
 Sheets("Sheet2").Select 
    Range("c" & rBegRow).Select 
    Application.CutCopyMode = False 
    ActiveCell.FormulaR1C1 = _ 
    "=IF(Sheet1!R[-8]C[4]0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)" 
    Range("C" & rBegRow).Select 
    Selection.Copy 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.Paste 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically.

the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly.

If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G90,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.

I have attached a sample to this Thank youu

I have a workbook with 2 worksheets...tab1 and tab2. On tab1 I have a current control number and original names assigned. On tab 2 I have control numbers and current names assigned.
Example:
tab1 control number orig names
2345 abcd (text)
tab2 control number new names
2345 defg (text)

I need to lookup 2345 from tab 1 and when it finds 2345 on tab 2, I need to have it POST/Return "defg" ON TAB1 (from tab2).

I think its an IF statement and a Vlookup but I cant get any combination to work. Please no VBA...I am not able to use that.
Thanks so much!!

hi,

I need some help creatng an 'IF' statement that contains a
'VLOOKUP' . In Sheet 1 i have an empty column called ROC (Column F)
that should contain the 'IF/VLOOKUP' function. This should look up
Column B in Sheet 2 and try to match them with entries in Sheet
1:Column D(but only the first four digits of the entries in Sheet
2:Column B. In Sheet 2:Column B row 58 the entry is 5730P; in Sheet 1
Column D, Row 18 the entry is 5730. The 'VLOOKUP' function needs to
identify that the first 4 digits are similar, then output the value of
Sheet 2 Column 'A' row 58 in the Column F Row 18.

if anyone has knowledge of if statement with vlookups please get back
to me.

Thank you,
Nakli

To get more of an idea on my question; My question is similar to the
thread below:

Someone else's Question/Answer relating to vlookups::

Afternoon,

I know some one out there might be able to assist me with my dilemna.

In Sheet1 I have a list of 8 digts id numbers in column A and in
sheet2
is where I am doing the lookup from.

The formula below is working great. I picked up from this website.

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number",VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE) )

What I need to know if possible is, can another (vlookup or if ) be
added to the ending formula to do search on just the first 4 number
of
the id, if I got the response Invalid Number.

I currently have another column doing the lookup on just the 4
digits.

Thanks

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 4 2006, 1:33 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Fri, 4 Aug 2006 10:33:02 -0700
Local: Fri, Aug 4 2006 1:33 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
You might try something like this.........

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number"&", Four-digit lookup =
"&YourFourDigitLookpuFormula,VLOOKUP(A6,Schedule!$ A$13:$E
$1463,3,FALSE))

Vaya con Dios,
Chuck, CABGx3

- Hide quoted text -
- Show quoted text -

"Sat3902" wrote:

> Afternoon,

> I know some one out there might be able to assist me with my dilemna.

> In Sheet1 I have a list of 8 digts id numbers in column A and in sheet2
> is where I am doing the lookup from.

> The formula below is working great. I picked up from this website.

> =IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
> Number",VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE) )

> What I need to know if possible is, can another (vlookup or if ) be
> added to the ending formula to do search on just the first 4 number of
> the id, if I got the response Invalid Number.

> I currently have another column doing the lookup on just the 4 digits.

> Thanks

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 7 2006, 1:56 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Mon, 7 Aug 2006 13:56:46 -0400
Local: Mon, Aug 7 2006 1:56 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Gracias Chuck,

Thank you for the suggestion, however it did not work for me. I still
got a return of "*Invalid Number*". I do have the 4 digit in the
Sheet1
where I am doing the lookup from.

I am hoping I followed example

Here is what I entered.

=IF(ISNA(VLOOKUP(A7,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number"&"",(VLOOKUP(LEFT(A7,4),Schedule!$A$13:$E$1 463,3,FALSE)))

The last part of the arguement does work correctly. I currently using
it when doing a vlookup on just the 4 digit on a seperate column.

I am doing a vlookup on my 8 digit user ID and when I do not get a
match I then what do a vlookup on the first 4 digits only.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 7 2006, 3:26 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Mon, 7 Aug 2006 12:26:02 -0700
Local: Mon, Aug 7 2006 3:26 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
If you are wanting to look up the LEFT 4 digits of a cell, then you
will have
to build that in to your VLOOKUP table, or another one.....unless you
have
both cells containing just those 4 digits AND other cells containg the
entire
number.....VLOOKUP cannot extract the left 4 digits out af a number in
the
table.....just add a column on the left side of the table....assume
you
insert a new column A and the old column A is now B and the table
extends to
F now, then try

=IF(ISNA(VLOOKUP(A6,Schedule!$B$13:$F$1463,3,FALSE )),"Invalid
Number"&",
"&vlookup(left(A6,4),$A$13:$F$1463,3,false),VLOOKU P(A6,Schedule!$B
$13:$F$14*63,3,FALSE))

hth
Vaya con Dios,
Chuck, CABGx3

- Hide quoted text -
- Show quoted text -

"Sat3902" wrote:

> Gracias Chuck,

> Thank you for the suggestion, however it did not work for me. I still
> got a return of "*Invalid Number*". I do have the 4 digit in the Sheet1
> where I am doing the lookup from.

> I am hoping I followed example

> Here is what I entered.

> =IF(ISNA(VLOOKUP(A7,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
> Number"&"",(VLOOKUP(LEFT(A7,4),Schedule!$A$13:$E$1 463,3,FALSE)))

> The last part of the arguement does work correctly. I currently using
> it when doing a vlookup on just the 4 digit on a seperate column.

> I am doing a vlookup on my 8 digit user ID and when I do not get a
> match I then what do a vlookup on the first 4 digits only.

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 7 2006, 6:34 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Mon, 7 Aug 2006 18:34:31 -0400
Local: Mon, Aug 7 2006 6:34 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Evening Chuck

I am going to try your suggestion. Just want to mention to you that
the VLoop can strip the LEFT 4 digits. I am currently using this
arguement

=IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439 ,3,FALSE)),"Invalid
Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439, 3,FALSE)) on the
same
work sheet. I am just trying to eliminate from having a lot of
columns
with formulas which slow up the workbook when saving updates or
making
change to it.

I do have the 8 digits and 4 digits in the same column but when doing
the vlookup I have 2 columns one for the 8 digits and the other for
the
4 digit.

The end results is being populated to another worksheet.

Gracias

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 7 2006, 6:41 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Mon, 7 Aug 2006 18:41:38 -0400
Local: Mon, Aug 7 2006 6:41 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Sat3902 Wrote:

- Hide quoted text -
- Show quoted text -

> Evening Chuck

> I am going to try your suggestion. Just want to mention to you that
> the VLoop can strip the LEFT 4 digits. I am currently using this
> arguement

> =IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439 ,3,FALSE)),"Invalid
> Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439, 3,FALSE)) on the same
> work sheet. I am just trying to eliminate from having a lot of columns
> with formulas which slow up the workbook when saving updates or making
> change to it.

> The database from where I am doing the lookup from does have the 8
> digits and 4 digits in the same column. But the work sheet that I
> import the data needing to be matched up, I have 2 columns one for the
> 8 digits and the other for the 4 digit to do the Vlookup.

> The end results is being populated to another worksheet. I hope I am
> not confusing you with what I am needing.
> Gracias

Have a good day

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 8 2006, 8:46 am

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Tue, 8 Aug 2006 05:46:13 -0700
Local: Tues, Aug 8 2006 8:46 am
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Well then, if you have both the 4 digit and 8 digit numbers in column
A then
this will probably work.....

=IF(ISNA(VLOOKUP(A6,Schedule!$A$13:$E$1463,3,FALSE )),"Invalid
Number,
"&vlookup(left(A6,4),$A$12:$E$1439,3,false),VLOOKU P(A6,Schedule!$A
$13:$E$14*63,3,FALSE))

Vaya con Dios,
Chuck, CABGx3

- Hide quoted text -
- Show quoted text -

"Sat3902" wrote:

> Evening Chuck

> I am going to try your suggestion. Just want to mention to you that
> the VLoop can strip the LEFT 4 digits. I am currently using this
> arguement

> =IF(ISNA(VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439 ,3,FALSE)),"Invalid
> Number",VLOOKUP(LEFT(A7,4),Schedule!$A$12:$E$1439, 3,FALSE)) on the same
> work sheet. I am just trying to eliminate from having a lot of columns
> with formulas which slow up the workbook when saving updates or making
> change to it.

> I do have the 8 digits and 4 digits in the same column but when doing
> the vlookup I have 2 columns one for the 8 digits and the other for the
> 4 digit.

> The end results is being populated to another worksheet.

> Gracias

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.

It worked when I got a match on the 8 digit but did not do the
vlookup
on the 4 digit that is when I got the *#N/A*

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.
It worked when I got a match.

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:22 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:22:54 -0400
Local: Tues, Aug 8 2006 4:22 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.
It worked when I got a match.

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 8 2006, 4:25 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Tue, 8 Aug 2006 16:25:14 -0400
Local: Tues, Aug 8 2006 4:25 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Chuck

Thank you for your time and patients. I tried your suggestion from
this
morning. It only work the first part of the formula.

Here is what I entered
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

Got a return value of #N/A when I did not get a match on the 8 digit.

It worked when I got a match on the 8 digit but did not do the
vlookup
on the 4 digit that is when I got the *#N/A*

Was I suppose to space the last letter of Vlookup at the last Vlookup
argument. I followed your example. It did the same without the same.

Again Thank your assistance Please don't stop.
Unless it can not be done.

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 8 2006, 8:03 pm

Newsgroups: microsoft.public.excel.newusers
From: "CLR" >
Date: Tue, 8 Aug 2006 20:03:34 -0400
Local: Tues, Aug 8 2006 8:03 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
I'm ready for bed now and cannot do any more tonight........all I can
see
off the bat is that you do not have the "Sheet2! reference on the
middle
part of the formula.........you might try this.........
=IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid number,
"&VLOOKUP(LEFT(A16,4),Sheet2!$A$2:$C$20882,3,FALSE ),VLOOKUP(A16,Sheet2!
$A$2*:
$B$20882,2,FALSE))

Otherwise, maybe you might send me a copy of your file
to .......croberts
at tampabay dot rr dot com.......and I'll take a look
tomorrow........sorry,
I'm out of gas tonight, but hang in there....we'll whip this thing.

Vaya con Dios,
Chuck, CABGx3

"Sat3902" > wrote
in
message ...

- Hide quoted text -
- Show quoted text -

> Chuck

> Thank you for your time and patients. I tried your suggestion from this
> morning. It only work the first part of the formula.

> Here is what I entered
> =IF(ISNA(VLOOKUP(A16,Sheet2!$A$2:$B$20882,2,FALSE) ),"invalid
> number,"&VLOOKUP(LEFT(A16,4),$A$2:$C$20882,2,FALSE ),VLOOKU
> P(A16,Sheet2!$A$2:$B$20882,2,FALSE))

> Got a return value of #N/A when I did not get a match on the 8 digit.

> It worked when I got a match on the 8 digit but did not do the vlookup
> on the 4 digit that is when I got the *#N/A*

> Was I suppose to space the last letter of Vlookup at the last Vlookup
> argument. I followed your example. It did the same without the same.

> Again Thank your assistance Please don't stop.
> Unless it can not be done.

> --
> Sat3902
> ------------------------------------------------------------------------
> Sat3902's Profile:

http://www.excelforum.com/member.php...o&userid=36777

- Hide quoted text -
- Show quoted text -

> View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

Sat3902 View profile
More options Aug 11 2006, 12:07 pm

Newsgroups: microsoft.public.excel.newusers
From: Sat3902 >
Date: Fri, 11 Aug 2006 12:07:30 -0400
Local: Fri, Aug 11 2006 12:07 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
Happy days are here again.

Got it to work, with help from a friend and your assistance.

I just needed to add in the ( Left formula ). So if I do not get a
match on my 8 digit number then it will match on the first 4 digit
next.

Here is the formula.

=IF(ISNA(VLOOKUP(A2,'Sheet2'!$A$2:$B
$3950,2,FALSE)),VLOOKUP(LEFT(A2,4),'She*et2'!$A$2: $B$3950,2,FALSE),
(VLOOKUP(A2,'Sheet2'!$A$2:$B$3950,2,FALSE)))

The only draw back about this is, I getting the #N/A value
when I don't get a hit. I can not seem to find the right mix to just
get a blank value if there is not match. I welcome your
input.

Gracias por todo
Senor Chuck

--
Sat3902
------------------------------------------------------------------------
Sat3902's Profile: http://www.excelforum.com/member.php...o&userid=36777
View this thread: http://www.excelforum.com/showthread...hreadid=568417

Reply Reply to author Forward Rate this post: Text
for clearing space

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before
posting.
You do not have the permission required to post.

CLR View profile
More options Aug 11 2006, 12:54 pm

Newsgroups: microsoft.public.excel.newusers
From: CLR >
Date: Fri, 11 Aug 2006 09:54:02 -0700
Local: Fri, Aug 11 2006 12:54 pm
Subject: IF & Vlookup
Reply to author | Forward | Print | Individual message | Show original
| Report this message | Find messages by this author
This is pretty messy, but you might give it a try.......it should
return the
value in the first table if it's there, otherwise return from the
second
table, if there....and if it's in neither, then return blank.........

=IF(AND(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B
$3950,2,FALSE)),ISNA(VLOOKUP(LEFT(A2,*4),Sheet2!$A $2:$B
$3950,2,FALSE))),"",IF(ISNA(VLOOKUP(A2,Sheet2!$A$2 :$B$3950*,
2,FALSE)),VLOOKUP(LEFT(A2,4),Sheet2!$A$2:$B$3950,2 ,FALSE),
(VLOOKUP(A2,Shee*t2!$A$2:$B$3950,2,FALSE))))

Locked as unwrapped Code.

what I am trying to do is write a series of If statements to filter data to various worksheets if a conditionis met. I am sure this is pretty easy but my VB skills are awful and I have been butting my head up against a wall for days now trying to figure this out.
For example, if cell one is equal to JC than the data in that row needs to be moved to tab G299. Otherwise if the data in the cell is equal to OK than the data would be moved to tab G298.
The way I figured it would look is below but its so far off base. anyhelp would be greatly appreciated.

Sub datafilter()
'purpose is to filter data out to appropriate worksheet so that a file may be created
'for each ID.
 
Dim G298 As String
Dim G299 As String
Dim G695 As String
Dim G696 As String
Dim G699 As String
Dim AG As String
Dim OK As String
Dim NA As String
Dim GA As String
Dim JC As String

'Worksheets("FORMAT").Activate

If Worksheets("FORMAT").Cells(a, 1) = AG Then
    Worksheets("G699").Cells(a, 1) = Worksheets("FORMAT").Cells(a, 1)
    
ElseIf Worksheets("FORMAT").Cells(a, 1) = OK Then
    Worksheets("G298").Cells(a, 1) = Worksheets("FORMAT").Cells(a, 1)
    

ElseIf Worksheets("FORMAT").Cells(a, 1) = NA Then
    Worksheets("G695").Cells(a, 1) = Worksheets("FORMAT").Cells(a, 1)
    
ElseIf Worksheets("FORMAT").Cells(a, 1) = GA Then
    Worksheets("G696").Cells(a, 1) = Worksheets("FORMAT").Cells(a, 1)

ElseIf Worksheets("FORMAT").Cells(a, 1) = JC Then
    Worksheets("G299").Cells(a, 1) = Worksheets("FORMAT").Cells(a, 1)
   
    
End If
Thanks in advance

I have a worksheet that I want to perform an if statement on. Cell N
contains an amount. In cell O, P, Q, & R, I have dollar values keyed (all in
row 2). What I want to do is say, if the amount in Cell N is greater then O,
and cell N is greater than P, etc. until it tests each of the assigned cells.
I want to have the result go into cell S!

Example: Cell N = $17,000.00. Cell O = $2000, Cell P = 5,000, with the
final Cell R= $8000. Since 17000 > 8000, the result (difference) should be
$2927.48 in Cell S!
I'm sure I'm making this tougher than it really is!! Thanks, in advance,
for your assistance.

if i want to select date from a separate worksheet in an if statement how would I do this -

for example,

if a1=1 and b1=1 in worksheet data then return xyz?

I am trying to write an IF statement that would pull info from 20 different worksheets in the same workbook onto a consoldiated worksheet. For example "MH1" is the start of the first worksheet and "WAR2a" is the end of the last worksheet and there is 18 different worksheets between them. They are all laid out the same. The formula below is what I am trying to do.

I need to take what is in cell C10 in all 20 worksheets and if it is less than or equal to 27 then I want to take whats in cel E10 in all 20 worksheets if not then I want zero for the ones that are not less than or equal to 27.

=IF(MH1:WAR2a!C10<=27,MH1:WAR2a!E10,0)

Thanks,

I have an IF statement that works just fine, but I now need to have that function open a separate worksheet if it returns a "true" result to allow the user to enter more detailed data on that separate worksheet. Any ideas? Thanks.

Hi All,

I have a Workbook (don't we all if we're looking for help here), with a
worksheet at the end that I have several command buttons on. The first
command button will insert a new worksheet that uses a variable as part of
the naming (i.e. Rename to "Formula Sheet #" & <variable>) and inserts the
sheet before the "command button sheet." It is working successfully as far
as inserting and naming goes. I have the varible declared in the Public area
so I can use it for the rest of the buttons.

The rest of the buttons have a macro attached to them with an IF statement.
They are supposed to activate Formula Sheet #<variable> and using the IF at
this point select a range based on the criteria. The code for the whole
thing is as follows

Worksheets("Formula Sheet #" & FormSheet).Activate
If LeftRight / 2 = FormatNumber(LeftRight / 2, 0) Then
FirstRow = Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row
FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column
Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
Else
FirstRow = Cells(FirstRow, FirstCol).End(xlUp).Offset(2, 0).Row
FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Offset(0,
4).Column
Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
End If

The IF portion determines where the range will be selected based on whether
or not LeftRight is even/odd. Basically the "odd" time any of the "range
selection buttons" are used the group of cells to the far left will be
selected and merged where an even LeftRight will select the range a couple of
cells to the right of the previous range and merge it. The problem I am
having is that if I have 'Worksheets("Formula Sheet #" & FormSheet).Activate'
statement in there I get an error. I've tried putting it in various
locations and still get the same error. If I leave it out, the Ranges on the
command button sheet get selected and merged in the same manner as I want the
ranges on the Formula Sheet to be selected and merged.

Also, I have tried moving the actual Range Selection statement to a location
outside of the IF Then but get a 400 error. If anyone has any ideas on how
to activate the formula sheet and select the ranges based on the LeftRight
idea please let me know. All the variable are public so they can be used
with any of the command buttons.

Thanks,

Chaz

How do i return to results of an Excel IF statement to a different worksheet

I am working on an If statement that shows a "yes" or "no" depending if it is <= or >= to one percent. This is what I have and it works fine.

=IF('Funding'!$G3<=1%,"yes",IF('Funding'!$G3>=1%,"no!"))

The problem is that column G is just the range of numbers, and it must reference another column that has a list. For example, "45-019" would be in the current worksheet as one instance(d3) but this number can be repeated again say in cell D8. Another worksheet has all of the list of numbers in column A that will be used.
Is it even possible to include the list from the other worksheet for each instance on the worksheet with the formulas and still keep the above statement? If so, can someone please help?

I have designed a worksheet that tabulates all revenue and expenses for each truck in my small fleet over an entire year.

I use the IF formula to identify a date in the first column (A) to return a blank in the other columns to keep the worksheet clear of 0’s or #DIV/0! for cells to be used later in the year. This keeps the entire worksheet clear of any data until a date is entered in column A.

On a separate worksheet designed to tabulate the combined numbers for each of the 30+ trucks in my fleet, the IF formula no longer works in the manner I want.

Here is an example of the IF statement used on each of the worksheets in the group…
=IF(A10>40500,D10-E10," ") I have used similar statements in each column to keep the worksheet clean.

This is the formula I attempted to use to tally these cells into a comprehensive worksheet total.
=IF(A9>40500,('405:410'!F10)," ")

Excel returns the following formula in the formula bar… =IF(A9>40500,('405:[410]410'!F10)," ")
And receive … #REF! in the cell where the formula was written.

I presently have the final worksheet set up to sum these figures BUT the worksheet is filled with 0’s and #DIV/0! when the sum formula is extended to the bottom of the worksheet.

Does anyone have a solution that would keep the final worksheet clean until a date is entered?

Hello!

I have an excel workbook with a sheet which is basically a financial summary. The first worksheet lays out information for (let's say cars)
(SHEET 1)

A | B
1 | Number |
2 | Descrip |
3 | Serial |
4 | Purchase Price |

You can see that column A is a fixed column. What I would like is for column B to be populated based on a drop down list.
I have let's say, cell C1 tied to a data validation list of all the possible unique identifier numbers. Example:

(SHEET 2)
This is the detail sheet. Cell C1 on the previous sheet can only use the column below marked "Unique Identifier":
Unique Identifier Description Serial Purchase Price
B1001 Ford F150 abc124 20k
B1002 Ford F250 abc987 30k
B1003 Ford F350 abc876 40k
B1004 Ford 5250 Diesel abc568 41k

As an example, on SHEET 1, if in the drop down in C1 that's tied to the unique identifers on sheet 2, I select "B1001"
then SHEET1 would populate column B to look like this:

A | B
1 | Number | abc124
2 | Descrip | Ford F150
3 | Serial | abc124
4 | Purchase Price | 20k

This is purely in EXCEL so I'm not wanting to use VBA for it, but I can't seem to make the IF statement work (cause I'm doing it wrong apparently)

In cell B4 of the first sheet (example above) I would put:
=IF(C1=SHEET2!$A$1:$A$4, SHEET2!$D$1:$D$4, "none available")

essentially I want it to look at the number I pull out of the drop down, and then find that number on the other sheet, and pull the value for the row I want. Any assistance would be greatly appreciated.

Hi guys,

I've written an if statement in the fifth column of a spreadsheet that looks at the four previous columns sequentially (I'll call them 4,3,2 and 1, the order in which they are used in my formula). It simply looks for a value in 4, and if not in 4, then 3, if not in 3, then 2, if not in 2, then 1. When it finds the first value it is looking for, it stops, and simply places that value in column 5. I know the if statement is sound in a technical sense, because if there is a qualifying value in column 4, (i.e. the first column it looks at) it returns that value to column 5 successfully.

My issue is that these four columns are drawing from other worksheets within my spreadsheet, using Vlookup. Vlookup returns an #N/A error when it doesn't find a value in a specific spreadsheet, causing my if statement not to work.

If the value that my formula seeks is in column 3, for example, my formula gets tripped up by the #N/A error in #4 (which I fully expect, given that the value it seeks is not present), not allowing it to return a result.

My Question: Can I somehow manipulate the #N/A results that I get in new, adjoining columns to anything other than #N/A, so my formula can actually work and simply be told not to look for that result? Is there another method to avoiding or changing the #N/A?

Hopefully this is clear.

Thanks for the response.
What I need to do, is declare each level (S1, S2, S3….etc) to their encounter (the last worksheet)

S1 1-2-3-4
S2 5-6-7-8
S3 9-10-11-12
W1 13-14-15-16
W2 17-18-19-20
W3 21-22-23-24
UPW1 25-26-27-28
UPW2 29-30-31-32
UPW3 33-34-35-36
T1 37-38-39-40
T2 41-42-43-44
T3 45-46-47-48
MS1 49-50-51-52
MS2 53-54-55-56
MS3 57-58-59-60
M 61-62-63-64
M 65-66-67-68

The idea is that when the teachers insert the start level in the first worksheet “Contracts” in cells “start level and end level” (G2 and H2), some how highlight or depict the encounters that correspond.

I was thinking of IF statements but I am limited to six,
Any ideas?
Thanks a bunch!!
Fernando

Hey Guys,

I've got an impromptu database in a workbook. The information is stored in a worksheet called Database. One of the columns in this sheet is a comments section, however i didn't want the comments being on the same worksheet, so i transferred the comments to another worksheet named Comments.

The problem I'm having now, is I want the user to be able to see if there is a comment for a specified value. If there is a comment in the comments sheet for a certain value, I want the celll in the Database worksheet to notify the user for that corresponding value.

This is the code I have written in the Database cell.

	VB:
	
(Comments!B32=0, HYPERLINK("[NC Table2.2.xls]Comments!B32","Create"), HYPERLINK("[NC
Table2.2.xls]Comments!B32","View")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code works fine for the moment, but when i drag the code down the column, the only thing that changes is the "B32" reference in the logic test of the if statement. The cell references stay in the hyperlink stay the same.
example:

	VB:
	
(Comments!B33=0, HYPERLINK("[NC Table2.2.xls]Comments!B32","Create"), HYPERLINK("[NC
Table2.2.xls]Comments!B32","View")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there anyway I can use a macro, so that i don't have to retype the code for all 1000+ cells?
If you don't understand the problem -might not have been to clear - let me know, and i'll throw up a copy of the sheet.

Hi All,

I am trying to make an if statement on a worksheet and want it to check these things.

1) If cell = "y" and referenced value >0 return "Correct"
2) If cell = "y" and referenced value 0 return "Wrong"
5) If cell = "n" and referenced value 0, "Correct", "Wrong"), IF(LOOKUP(CONCATENATE($B3,F$2), $A$2:$A$3397, $C$2:$C$3397)

I have a worksheet that has 5 rows and 5 columns. The five rows are Highschool subjects. Math, English, French, Phys. Ed, History. The Five columns are Monday, Tuesday, Wednesday, Thursday, Friday. I want to device a worksheet that say for Monday it will have YES for math, no for english, yes for French, yes for phys. ed, no for history. Tuesday would be say no for math, yes for english, no for french, etc, etc. I then want to use an if statement to convert the yes & no entries to a numeric value. I.E I have this right now.

	VB:
	
(B6="YES",1,0) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This simply puts a 1 for a yes and 0 for a no into cell M6. What I want tho is each subject to add the amount of yes's for the week and put it into cell M6. So if math is taken 3 times a week it would put a 3 into cell m6. if History is taken 4 times a week it would put 4 into cell m7, etc, etc. Any Ideas?

I have the following code written for my hide/unhide sheets. I had to write this because anything else i tried didn't work for me. I am having an error on line 1. Can anyone see why?
I've tried various things:
removing "= True"
removing "= xlSheet_____"


	VB:
	
 Worksheet 
If Worksheets("C_Int").Visible = xlSheetVisible = True Then 
    For Each wsSheet In ThisWorkbook.Worksheets 
        wsSheet.Visible = xlSheetVisible 
    Next 
    Worksheets("C_Int").Visible = xlSheetVeryHidden 
ElseIf Worksheets("C_Int").Visible = xlSheetVisible = False Then 
    For Each wsSheet In ThisWorkbook.Worksheets 
        wsSheet.Visible = xlSheetVeryHidden 
    Next 
    Worksheets("C_Int").Visible = xlSheetVisible 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Also, this should work if I shorten the code as follows?


	VB:
	
 Worksheet 
If C_Int.Visible = xlSheetVisible = True Then 
    For Each wsSheet In ThisWorkbook.Worksheets 
        wsSheet.Visible = xlSheetVisible 
    Next 
    C_Int.Visible = xlSheetVeryHidden 
ElseIf C_Int.Visible = xlSheetVisible = False Then 
    For Each wsSheet In ThisWorkbook.Worksheets 
        wsSheet.Visible = xlSheetVeryHidden 
    Next 
    C_Int.Visible = xlSheetVisible 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all,

Having a mental block at the moment...

Its an IF statement with figure that falls into one of the attachment points (below). From this the factor is given and the cell below should show the relevant factor

I.e in a1 = 112 and therefore attachment of 100-124, which means in cell a2=2.778
Attachment Factor
>150 1.000
125 - 149 1.667
100 - 124 2.778
75 - 99 4.630
50 -74 7.716
25 - 49 12.860

I have attached a worksheet with just the table in and a1 filled in.

If you can have a look that would be great, im not so great with between figures!

I have two macros
Each macro imports data from a text file, formats them appropriately (extracting data based on criteria) and placing them in designated columns in Sheet1

The macro do absolutely the same thing, except the format of each (placement of data in each cell) in the text file is a little different.
one macro (AggregateMetrics) works with data prior to 3/2/05
the other (SummaryMetrics)works data prior after 3/2/05.
They both work perfectly.

Except i am trying to merge the macro (instead of two) to work based on criteria with just one text file that contains all the data.
If date is >= 3/2/05...extract this from cells and place here
else
extract this from cells and place here

Or perhaps If InStr(cell, "AGGREGATION METRICS:") > 0 Then ....
and If InStr(cell, "SUMMARY METRICS:") > 0 Then

Whichever is wiser!!

I have attachd what i have so far and it's doesn't pick up the correct data, even thouh they both work perfectly independently. But tryng to combine things here with an IF statement is such a nightmare, and i have spent so muchtime on this already and i am getting no where.

Please help anyway you can with suggestiosn, ideas, anything.
Thanks in advance.

This is what i have been working on:

You may check the dailrpt.zip attachment or each individual macro that works separately with the text.txt file.


	VB:
	
 wkscmd_ExtractData_Click() 
     
     '   Local Variables
    Dim cell As Range, rngOut As Range 
    Dim strDate As String, strTable As String 
    Dim strPreAGG As String, strPostAGG As String, strCompression As String 
    Dim strRenovated As String, strRenopercent As String 
     
     '   Read data
    For Each cell In Me.Range("rdi_TableTop", "A" & Me.Range("A65536").End(xlUp).Row) 
        If ActiveSheet.Name = Me.Name Then cell.Select 
         
         ' Get effective date
        If InStr(cell, "SUMMARY METRICS:") Or InStr(cell, "AGGREGATION METRICS:") > 0 Then 
            If strDate = "" Or strDate  Right(cell, 10) Then strDate = Right(cell, 10) 
            strDate = Format(strDate, "mm/dd/yyyy") 
        End If 
         
        If strDate >= "3/2/05" Then 
             
             ' Get Global House Count:
            If InStr(cell, "GLOBAL HOUSE COUNT:") > 0 Then 
                 'If strPreAGG = "" Or strPreAGG  Trim(Mid(cell, 22, 13)) Then
                strPreAGG = Trim(Mid(cell, 22, 13)) 
                strPostAGG = Trim(Mid(cell, 59, 11)) 
                strCompression = Trim(Right(cell, 4)) 
                strRenovated = Trim(Mid(cell, 38, 12)) 
                strRenopercent = Trim(Mid(cell, 53, 4)) 
            End If 
        Else 
             ' Get Global House Count:
            If InStr(cell, "GLOBAL HOUSE COUNT:") > 0 Then 
                 'If strPreAGG = "" Or strPreAGG  Trim(Mid(cell, 22, 13)) Then
                strPreAGG = Trim(Mid(cell, 24, 13)) 
                strPostAGG = Trim(Mid(cell, 41, 16)) 
                strCompression = Trim(Right(cell, 4)) 
                strRenovated = "" 
                strRenopercent = "" 
            End If 
        End If 
         
        If InStr(cell, "TOTAL") = 0 Then 
            cell.Select 
            If IsNumeric(strPreAGG) And IsNumeric(strPostAGG) Then 
                Set rngOut = Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0) 
                rngOut.Offset(0, 0) = strDate 
                rngOut.Offset(0, 1) = strPreAGG 
                rngOut.Offset(0, 2) = strPostAGG 
                rngOut.Offset(0, 3) = strCompression 
                rngOut.Offset(0, 4) = strRenovated 
                rngOut.Offset(0, 5) = strRenopercent 
            End If 
        End If 
    End If 
Next cell 
 
Sheets("Sheet1").Select 
Sheets("Sheet1").Copy 
Application.DisplayAlerts = False 
ActiveSheet.Name = "DailyReportData" 
ActiveWorkbook.SaveAs ThisWorkbook.PATH & "SummaryMetrics From 3-3-05 To Date" '& Format(Date, "mmmyy")
Application.DisplayAlerts = True 
 
Windows("DailyRpt-Import&ExtractMacro.xls").Activate 'Go back to rawdata workbook
ActiveWorkbook.Close SaveChanges:=False 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When i am done with this. I would like to delete empty rows from each cell, but i can address that later.

Thanks in advance.

Im having a bit or trouble with "IF" statements, what im trying to do is check a worksheet to see is populated with data, If it is then do nothing, if it isnt do "this" this is actually importing data from mysql (not important) i already have most of the macro set out all I want to do is add this "if" check.

thanks for your help

mattyg

I need to know how to do an IF statement for this.

IF Now() > 12:00AM and < 2:00AM Open this worksheet Else Open another sheet.

This is proabaly stupid sounding to you guys but i cannot figure out the Time Property,

Please help. I am almost done.

Thanks

Mike


No luck finding an answer? You could always try Google.