Copy Visible Filters cells to other Visible and Filtered cells

[eugchen]

on the sheet that im attaching, i have data on SHEET 1 that i want to copy onto SHEET 2. SHEET 1 has test scores for 5 students, sheet 2 is missing 2. i want to FILTER John and Larrys score on Sheet 1, thereby only showing cells A6, B6, A8, B8. the copy over the "88" and "85", then head to Sheet 2, and filter out only for John and Larry. i highlight cells B6 and B8, go to Ctrl +G, select blanks, and paste over, and get an error. how do i accomplish this? thanks

[bebo021999]

Why didn't you try to use VLOOKUP?

In B6 sheet 2:

=VLOOKUP(A6,Sheet1!$A$5:$B$9,2,0)

Copy to B8

[eugchen]

YA i thought of that, but my real spreadsheet has 100 columns. so didnt want to go through all of that.

[Poons]

Hi eugchen,

I'd agree with bebo021999 that VLOOKUP is a good way to go about it. I understand writing the formula for 100 columns is a problem...
Assuming the position of the [Grade] column (and other 99 of them are the same in Sheet2), maybe you could still copy/paste the VLOOKUP formula below (example for your Sheet2, Cell B8)?


=VLOOKUP($A8,Table1,COLUMN(B8),FALSE)


The COLUMN() function ensure the column number in the range containing the return value is changing accordingly.
"Table1" is a table referencing your Sheet1 dataset (not absolutely necessary but I love Tables )

[José Augusto]

Hi @eugchen

Have you tried using the advanced filter?


See the file