Excel - Formula to Reference another Cell if blank

[Hunter Childress]

Hi There,

If you open the attached spreadsheet, I will explain what I am trying to achieve.

On the SUMMARY tab, I would like Cell B3 and down to pull the list of railcars on the RAILCARS Tab in Column C only if Column G is blank.

To explain what is happening, I only want the list of railcars (Column C) to show up on the summary page (B3 and down) if they have been released. If column G is blank, then the railcar hasn't been released yet.

Also, on the summary page (B3 and down), I don't want blanks to be shown if there is a break in Railcar spreadsheet.

I need a formula that will return Test 1, Test 4, Test 5, Test 8-14, Test 17,Test 18, Test 22-35 on the Summary Page without any break.

Let me know if you have any questions.

Thank you,

Hunter

 

[Hui]

Hunter

In Summary!B3:
=IFERROR(INDEX(RAILCARS!C:C, SMALL( IF(RAILCARS!$G$2:$G$36 ="", ROW( $A$2:$A$36)), ROW(A1))),"") Ctrl+Shift+Enter
Copy down

You can read about how that works here:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Also change C3 to: =COUNTIF(B3:B40,"> ")

or see in the attached file: