formula help - probably simple but I'm new to this!

[Kangolia]

I have a weekly resource sheet that gets extracted from an external data source into excel. The team add an extra column (O) to add comments that are forever changing in a shared Teams environment.

The name of the person always sits in column D and the comments related to that person always sits in column O.

We manually copy and paste the comments over one by one because of filters and because the list is not in the same order.

Is there a formula to easily find the comments related to that person from last week and automate it to transfer onto the new list by way of a formula???

For instance: looking up someone's name who resides in say D2, but on the previous sheet could be residing in D27, I would need the comments from O2 to go into O27.

TIA

 

[Sufiyan97]

Try

Sheet1

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				Name											Comment
2				A											Comment1
3				B											Comment2
4				C											Comment3
5				D											Comment4
6				E											Comment5
7				F											Comment6
8				G											Comment7
9				H											Comment8
10
Sheet1

Sheet2

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1				Name											Comment
2				B											Comment2
3				D											Comment4
4				A											Comment1
5				C											Comment3
6				H											Comment8
7				G											Comment7
8				F											Comment6
9
10
Sheet2

Cell Formulas
Range	Formula
O2:O8	O2	=INDEX(Sheet1!O:O,MATCH(Sheet2!D2,Sheet1!D:D,0))

 

[Kangolia]

Thank you so much for your quick reply. I seemed to have confused myself with this now as I think the sheet names were in the wrong order so I changed it and it worked and now I get a error so i thought we could start again and I show you my screen shots as a mock up if we could use this example so I dont get confused lol

So basically, need the comments from previous week to match up with persons name on the following weeks sheet - I would need the conditional formatting to also be copied over i.e the colour of the box and text too. Could you please give me the formula working on these sheets - and is this doable to take the colours of the boxes too???

I really appreciate your help

How do you learn everything in excel - it baffles the hell out of me!

 

[Kangolia]

and everytime im trying to enter your formula this pops up - have no idea what to do lol

 

[Peter_SSs]

I would need the conditional formatting to also be copied over

You cannot copy the formatting over by using a formula, you would need to use vba (that is, a macro). Would that be acceptable?
What is/are the the conditional formatting rule(s) in cell O2 of sheet '5th Jul 2021'?
BTW, I suggest that you name your sheets more consistently (one has "Jul" and the other has "July")

and everytime im trying to enter your formula this pops up

That is because the formula uses 'Sheet1' and 'Sheet2' as the sheet names but your sheets are named differently. (You had not told us the names when that formula was suggested)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. That would also have given us the conditional formatting rules.

 

[Kangolia]

Hi ya

Thanks so much - in fact there is no conditional formatting - my bad. We just manually fill the cell with colour. i will have to learn how to use this XL2BB.. Macros dont run on shared documents on teams does it?

 

[Peter_SSs]

Macros dont run on shared documents on teams does it?

I think that is correct.

We just manually fill the cell with colour.

You still cannot use a formula to copy the colour formatting.

To get the value, use this formula in the comments column of the 19th Jul 2021 sheet.
You will need to check the name of the table in the '5th Jul 2021 sheet' & change the 'Table1' in this formula to match that table name if it is not 'Table1'.

=XLOOKUP([@Name],Table1[Name],Table1[Comments],"")