Omitting a specific phrase while using IF

[jsoutham]

Hi All,

I am currently using the following formula to pull information from a sister sheet. The original name of the cell in "Select Amenity" but it is a drop down list for the user to select. I am wondering if I can add to the below formula so if the original cell still says "Select Amenity" the new cell will come up blank, instead of showing "Select Amenity". Is this possible?

=IF(ROWS('Order Form'!$C$8:C9)>SUM('Order Form'!$H:$H),"",LOOKUP(ROWS('Order Form'!$C$8:C9),'Order Form'!$T:$T,'Order Form'!C:C))

Please see attached workbook, highlighted sheets and cells.

MutlipleAmenities.xlsx

[Subject_Name_Here]

You'll have to help me understand. Which columns are you referring to? Your primary?

Anyway, you can always use a nested if statement, aka add another if in front of your equation. Since you used the validation drop down menu and not a form or active x like most people do (which uses index numbers), then you refer to the cell as "Select Amenity".

=IF(C9="Select Amenity","",IF(ROWS('Order Form'!$C$8:C9)>SUM('Order Form'!$H:$H),"",LOOKUP(ROWS('Order Form'!$C$8:C9),'Order Form'!$T:$T,'Order Form'!C:C)))

Now, if it's "Select Amenity" the cell will show up blank. If you want it to say something different then just replace the "" with anything you want. If it's text you want to appear, just surround it with quotations ("Your text here!"). As a bit of a warning, if you have other cells that are dependent on this if statement you'll have to put =IFERROR(...,0), assuming you want it 0 when there's an error. The error will come up when you try to multiply "" by a number. Maybe just use 0 instead and conditionally format the zeros so they'll disappear. It's up to you.

Mike

[jsoutham]

Thanks Mike, that works great for the immediate cell, but the formula is set up that the table grows based on the quantity allocated on the first sheet. For example, if someone selects "Grouse" from the drop down list, and quantity 2, the next available 2 rows will both be grouse. This means that even though F5(Tracking Sheet) is looking at C9(Order Form) for its information, 2 rows later (and two "grouse's), F7 is not looking at C11, but instead, C10 for its information. Does that make sense? The problem I am running into, is the source for the "Logical Test" for the first IF, is fixed, but it needs to change with flow of the document. Is there a way to tie that into the logical test for the second IF?