UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create A If Statement With Text In Cell, Office 2013    
 
   
halefamily104
post Apr 13 2020, 01:09 PM
Post#1



Posts: 544
Joined: 7-November 10



Hello UA!

I have a formula that I am trying to figure out why its returning a #Value! error. Here is the formula

[code][/code]
=IF(D5=Summary!$C$27,(Summary!$C$27*$D$27),"")


D5 can be text or numeric, for example D5 could be FULL or 0.05. Any ideas on how I can get this formula to work?

Thanks!!




Go to the top of the page
 
Doug Steele
post Apr 13 2020, 01:33 PM
Post#2


UtterAccess VIP
Posts: 22,280
Joined: 8-January 07
From: St. Catharines, ON (Canada)


If D5 is text, and implies that Summary!$C$27 can be text as well, which means that Summary!$C$27*$D$27 is invalid, since you can't use text in multiplication.

Can you provide some sample data, and explain in words what you're trying to do?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
halefamily104
post Apr 13 2020, 02:19 PM
Post#3



Posts: 544
Joined: 7-November 10



Hi Doug Steele!

Thanks for the response! I have attached a sample of my entire problem. I was piecing it together to figure it out but I got stuck on the first part!

Please see attached that has more information.

Thanks


Attached File(s)
Attached File  Sample_Sheet.zip ( 7.75K )Number of downloads: 4
 
Go to the top of the page
 
Doug Steele
post Apr 13 2020, 03:50 PM
Post#4


UtterAccess VIP
Posts: 22,280
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Take a look at the VLOOKUP function: it should do exactly what you're looking for.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
halefamily104
post Apr 13 2020, 04:52 PM
Post#5



Posts: 544
Joined: 7-November 10



ok, thanks! I will look at it.
Go to the top of the page
 
halefamily104
post Apr 14 2020, 03:57 PM
Post#6



Posts: 544
Joined: 7-November 10



Hello UA!

I have reviewed the vlookup topics and cant see how to make my formula work (due to my novice understanding), so, back to my original plan.

I have come up with this formula:
[code][/code]

=IF(AND(B5="VB-V",C5="Int"),Summary!D19,"")
=IF(AND(B5="VB-V",C5="Sta"),Summary!E19,"")
=IF(AND(B5="VB-V",C5="Mai"),Summary!F19,"")

=IF(AND(B5="NJ-V",C5="Int"),Summary!D20,"")
=IF(AND(B5="NJ-V",C5="Sta"),Summary!E20,"")
=IF(AND(B5="NJ-V",C5="Mai"),Summary!F20,"")

=IF(AND(B5="VB-S",C5="Int"),Summary!D21,"")
=IF(AND(B5="VB-S",C5="Sta"),Summary!E21,"")
=IF(AND(B5="VB-S",C5="Mai"),Summary!F21,"")

=IF(AND(B5="NJ-S",C5="Int"),Summary!D22,"")
=IF(AND(B5="NJ-S",C5="Sta"),Summary!E22,"")
=IF(AND(B5="NJ-S",C5="Mai"),Summary!F22,"")

Now I need to combine this. Any ideas how? Some type of If/and/or combo?

Thanks in advance!


Go to the top of the page
 
Doug Steele
post Apr 14 2020, 04:15 PM
Post#7


UtterAccess VIP
Posts: 22,280
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You should be able to simply nest them.

=IF(AND(B5="VB-V",C5="Int"),Summary!D19,IF(AND(B5="VB-V",C5="Sta"),Summary!E19,IF(AND(B5="VB-V",C5="Mai"),Summary!F19,"")))


--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
halefamily104
post Apr 14 2020, 04:15 PM
Post#8



Posts: 544
Joined: 7-November 10



UPDATE: I was able to get each of these formula's to work separately:

=IF(AND(B6="VB-V",C6="Int"),Summary!D19, IF(AND(B6="VB-V",C6="Sta"),Summary!E19, IF(AND(B6="VB-V",C6="Mai"),Summary!F19,"")))
=IF(AND(B6="NJ-V",C6="Int"),Summary!D20, IF(AND(B6="NJ-V",C6="Sta"),Summary!E20, IF(AND(B6="NJ-V",C6="Mai"),Summary!F20,"")))
=IF(AND(B6="VB-S",C6="Int"),Summary!D21, IF(AND(B6="VB-S",C6="Sta"),Summary!E21, IF(AND(B6="VB-S",C6="Mai"),Summary!F21,"")))
=IF(AND(B6="NJ-S",C6="Int"),Summary!D22, IF(AND(B6="NJ-S",C6="Sta"),Summary!E22, IF(AND(B6="NJ-S",C6="Mai"),Summary!F22,"")))

How can I combine all four lines?
Go to the top of the page
 
halefamily104
post Apr 14 2020, 04:17 PM
Post#9



Posts: 544
Joined: 7-November 10



Sorry, I was submitting my update and didn't see your suggestion until afterwards! Great! so I am going in the right direction (surprisingly..lol). Now to combine them all?
Go to the top of the page
 
halefamily104
post Apr 14 2020, 07:27 PM
Post#10



Posts: 544
Joined: 7-November 10



Any ideas on how to combine all formulas efficiently?
Go to the top of the page
 
halefamily104
post Apr 20 2020, 10:33 AM
Post#11



Posts: 544
Joined: 7-November 10



Topic closed. Thanks for the review everyone!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 10:21 AM