Need help from an Excel guru

[BLachance75]

I need help with an Excel formula.

I'm putting the formula in cell X5. If M5 is <0 and C5 doesn't have a number then I want X5 to be blank. If M5 is <0 and C5 has a number I want X5 to say "ERROR"

Any help is appreciated.

[flyweightnate]

You can always nest "if" statements. If(m5<0, if(c5="","","error")...)
Clunky but effective.

[Grendel]

Here you go this is one way to two logical arguments in an if statement given your sentence description:

=IF(AND( ISNUMBER(C5)=FALSE,M5<0),"Error","")

You can also use IFS function to build this logic and might be what is needed if there are multiple combinations that require multiple outputs. This will work it there are only two answers <blank> and "Error" and only two variables C5 is a number and M5 is less then zero.

If you want more specific help let me know this type of stuff is what I do all day at work most days; build logic for other peoples spreadsheets or at least that is what it seems like most days.

I reread for the umpteenth time your statement and I think I see the error in my formula. I had the C5 logic backward in my head

=IF(AND( M5<0,ISNUMBER(C5)=TRUE),"Error","")

• [*=1]This works out to the logic IF value in M5 is a Negative number and value in C5 is a Number then write "Error" in the cell otherwise leave cell <Blank>
• [*=1]Given this is using a nested AND function in the IF statement both conditions have to be true to get an Error message all other conditions will result in <blank>
• [*=1]Screen capture showing the results from various inputs to Column C and M to illustrate how the logic works

[Paintslinger16]

I use excel for inputs into already generated smart spreadsheets pretty much everyday, I took a really basic course years ago I am amazed at what guys like you can do.

[BLachance75]

I’ll get ahold of you next week when I’m at my work computer and can look at the sheet. I tried what you had and it didn’t work correctly. It will probably be easier to show you the sheet. I may not be explaining it correctly or giving enough info.

[Grendel]

I thought it might not work for what you really wanted to do. I built it based on how you described it but there are some loopholes in the logic so I figured there was something you wanted that was not coming across. One thing is "M5<0" are you talking about a number less the 0 [negative number] or where M5 is blank or not a number? This really matters in the logic, what I have is if M5 literally is less the 0 (a negative number) AND C5 is not a number (which includes blank) X5 will Display "Error" Essentially all other combinations will result in <Blank>. Basically this is a simple AND Gate logic where both parts need to be a logical 1 to get a logical 1 output "error". I expect you are looking for a multiple combinations to result in your "Error". Let me take a look at your spreadsheet when you can I we'll be able to figure something out.

[Siress]

Would you prefer to just disallow erroneous input? That may not be possible here. You can definitely have it color erroneous input directly

[Grendel]

Yup, you could easily utilize Data Validation to disallow input not desired. There are literally hundreds of different ways to do something in Excel. You can even use Lookup and Match along with IFS formulation to provide various insulting dialog for erroneous data entry if you really wanted to get creative

[Siress]

I use it to do matrix operations with logic rather than MATLAB. It's crazy useful for a tool most people have accesa to.

[Spider!]

I think Excel is a great calculator also. Back when Excel was on Apple computers (late 80s) we used it in a lab for finite element analysis. There was a Cray front end available in the next room, but it was easier and cheaper to set up a spreadsheet and go to lunch.

[Siress]

https://www.dropbox.com/s/gwz1pydignvcfw5/Conditional%20data%20validation.xlsx?dl=0

[BLachance75]

I completely forgot to update you guys and thank you. I did get it working but I don't remember the fix off the top of my head.

I do have another question about another formula. I need to add a second condition to an if statement.

The current formula is, =IF(AI13="EAPS",AC13+AG13,AC13)

I want to add in a condition that if AI13="CES" then AC13+AA13

[TheYoda]

I saw the thread title and was getting ready to jump in and help with programming your EXCAL.
You know, paintball forum.
Guess I need to work on my reading and comprehension skills.
Good luck with all your smarty pants formulas.

[Siress]

i completely forgot to update you guys and thank you. I did get it working but i don't remember the fix off the top of my head.

