Skipping Blank Cells Using Array Formula

[Just Anonymous User]

How do I reference another worksheet that has information listed, but skip any blank cells without using VBA?

I tried using the formula from another thread:
{=IFERROR(INDEX(Sheet1!$A$2:$A$22,SMALL(ISTEXT(Sheet1!A2:A22),ROW(Sheet1!$A$2:$A$22)),ROWS(A$31:A31))-ROW($A$31)+1,"")}

but I am still just getting an empty cell, instead of getting the next available cell with information in it.

Thanks,

 

[Aladin Akyurek]

Control+shift+enter and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$22,SMALL(IF(Sheet1!$A$2:$A$22<>"",ROW(Sheet1!$A$2:$A$22)-ROW(INDEX(Sheet1!$A$2:$A$22,1,1))+1),ROWS($1:1))),"")

 

[Just Anonymous User]

Control+shift+enter and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$22,SMALL(IF(Sheet1!$A$2:$A$22<>"",ROW(Sheet1!$A$2:$A$22)-ROW(INDEX(Sheet1!$A$2:$A$22,1,1))+1),ROWS($1:1))),"")

I am accessing the information from Sheet 1 and utilizing the information for sheet 2. Would I need to put the formula for Sheet 2 into the formula?

 

[Aladin Akyurek]

I am accessing the information from Sheet 1 and utilizing the information for sheet 2. Would I need to put the formula for Sheet 2 into the formula?

If the data is in Sheet1 and you want to have the results in Sheet2...

In a cell of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$22,SMALL(IF(Sheet1!$A$2:$A$22<>"",ROW(Sheet1!$A$2:$A$22)-ROW(INDEX(Sheet1!$A$2:$A$22,1,1))+1),ROWS($1:1))),"")



If the data is in Sheet2 and you want to have the results in Sheet1...

In a cell of Sheet1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet2!$A$2:$A$22,SMALL(IF(Sheet2!$A$2:$A$22<>"",ROW(Sheet2!$A$2:$A$22)-ROW(INDEX(Sheet2!$A$2:$A$22,1,1))+1),ROWS($1:1))),"")

 

[Just Anonymous User]

If the data is in Sheet1 and you want to have the results in Sheet2...

In a cell of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$A$2:$A$22,SMALL(IF(Sheet1!$A$2:$A$22<>"",ROW(Sheet1!$A$2:$A$22)-ROW(INDEX(Sheet1!$A$2:$A$22,1,1))+1),ROWS($1:1))),"")


If the data is in Sheet2 and you want to have the results in Sheet1...

In a cell of Sheet1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet2!$A$2:$A$22,SMALL(IF(Sheet2!$A$2:$A$22<>"",ROW(Sheet2!$A$2:$A$22)-ROW(INDEX(Sheet2!$A$2:$A$22,1,1))+1),ROWS($1:1))),"")

I have cells A11:A29 for naming description, F11:F29 for quantity, and G11:G29 for cost in sheet 2. I want to incorporate this information into sheet 1, so I used the formula you have provided. I get A11 information from Sheet 2 since that is the first available cell with the information that I need. If the next available information is in A13 I would like to retrieve that, but the outcome is still a blank cell. I tried to click and drag the formula down into all the cells, but that did not work. How do I fix this problem so I can utilize it for each cell?

Thanks,

 

[Aladin Akyurek]

Did you apply control+shift+enter before dragging down the formula?

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

If still in trouble, try to post a sample along with the expected result.

 

[Just Anonymous User]

Did you apply control+shift+enter before dragging down the formula?

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

If still in trouble, try to post a sample along with the expected result.

Going over the worksheet a little bit closer I realized that I was using the VLOOKUP formula on sheet 1, which was causing the issue because I was running into the N/A error message. When inputting the IFERROR formula into the VLOOKUP formula, it seemed to fixed the problem and populate correctly using the formula you have provided.

I greatly appreciate your time and thank you for the knowledge Aladin. I hope you have a great day!

 

[Aladin Akyurek]

You are welcome. Great to see that you have figured out the issue.