Trying to create a formula to autofill cells (with text and other cells with dates)

[JulienMercier]

Hi,

I'm trying to create a formula that will pick up only a few words in columns D, J or P. So if those words are in a in cell in those columns, automatically the cell containing those words needs to be copied in a cell in column V. So for example, if we take the words "Receipt for initial PD" in cell D2 needs to be copied to V2. Also, the dates in D3 needs to go to V3 but minus 3 days for approval.

Is it something that would be possible?

Can someone help me please?

Thank you very much,

Julien M.

[#2]

@JulienMercier 

Attaching a simplified example of what I think you are asking for. Of course, you need to adjust it to the ranges in your own spreadsheet. 

[#3]

@Riny_van_Eekelen 

Thank you Riny! This is very helpful!

[#4]

@Riny_van_Eekelen 

Do you know if it is possible to copy the cell in the next available cell? It does have to be A2 copied to F2.

I have attached a copy of my Excel. For example from my excel, If I want D11 to be copied, I want it to go in the Y column but to the next available cell. not to Y11.

Thanks!

[#5]

@JulienMercier 

I believe your Excel version requires the formula to be entered by Shift-Ctrl-Enter. It will put curly brackets around the formula. See attached.

[#6]

@Riny_van_Eekelen 

Again this is very helpful! Thanks for all your help.

Although, I this is not exactly What I need. I think I am not explaining it the right way and I seems to be complicated.

I would like to have every cells that contains the words Receipt of initial PD be copied in the column Y but with the wording that every cell have. E.G. the first one starts with Alexandra, second one starts with Castle Mountain and the Hays LNG..... Those cells to be copied in column Y but in order starting from cell 2,3,4,5,6,7,8..... not like 2, 12, 34, 50.....

[#7]

@JulienMercier 

Have been away. Let me get back you tomorrow morning (CET).

[#8]

Thank you Riny :)

[#9 · Accepted solution]

@JulienMercier 

Good morning! I attach two versions of your file with updated formulae in columns Y and Z in the Master sheet. One file is using the new FILTER function. It does exactly what you want, but it depends on your Excel version if you can use it or not.

The other is using a formula, similar to what you already had, but I couldn't figure out how to skip the empty rows. I know it's possible, I have seen it done but I can't find it right now. I believe it was @Sergei Baklan who demonstrated it not too long ago.

But, after studying your file I wonder is you really need to skip the empty rows. You are using a template that seems be a Homework Tracker, producing a weekly calendar view of all tasks due on every day of the week. I noticed that, even with the empty rows in column Y, and Z the calendar view is correctly populated. So, why bother filtering out empty rows in the master list? 

[#10]

@Riny_van_Eekelen 

Hi Riny,

Thank you very much for all you help!

This version, Projects Milestones Calendar2-FILTER.xlsx , seem to do pretty much what I want.

The reason why I want to filter empty rows it's because I will have to make a formula for other words to find in the column D. Also, I will have to make a formula for the column K and also for column R. And they will all need to go in column Y. I don't know if it's clear?

[#11]

@JulienMercier 

Not entirely clear on the question if you can use the FILTER function. If YES, congratulations. If not, I did work out a formula that sequentially lists the only text with the searched words.

=IFERROR(INDEX(D:D,AGGREGATE(15,6,(1/ISNUMBER(SEARCH($I$2,D:D,1))*ROW(A:A)),ROW()-ROW($D$1)),1),"x")

The attached workbook has the formula in Col Y. At first, I copied it all the way down but then it became very slow in calculating/updating. I suggest you copy the formula down until you see "x". Hope this is an acceptable solution for you.

[#12]

@Riny_van_Eekelen 

I don't think I can use the FILTER function. When I open your document I can see the information from column D is copied in column Y but if I change something I am losing everything in column Y. I do I get it to work? The FILTER function I mean. Also, the date are no longer doing the minus 4 days when populating in the Z Column.

[#13]

@JulienMercier 

I uploaded a new version of the file in my previous post (same file name). Fixed a small formula error and deducted 4 days in the date column.

[#14]

@Riny_van_Eekelen 

I think I have pretty much what I am looking for with your update.

A HUGEEEE thanks for all your help on this! Very much appreciated! :D

[#15]

@JulienMercier You're welcome. It was a learning experience!

[#16]

@Riny_van_Eekelen 

now I'm trying to figure out how to add more words to find and that they all go to my Y column...

Also, there is certain type of words that instead of minus 4 days they need to by minus 2 days, some minus 7 days.... Don't know if you would be able to help me as well with this?

[#17]

@JulienMercier 

Now it becomes a bit complicated. Perhaps more than necessary. It seems that column D consists of "Agency name - action to be taken". Now you want to search for multiple actions that "belong" to Jen.

I think my formula is too "simple" for that, although it is already quite complicated and not so easy to read, maintain or change. Perhaps someone else, more able, reading this, can resolve it.

Though, my feeling is that you are using a standard template for something that it wasn't really designed for. Perhaps you should look for a solution where you list all actions that belong to a particular person (like Jen) in a separate table and spilt column D in two (Agency and Action). Then you could lookup all Actions that relate to "Jen" (or "VP", "Pres", "Minister") and then find the Agency, Action and Date. Not sure though if this is at all possible in the template you are using. 

[#18]

@Riny_van_Eekelen 

Thanks for looking into this Riny. Yes, I think that what I need is pretty complicated.

I think I will have to abandon what I really want And pretty much do it manually.

Thanks for all your help on this!