Need help with IF statement

I have a formula with may IF statements and* I need this to work in Excel 2000. The issue is I have this formula and it works fine in Excel 2000
*
WORKS
*When I try to add just 1 more If to this (more than 8) It will give me an error in Excel 2000.*
*
DOESN'T WORK IN EXCEL 2000
*
=IF(V15="#'S",$A$355,IF(V15="INV",$A$354,IF(V15="OP INV",$A$355IF,IF(V15="OP",$A$355,IF(V15="REQ OP",$A$355,IF(V15="MID",$A$356,IF(V15="REQ MID",$A$356,IF(V15="CL",$A$357,IF(V15="REQ CL",$A$357,""))))))))
*
Does anyone know of a way to write the 2nd formula having 8 or more IF's?

Hi g1terra,

Instead of using multiple If statements, look at using VLookup instead.

https://www.howtogeek.com/howto/1378...okup-in-excel/

If you can attach a sample workbook we can show you "exactly" how to use it.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

there is a maximum of the amount of IF statements you can nest which is 7

why dont you just have a table to vlookup what it should do when has any of those values?

This should work, but it's pretty brute-force:
=IF(OR(V15="#'S",V15="OP INV",V15="OP",V15="REQ OP"),$A$355,IF(V15="INV",$A$354,IF(OR(V15="MID",V15="REQ MID"),$A$356,IF(OR(V15="CL",V15="REQ CL"),$A$357,""))))

ps: A slightly tighter version, added later:
=IF(OR(V15={"#'S","OP INV","OP","REQ OP"}),$A$355,IF(V15="INV",$A$354,IF(OR(V15={"MID","REQ MID"}),$A$356,IF(OR(V15={"CL","REQ CL"}),$A$357,""))))


Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

awww i did the brute force too...but too slow

Formula:

Please Login or Register  to view this content.

its slightly different =p
i have INV in the 3rd IF

As there were already responses in both threads, I have merged your duplicate threads. In future, please do NOT start more than one thread per issue. Thanks.

Ok here is a copy.

I started it on P1 and highlighted the 1st sequence I have to copy the formula to in orange. The issue with using the VLOOKUP vs IF statements is I need this schedule to know each version of the Opening Shift (Ex OP, OP INV, REQ OP). But what makes this not work is I also need to be able to manually overwrite formula (instead of always selecting using drop down) to add the shift times. When I use VlOOKUP and manually overwrite these populated shift times it comes out as a date format I need to show as text. (ex 10-8) not Oct-8

Sorry the file is 3MB but saying upload failed..

There is no file attached ...

Lets try this again..

To manually enter this sort of string you need to precede it with an apostrophe (') to prevent Excel from reading it as a date. So, to get 10-8, you must actually type '10-8.

Or, format the cell as text 1st

As Ford has said above, pre-format cells as text.

Originally Posted by g1terra

If i format the cell as text the formula that auto populated will not show result?

It seems to continue working here. It's not a calculation, just a lookup, so there is no reason why changing the format in this instance will break it.

Originally Posted by g1terra

Yes I know this but the people using my sheet will not. Is there a way of displaying the cell that auto populated as text so they wont have to do this? (use the ')

Format the whole range as text, they wont see any difference and entries will be accepted correctly

Originally Posted by g1terra

If i format the cell as text the formula that auto populated will not show result?

OK, you have said the user is entering data into the cell, now you are saying there will be a formula. - which is it?

Hi g1terra,

I'm looking at your sheet and at cell V13 on the sheet named "P1". This cell is merged with U13. Because U13 and V13 are merged, it looks like U13 and/or V13 are equal to "REQ OP". V13 really has nothing in it. This makes your VLookup formula a problem as you are asking to match a blank cell. Try this formula in U14:

Please Login or Register  to view this content.

I think the merged cells in your design of your worksheet is confusing us all.

Originally Posted by g1terra

Did you look at the sheet I attached? The orange highlighted fields will auto Populate (using drop down list). I want to reserve the option for them to overwrite the formula and type text in. The Vlookup I am using is not working like it should for some reason. Can you open this sheet to see what I am talking about.

I can find no file attached

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

The file was attached to post #9 - it seems the OP removed it last evening.

Thats probably why I cant find it, but if OP removed file, why ask if we looked at it lol?

He asked that question roughly 9 hours before he removed the file. Maybe he just gave up on the thread? Some members don't seem to grasp the time zones at play here. Hopefully he'll see these new posts and replace the file.

we can but hope