Sheet 2 skipping

[Countryboy69]

I'm building a workbook for work, I have very limited excel knowledge (YouTube has been a great friend lol). Anyways my question is on sheet 1 column A every cell may not end up filled out so on sheet 2 row b I want it to not skip rows if sheet 1 does. Sheet 1 is my input sheet where sheet 2 does the calculations. Any help would he awesome.

 

[Yongle]

if the formula is already in the cell...
select the cell ...
{F2} to go into edit mode ...
use the 3 keys instead of {ENTER}

 

[Countryboy69]

Did you remember to commit with {CTRL}{SHIFT}{ENTER} ?

OK i figured that one out lol. yes i forgot to commit, but now if no info is in the cell it shows #num ! any ideas on this. BTW you rock dude thank you for your help so far

 

[Yongle]

If you want to suppress #NUM errors, wrap IFERROR around the formula, like this...
(it is still an ARRAY formula)

=IFERROR(INDIRECT("Sheet1!A"&SMALL(IF(LEN(Sheet1!$A$1:$A$500)=0,"",ROW(Sheet1!$1:$500)),ROW(B2))),"")

 

[Countryboy69]

if you want to suppress #num errors, wrap iferror around the formula, like this...
(it is still an array formula)

=iferror(indirect("sheet1!a"&small(if(len(sheet1!$a$1:$a$500)=0,"",row(sheet1!$1:$500)),row(b2))),"")

dude you freaking rock thank you so much for your help.

 

[Yongle]

also is there anyway to incorporate this formula in the one you made?
=IF(OR(D3="X",E3="X",F3="X",G3="X",I3="X",K3="X",L3="X",H3="X"),NOTES!A3,"")

1. Which sheet contains D3,E3,F3,G3,I3,K3,L3 & H3 ? Are those cells in "Notes" or Sheet2?

2. Are those cells empty if they do not contain "X". If so you could use simpler formula
=IF(D3&E3&F3&G3&I3&K3&L3&H3>"",Notes!A3,"")