Sir Topham Hatt Posted October 3, 2020 Share Posted October 3, 2020 Someone in my team did the old "use excel because of the boxes" thing years ago and created timesheets using an excel spreadsheet. These spreadsheets don't do any calculations. I was looking at the IF/THEN command and whether it could have more than one IF? For example if box A1 says W then box Z1 says WORK, but I also need it to check if box A1 has another letter and if it does, put something else in box Z1 - there may be 5 or 6 potential letters in box A1 and each one would need to print something different in Z1. Is that possible? Not too worried about calculating the hours worked as yet a that should be easy (ish!). Link to comment Share on other sites More sharing options...
+BudMan MVC Posted October 3, 2020 MVC Share Posted October 3, 2020 Prob be easier to use a vlookup table. You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1. This might help https://exceljet.net/excel-functions/excel-vlookup-function Sir Topham Hatt, NoahGreat and +Matthew S. 3 Share Link to comment Share on other sites More sharing options...
+Matthew S. Subscriber² Posted October 3, 2020 Subscriber² Share Posted October 3, 2020 46 minutes ago, BudMan said: Prob be easier to use a vlookup table. You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1. This might help https://exceljet.net/excel-functions/excel-vlookup-function Could also use a 3D array lookup using INDEX / MATCH as well, though this could be costly. Link to comment Share on other sites More sharing options...
+BudMan MVC Posted October 3, 2020 MVC Share Posted October 3, 2020 Doesn't sound like he has all that much to lookup Just 6 letters Link to comment Share on other sites More sharing options...
Sir Topham Hatt Posted October 8, 2020 Author Share Posted October 8, 2020 (edited) Hi Thanks - have used this function instead and it works fine. Put the codes in a different sheet and added a query to be able to call the codes / display text from that sheet. The next part is hours worked, which seems to involve a long IF equation otherwise it shows minus hours: =IF(E15>C15, E15-C15, 1-C15+E15) Which gives me the result I'm looking for. I may be back depending on how complicated I want to make it but managed to also add protection for the formulas, so I can't change those cells, while still being able to add hours worked and such. Cheers Edited October 8, 2020 by Sir Topham Hatt Link to comment Share on other sites More sharing options...
Sir Topham Hatt Posted November 8, 2020 Author Share Posted November 8, 2020 Okay, so here we go. Can I have two IF commands in one cell? For example, I have the following in one cell: =IF(E18 > C18,E18 - C18,0 - C18+E18) which gives me the total hours worked, depending on what I write in the previous columns. Elsewhere on the same sheet, I have a column for "codes". If I type in W, the next column prints "Working", or OT prints "Overtime". If I write "FH", it'll say "From Home" but the hours are still 0. Can I change the above IF command to also look at the "Codes" column and if it says a specific code ("FH"), then it'll automatically change the hours to a number I set? I know this is straying away from dumb timesheets but just wondering. Thanks Link to comment Share on other sites More sharing options...
NoahGreat Posted November 10, 2020 Share Posted November 10, 2020 On 03/10/2020 at 18:07, BudMan said: Prob be easier to use a vlookup table. You create a table with values and what you would like it to return.. Then in your z1 cell put in the formula to look up A1 in the table and return what you want to show in Z1. This might help https://exceljet.net/excel-functions/excel-vlookup-function/worktime Thank You for guide But i recommend to add a HLOOKUP guide too for timesheets building Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now