Copy the cell values of a column whose certain rows are hidden, to another sheet

[Ian Y]

Here are two demonstrative sheets:

Demo1.xlsx
	A	B
1	ID	Text
2	zQmGwsBXVa	Lorem ipsum dolor sit amet. Consectetur adipiscing elit.
3	8tcbmfv2dl	Aliquam pulvinar tincidunt lorem vel cursus.
4	U7km0cE1UV
5	Lm3BK6UrpS	Pellentesque tincidunt purus nec. Enim molestie venenatis. Nunc tempor dapibus dui, sed tempor mi fermentum pretium.
6	IOwCYwdCzP
January 7

Demo2.xlsx
	A	B
1	ID	Text
2	rUHBLBZZyc
3	zQmGwsBXVa
4	LxNNV0uEwx
5	oiNo2eOZIU
6	JV8n6HfJAE
7	8tcbmfv2dl
8	q0hZR5UrGi
9	CDFzs37XMd
10	Lm3BK6UrpS
11	JZxjBnip0M
January 8

In Demo1.xlsx, by using filter to hide row whose cells in column B are empty, I can copy other row's Text values all at once easily:

My question is, how do I fill in those copied Text values to the cells whose corresponding IDs are zQmGwsBXVa, 8tcbmfv2dl, and Lm3BK6UrpS, respectively in column B in Demo2.xlsx all at once easily? The order of rows is not to be worried about. In Demo2.xlsx, zQmGwsBXVa always appears before 8tcbmfv2dl, which always appears before Lm3BK6UrpS.

The two sheets are streamlined versions for demonstrative purposes. The actual sheets can contain more than 1,000 rows.

 

[DanteAmor]

Try:

Demo2.xlsx
	A	B
1	ID	Text
2	rUHBLBZZyc
3	zQmGwsBXVa	Lorem ipsum dolor sit amet. Consectetur adipiscing elit.
4	LxNNV0uEwx
5	oiNo2eOZIU
6	JV8n6HfJAE
7	8tcbmfv2dl	Aliquam pulvinar tincidunt lorem vel cursus.
8	q0hZR5UrGi
9	CDFzs37XMd
10	Lm3BK6UrpS	Pellentesque tincidunt purus nec. Enim molestie venenatis. Nunc tempor dapibus dui, sed tempor mi fermentum pretium.
11	JZxjBnip0M
January 8

Cell Formulas
Range	Formula
B2:B11	B2	=IFNA(VLOOKUP(A2,'[Demo1.xlsx]January 7'!$A:$B,2,0),"")

 

[Ian Y]

@DanteAmor Thank you! This is the first time I try out VLOOKUP. I just pasted the formula into B2 and manually expand the range to B11, and then I got the successful result.

If Demo2.xlsx has 1,000 rows, is there an easier way to apply the range so that I don't need to manually expand the range to so many rows?

 

[DanteAmor]

If Demo2.xlsx has 1,000 rows, is there an easier way to apply the range so that I don't need to manually expand the range to so many rows?

Just copy the formula up to row 1000 or 2000.

 

[Ian Y]

Just copy the formula up to row 1000 or 2000.

Excuse me, but what does that mean?

 

[DanteAmor]

is there an easier way to apply the range so that I don't need to manually expand the range to so many rows?

So I don't understand what you need.
In my example the formula goes from cell B2 to B11, if you need the formula up to cell B1000 then copy the formula from cell B2 to cell B1000.



=IFNA(VLOOKUP(A2,'[Demo1.xlsx]January 7'!$A:$B,2,0),"")
The formula covers the entire column A and B, it is not necessary to delimit the range up to cell 1000, since the formula covers the ENTIRE column.

Is that what you need?

 

[Ian Y]

So I don't understand what you need.
In my example the formula goes from cell B2 to B11, if you need the formula up to cell B1000 then copy the formula from cell B2 to cell B1000.



=IFNA(VLOOKUP(A2,'[Demo1.xlsx]January 7'!$A:$B,2,0),"")
The formula covers the entire column A and B, it is not necessary to delimit the range up to cell 1000, since the formula covers the ENTIRE column.

Is that what you need?

After I pasted the formula into B2, I didn't see the expected result. I had to manually delimit the range up to B11 to see the expected result. What did I miss?

 

[DanteAmor]

After I pasted the formula into B2, I didn't see the expected result. I had to manually delimit the range up to B11 to see the expected result. What did I miss?

According to your example in B2 the result is empty, which is correct.
For it to work automatically you will have to create a macro that copies the formula downwards, but it seems to me that manually copying the formula downwards to B11 or B1000 is enough. I think it is not a complicated task.

Another option is to create a table, so when you put the formula in B2, it is automatically copied to the entire column.

 

[Ian Y]

I see. Thanks. My last question is that every time I open Demo2.xlsx, there is a dialog box asking me whether I want to update the link to get the latest data. Is it possible to disable that dialog?

 

[DanteAmor]

here is a dialog box asking me whether I want to update the link to get the latest data. Is it possible to disable that dialog?

Check this:

Control the startup message about updating linked workbooks - Microsoft 365 Apps

Discusses how to control the startup message that is prompted for updating linked workbooks in Excel 2002 and in later versions of Excel.

learn.microsoft.com