Forum Discussion
Trying to create a formula to autofill cells (with text and other cells with dates)
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 SergeiBaklan 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?
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 SergeiBaklan 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?
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!
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.
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?
JulienMercier You're welcome. It was a learning experience!
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! 😄
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.
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.
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.
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?