Function for checking a cell and if it holds text "N/A" move right to next cell

[jackson1990]

Hey everyone!

I'm working through a little but of an issue. Trying to think of a way to write a function that will cause it to check the cell to the right of it and if it has "N/A" to skip that cell and move to the cell to the right of it check that cell if it has N/A then move to the right, until it finds one that actually has a date in it.

How it is set up as follows: Someone puts in a final date (when it needs to be finished by) this is manually entered. We'll just use and example here of 5/15/2018. I think use a mix of a if,sum, and vlookup in the cell to the left of it to minus days off for the milestone before that (say the mile stone before final date is like editing) so to the left of the final date I have this function.

=IF(SUM(AC2-VLOOKUP($L$2,Table1[#All],COLUMN()-20,FALSE))=AC2,"N/A",SUM(AC2-VLOOKUP($L$2,Table1[#All],COLUMN()-20,FALSE)))

AC2 is the final date, it vlookups to a table with a bunch of numbers, finds the right number to minus off, brings it back and minuses that number of days off. It also is checking to see if the cell to the right and this current cell are equal (as in having the same date), and if they are it puts N/A in the cell.

What I want to be able to do is for it to check "AC2" (in this case) to see if it is N/A, if it is N/A then I want it to skip to the cell to the right of it, as many times as it has to till it finds a date. Is this possible?

Thanks in advance!!!

 

[Aladin Akyurek]

Care to post a scaled-down version of Table1 along with the desired result(s)?

 

[jackson1990]

Absolutely, appreciate the help. So below is the table. Those are static numbers that the vlookup goes into and chooses. I took down the headers and titles on the left that the vlookup uses to choose as they aren't relevant. So basically it looks up into these cells to find a number in there.

https://ibb.co/cYzg68

then i am using that formula previously stated in every cell here. I am using the formula going from right to left. So 5/15/2018 is the manually inputted date. To the left of it is where the function begins. It basically does 5/15/2018 - the vlookup number = new date in that cell. Which works fine. But as you see when we reach an "N/A" it breaks and gives #value errors as it cannot do N/A minus the vlookup number as it's impossible. What I want it to be able to do is say, hey that's an "N/A" I'm going to skip this cell and move to the next on to the right to retrieve the date. It needs to do that as many times as there is "N/A". For instance there could be 3 N/A's in a row, so it would need to skip 3 to get to an actual date.

https://ibb.co/kmruR8

Does this make sense?

 

[Aladin Akyurek]

I'm not going to type what a picture/image shows into Excel in order to craft a formula.

Try to post Table1 (scaled-down if it's big) along with the results.

 

[jackson1990]

I apologize I thought you were just asking for clarification and being able to imagine what I was looking. Didn't mean to do that to add extra effort.

To make it as easy as possible I have uploaded an Excel file with exactly the situation I am working with. With showing what I want.

https://ufile.io/al8dt

If this doesn't work for whatever reason please let me know.

 

[Aladin Akyurek]

How does 4/5/2018 obtain for Situation 3 for Milestone 1? I'm asking for a procedure for manual calculation?

 

[jackson1990]

4/5/2018 is the first milestone, it all gets pulled in from the manual entry of the 5/15/2018 at the end. So before Milestone 1, there is nothing that is pulled in really. Basically it works from right to left instead of left to right. So they enter the final date, then milestone 9 gets generated, then based off that milestone 8, all the way down toe milestone 1 where it stops.

If that makes sense, and I hope I'm answering that correctly!

 

[Aladin Akyurek]

Hard to follow I'm afraid: I want you to answer the specific question posed in #6 .

 

[jackson1990]

Maybe you could phrase it another way? I thought I did.

It's basically a chain. User inputs a final date in the column all the way to the right. In this case 5/15/2018. Milestone 9's function looks at the date 5/15/2018, subtracts the number coordinating to the Situation and milestone on the table, which outputs a new date for Milestone 9. So to get milestone 9 there has to be user input of a date at the end. Milestone 8 is created based off of Milestone 9's date the same way milestone 9's is based off that user inputted date. Then down the line milestone 7 is created based off milestone 8's date. This continues on all the way to Milestone 1, which gets it's date based off Milestone 2.

Does this help?

 

[Aladin Akyurek]

Is it really necessary having those "N/A" values?

If not, the following would do:

In B2 of Data enter and copy across to J2...

=LOOKUP(9.99999999999999E+307,N(OFFSET(C2:$K2,0,COLUMNS(C2:$K2)-1,,-(COLUMN(C2:$K2)-COLUMN(C2)+1))))-VLOOKUP($A2,Table1!$A$2:$J$4,MATCH(B$1,Table1!$A$1:$J$1,0),0)