IF Then Statements

[compgeek1979]

i have a spreadsheet for TV shows that I watch. I want to be able to put a x in a cell and have cell b2 data changed to the next episode and the the air date in Column D to cange for 7 days for what is currently there. I have attached a screen shot. I am open to any suggestions,
HEre is the forula i tried just to see but it did not work.
=IF(E2=x,(B3+1))

=IF(E2="x",(B2+1))

[RobertMika]

Attached your workbook with desired result.

[compgeek1979]

Im Sorry I dont see anything attached

[RobertMika]

I meant-Can you attached your workbook?

[compgeek1979]

oh ok ....Shows List.xlsx

[RobertMika]

If E2=x then B2 will change to S03E05?
But then what?
What if you need E06?
The only way to change a value to a new one if cell is already populated is to use VBA.
Unless you will have your list of Titles and Episodes in different columns and the A-B column will be based on formulas.

[compgeek1979]

ok so how would i use VBA? Could you give me an example of your solution withthe different columns

[FDibbins]

1st, I think you have an incorrect reference in E3?
=E2=IF(E3="x",(B3+1)) This is causing a circ ref error

Then in all the rows after that in E, you need to put "" around all the text references...
=IF(E4=x,(B4=S03E04))
...=IF(E4="x",(B4="S03E04"))

And finally, your IF() statements dont really make sense. The syntax for an IF statement is...
=if(criteria-to-test,what-to-do-if-test=TRUE,what-to-do-if-test=FALSE)
so...IF(E4="x",then do this, else do that...

Perhaps if you explain exactly what you are trying to do?

[compgeek1979]

i am trying to have a list of tv shows that when i watch on episode the list tells me what the next eipsode # is and what date it airs.

[FDibbins]

Can you show a few samples of what your expected outcome would be?

[compgeek1979]

thats my issue i dont know how to achieve it
Basically if i have

Please Login or Register  to view this content.

and i somehow tell excel i watched it then excel changes the values to

Please Login or Register  to view this content.

[FDibbins]

OK well anyone who watches the Walking Dead, is worthy of extra help

I will see what I can put together for you

[compgeek1979]

ok thank you very much

[FDibbins]

Take a look at he attached, see if we are heading in the right direction. I changed B to "Episode watched" and added a "next episode". When you enter an "x" in E, it will populate F with the next episode.

We need to figure out how to handle the column C data though - I have a test in F that checks for "Finished" so maybe give some thought to some consistency in the type of data you will put in that column?

[compgeek1979]

ok so what about dont worry about column C and and do the formula that if E2="" then (B2,2+1) and D2 + 7 days then reset E2 to blank

[FDibbins]

This will change the "next" based just on if there is an "x" in E. To reset and change things back, will take VBA, and thats not my strong point
=IF(E2="","",LEFT(B2,3)&MID(B2,4,1)&TEXT(RIGHT(B2,2)+1,"00"))

[compgeek1979]

by using your method I will have to go back into the Cells in the B column and edit them to get ready for the next episode correct?

[FDibbins]

Yes, unfortunately

[compgeek1979]

That kind of defeats the purpose of my intent.