I have a list of names on one sheet. I would like to be able to type something next to a name and that name is placed in another sheet in the correct place.
For example if I type A1 1A next to a name, that name would fill the cell next to A1. It would not be the A1 1A of the excel sheet though, it would be where I have placed A1 1A.
Have attahced some photos to hopefully make it clearer.
Destination of names
List of names
You cannot use a simple vlookup because that function looks only to the right. Of course you could change your order of your data. But if you want to keep it as-is, then you could use INDEX and MATCH to solve this.
The MATCH function returns the position of something within a range, while the INDEX function will show you the result from that position. Wrap it with an IFERROR to display whatever you want when a match is not found.
Lastly, in your formula you'll need to concatenate [A1, a space, and then the row value] to match your entry from your other sheet if that's how you choose to enter the data.
Cell B3 formula:
=IFERROR(INDEX($E$2:$E$10,MATCH(CONCAT(A$1," ",A3),$F$2:$F$10,0)),"nameless")
Drag it down and you're done.
=A1for that). To actually COPY the data you need VBA-code.