Hey all,
First time posting here. I'm looking to modify this ifs() function so that it does not repeat values. Please see attached screenshot or I've attached a Xl2bb mini spreadsheet for you to see the formulas yourself if you'd like.
I have three columns and I want to pull the info from the left column (Bidders) into the right column (Cells In Other Sheet In Workbook) only if the value in the middle column (Bidding?) equals "Yes". I want the data to fill from top to bottom in the right column with no blanks in between. If you look at the screenshot, I want Company 3 to appear in the top cell of the right column (which I've managed to do), then I want to next cell down (in the right column) to display the next company that is listed as "Yes". In this case, it would be Company 4.
I tried to explain it the best I could. Maybe there's a simple solution that I'm missing?
Thanks for any help/advice in advance!
First time posting here. I'm looking to modify this ifs() function so that it does not repeat values. Please see attached screenshot or I've attached a Xl2bb mini spreadsheet for you to see the formulas yourself if you'd like.
I have three columns and I want to pull the info from the left column (Bidders) into the right column (Cells In Other Sheet In Workbook) only if the value in the middle column (Bidding?) equals "Yes". I want the data to fill from top to bottom in the right column with no blanks in between. If you look at the screenshot, I want Company 3 to appear in the top cell of the right column (which I've managed to do), then I want to next cell down (in the right column) to display the next company that is listed as "Yes". In this case, it would be Company 4.
I tried to explain it the best I could. Maybe there's a simple solution that I'm missing?
Thanks for any help/advice in advance!
Master Scope Spreadsheet (Revised 1.29.2021).xlsm | |||||||
---|---|---|---|---|---|---|---|
O | P | Q | R | S | |||
16 | Bidders | Bidding? | (Cells In Other Sheet In Workbook) | ||||
17 | Company 1 | No | Company 3 | ||||
18 | Company 2 | No | Company 3 | <<< I don't want the first company to repeat. I want the next cell that fills here to be the next compnay that is listed as "Yes", which would be Company 4 in this example. | |||
19 | Company 3 | Yes | Company 3 | ||||
20 | Company 4 | Yes | Company 3 | ||||
21 | Company 5 | No | Company 3 | ||||
22 | Company 6 | No | Company 3 | ||||
23 | Company 7 | Yes | Company 3 | ||||
24 | Company 8 | No | Company 3 | ||||
25 | Company 9 | Yes | Company 3 | ||||
26 | Company 10 | No | Company 3 | ||||
27 | Company 11 | No | Company 3 | ||||
28 | Company 12 | No | Company 3 | ||||
29 | Company 13 | No | Company 3 | ||||
30 | Company 14 | Yes | Company 3 | ||||
31 | Company 15 | No | Company 3 | ||||
Bid Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R17:R31 | R17 | =IFS('Bid Summary'!$Q$17="Yes",'Bid Summary'!$O$17,'Bid Summary'!$Q$18="Yes",'Bid Summary'!$O$18,'Bid Summary'!$Q$19="Yes",'Bid Summary'!$O$19,'Bid Summary'!$Q$20="Yes",'Bid Summary'!$O$20,'Bid Summary'!$Q$21="Yes",'Bid Summary'!$O$21,'Bid Summary'!$Q$22="Yes",'Bid Summary'!$O$22,'Bid Summary'!$Q$23="Yes",'Bid Summary'!$O$23,'Bid Summary'!$Q$24="Yes",'Bid Summary'!$O$24,'Bid Summary'!$Q$25="Yes",'Bid Summary'!$O$25,'Bid Summary'!$Q$26="Yes",'Bid Summary'!$O$26,'Bid Summary'!$Q$27="Yes",'Bid Summary'!$O$27,'Bid Summary'!$Q$28="Yes",'Bid Summary'!$O$28,'Bid Summary'!$Q$29="Yes",'Bid Summary'!$O$29,'Bid Summary'!$Q$30="Yes",'Bid Summary'!$O$30,'Bid Summary'!$Q$31="Yes",'Bid Summary'!$O$31) |