Weekly planning update

[ErwinEGY]

Hi there all, i am strugling with this XL problem. But first some basic information; i am using windows 10 and office 2019. Every monday evening a make the planning for the current week. In a xl sheet i compare the last week with the new week (see picture 1) in column E and F all the red fields are there in both columns.

The data from columns B / C / D have to be copied to columns G / H / I, and data from column A has to be copied to column J (see picture 2)

I have to copy and paste this information by hand, is there a formula to do this automatically??

Greetings Erwin

[#2]

@ErwinEGY Yes you can use lookup functions.  Since you don't have the newest Excel I suggest using the INDEX(.. MATCH()) combo.  I also suggest you format the table as a table (home -> format as table) so you can use the table based references so you don't have to worry about how big the table gets and not bog excel down looking at hundreds of blank rows.

=IFERROR(INDEX([SPLITTER_W3],MATCH([@Week4],[Week3],0)),"")

In the attached is a small sample using this formula in column J

In case you can't or really don't want to format as a table I use column-row references in columns G-I

[#3]

@mtarler 

Thanks for your time.

I format the sheet as a table as you suggested.

Tested it in the last week planning, works good on column J.

I use the same formula for columns G-I and this isn't working.

What do i wrong, see the photo.

Spoiler

Spoiler

How do i send a file to you instead of photo's?

Where is the link in the editor?

Thanks mtarler

[#4]

@ErwinEGY so i don't know how or if is even possible to 'lock' a column using the structured (table) references like using a $ on the row or column references.  That said, when you are in a table and you click on a cell  or column it will try to use the structured references accordingly.  So by clicking on the formulas I had in there, I could highlight the purple ref and then click at the top of the column (high enough to get the down arrow cursor but not highlight the entire column / column letter) and it replaced the previous row-col ref with the table ref.  See attached.

as for attaching files, you should get a box below the edit box that says 'Drag and drop here ...' but you may have to have a certain rank to get that permission.

[#5 · Accepted solution]

@mtarler 

Hi Mtarler,

Yeh it works, i am using your file for it and it works, see photo 1. 

I tested it on monday for the first time with the new weeks data, and as you can see it works perfectly for the data. But...... is it also possible to copy the field colors with it??? See photo 2.

I will also send the last file from this week, so you have a better look on it.

For so far thanks for all your time.

Greatings Erwin.

[#6]

so in cell formulas can't see nor set the color or other formatting. Conditional Formatting can set them (and only set formatting). So if there are 'rules' for how they get colored, then instead of manually setting the cells to those colors you can use conditional formatting and then the same 'rule' can be applied to both before and after values. The only other way is to get into writing a macro to do it.