I do have another question about another formula. I need to add a second condition to an if statement.

The current formula is, =if(ai13="eaps",ac13+ag13,ac13)

i want to add in a condition that if ai13="ces" then ac13+aa13

=if(AI13="CES",ac13+aa13,if(ai13="EAPS",ac13+ag13, ac13))

[BLachance75]

Thank you, worked perfectly.

[ford]

If we're using 'if' statements and conditional logic... isn't it time to update from excel to a database?

[Siress]

If we're using 'if' statements and conditional logic... isn't it time to update from excel to a database?

Let me know when database interfaces are as easy to setup, create, modify, and to read/understand an existing one.

[Seajay]

If we're using 'if' statements and conditional logic... isn't it time to update from excel to a database?

Depends on the database Excel can manage a great deal of simple data, numbers and names, and do some pretty sophisticated logic.

[ford]

Depends on the database Excel can manage a great deal of simple data, numbers and names, and do some pretty sophisticated logic.

Sure, the point being there is a productivity curve out there somewhere in the amount of time needed to maintain/retrieve data using excel where things get too complicated and one would be better off performing such operations in a real database.

I empathize that a lot of database concepts aren't as intuitive at first blush as Excel... Maybe some day.

[Siress]

I have a lot to learn about DBs. Just starting up a db has been more complicated than migrating to a new OS. And then it's not as transferable as an excel file. MS Access seems alright but I've never actually found a use case for it. I either go cloud-based or stick with excel.

[Seajay]

This topic reminds me I need to take some Excel courses. Being an excel guru is such a boon.
Also you only need databases when you start working with multiple data sets, otherwise Excel can manage 100,000 rows quite fine, and is more flexible dealing with details. A database is really useful when you want to query a lot of data sets over a long period of time, but is much harder to use working with specific data manipulation.

[BLachance75]

Almighty gurus I need your help again.

I'm trying to create a macro to create and rename a file with the date at the end. We used to have a macro that did this along with a bunch of other stuff but I'm trying to trim it down because most of the other information that the macro gathered is obsolete. From going through the old macro below is what I pulled out that did what I want except it isn't working for me.

I want the macro to open a file called "A1 checkpoint master", go to the tab called "A1", change the date in cell H1 to the current date, save the master sheet with the current date, save a copie as "A1 mm/dd/yy", "A1 mm/dd/yy +1" and "A1 mm/dd/yy +2"

Right now the macro will open the file and go to the "A1" tab but then I get the run-time 1004 error below. It won't select cell H1 and change the date.

If anyone can help I'd really appreciate it and will owe you.





Private Sub CommandButton1_Click()


Application.ScreenUpdating = False
Dim name, location As String

location = "C:\Users\Brian.Lachance\OneDrive - USTSA\Desktop\New folder"
name = "A1 " + Format(Now() + 1, "mmddyy")

'Opens Master Workbook
Workbooks.Open Filename:= _
"C:\Users\Brian.Lachance\OneDrive - USTSA\Desktop\New folder\A1 Checkpoint Form Master.xlsm"

Sheets("A1").Select
Range("H1").Select
Range("H1").Value = Format(Now() + 1, "mm/dd/yy")
Sheets("Status").Select

ActiveWorkbook.Save 'Updates the Master to reflect the date last used
'************************************
'First Day
ActiveWorkbook.SaveAs (location + name + ".xlsm")
'************************************
'Second day
Sheets("A1").Select
Range("H1").Select
Range("H1").Value = Format(Now() + 2, "mm/dd/yy")
name = "A1 " + Format(Now() + 2, "mmddyy")
Sheets("Status").Select
ActiveWorkbook.SaveAs (location + name + ".xlsm")
'************************************
'Third day

Sheets("A1").Select
Range("H1").Select
Range("H1").Value = Format(Now() + 3, "mm/dd/yy")
name = "A1 " + Format(Now() + 3, "mmddyy")
Sheets("Status").Select
ActiveWorkbook.SaveAs (location + name + ".xlsm")
ActiveWorkbook.Close True
Application.ScreenUpdating = True


End Sub