If Then Formula For Headers?

I posted a copy of the file I am trying to complete. I want the orange column on my sheet to display the stage number ( the header) when data is entered into one of the stages cells. The problem is that the data is not consistent, and some of the data will be a date, and some of it just says "comp". Is there a formula to say that if something is entered into one of these stage columns, then the orange column will output the stage number where the data was written?Help.xlsx

Do you mean if a date is entered into any of the columns A to V you want that column heading to appear in column W, but anything else (like "comp") should be ignored?

If so, put this formula in W2:

=IFERROR(INDEX(A$1:V$1,MATCH(9E+99,A2:V2)),"")

then copy down. You may need to widen the column to see the full heading.

Hope this helps.

Pete

Yes this is what I mean

This is really close, but I actually need it to include "comp" as this file is very old ( it is much larger than what I showed) and there are many missing dates that only show "comp" basically if there is anything in these cells then I need it to display what stage it is on.

In your example file "comp" appeared many times on the same row. In this case, which column would you like the heading for?

Pete

The most recent stage. So if comp is in stage 17 18 and 19 then I want it to display 19 so I can see where the project currently stands.

Put this in W2:

=IFERROR(MATCH(9E+99,A2:V2),IFERROR(MATCH("comp",A2:V2),""))

it will report the column number, which seems to be what you want from your last post. If there is a date on the row it will report this, if not it will report the last "comp" on the row, or nothing.

Is this what you want?

Hope this helps.

Pete

I actually need it to report if the text is "comp" (or even just any text) as well. I have tried messing with your formula but I can't figure out what the parameter is for unlimited text like how the 9E+99 is for all numeric values. I have also been looking at this but of VBA to see if this is what I need to allow any text in the cell to display the stage the project is on.

Please Login or Register  to view this content.

Here is a sample of how I want it to work Help.xlsx. I realize that my first post might not have been really clear. This has been a jumbled mess that I have been working on for way to long, I really appreciate all your help!

I think this will give you what you want:

=MAX(IFERROR(MATCH(9E+99,A2:V2),0),IFERROR(MATCH("zzzzzz",A2:V2),0))

Put it in W2 then copy down.

If the cells are completely empty you will get zero, otherwise it will give you the column number of the last filled cell.

Hope this helps.

Pete