Feb 19 2022 02:01 PM
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
Feb 19 2022 06:34 PM
@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
Feb 20 2022 03:41 PM
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.
How do i send a file to you instead of photo's?
Where is the link in the editor?
Thanks mtarler
Feb 20 2022 07:35 PM
@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.
Mar 01 2022 01:36 PM
SolutionHi 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.
Mar 01 2022 02:45 PM
Mar 01 2022 01:36 PM
SolutionHi 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.