Need help with ifs() function..

Moose2895

New Member
Joined
Jan 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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!

Master Scope Spreadsheet (Revised 1.29.2021).xlsm
OPQRS
16BiddersBidding?(Cells In Other Sheet In Workbook)
17Company 1NoCompany 3
18Company 2NoCompany 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.
19Company 3YesCompany 3
20Company 4YesCompany 3
21Company 5NoCompany 3
22Company 6NoCompany 3
23Company 7YesCompany 3
24Company 8NoCompany 3
25Company 9YesCompany 3
26Company 10NoCompany 3
27Company 11NoCompany 3
28Company 12NoCompany 3
29Company 13NoCompany 3
30Company 14YesCompany 3
31Company 15NoCompany 3
Bid Summary
Cell Formulas
RangeFormula
R17:R31R17=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)
 

Attachments

  • Screenshot 2021-01-29 110436.png
    Screenshot 2021-01-29 110436.png
    31 KB · Views: 1

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,924
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
OPQR
16BiddersBidding?(Cells In Other Sheet In Workbook)
17Company 1NoCompany 3
18Company 2NoCompany 4
19Company 3YesCompany 7
20Company 4YesCompany 9
21Company 5NoCompany 14
22Company 6No
23Company 7Yes
24Company 8No
25Company 9Yes
26Company 10No
27Company 11No
28Company 12No
29Company 13No
30Company 14Yes
31Company 15No
Main
Cell Formulas
RangeFormula
R17:R21R17=FILTER(O17:O50,Q17:Q50="Yes")
Dynamic array formulas.
 

Moose2895

New Member
Joined
Jan 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you for your input! This is interesting, I've never used the Filter function before. I learned something today!

I should have been a bit more specific I suppose. The cells in the other Sheet that I'm trying to fill are not in the spill range of dynamic array formulas (as far as I know, unless you can specify which cells to spill into somehow). The other Sheet is a blank template of our company's proposal form that we send to our customers that contains our bid price for construction projects. The Sheet contains six of these blank templates side by side. If you take a look at the screenshot I've attached, we manually fill in the name of the company we are sending our proposal to on each of the six templates. I was just trying to figure out a way to have the cell to the right of the "Contractor:" cell display the name of the company we are bidding to by referencing the data from the O and Q columns on the other Sheet without repeating. I'm no whiz with Excel but I'm sure it can be done with an extremely long IF or IFS function. I was hoping to not have to write that formula out haha.

Hope I'm explaining it alright..

Thanks again for your response!
 

Attachments

  • Screenshot 2021-01-29 142329.png
    Screenshot 2021-01-29 142329.png
    4.2 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,924
Office Version
  1. 365
Platform
  1. Windows
Sorry but you totally lost me.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,924
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Had a rethink, on the 1st template use
Excel Formula:
=INDEX(FILTER('Bid Summary'!O17:O50,'Bid Summary'!Q17:Q50="Yes"),1)
on the 2nd sheet change the final1 to a 2 & then a 3 etc.
 
Solution

Moose2895

New Member
Joined
Jan 29, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Had a rethink, on the 1st template use
Excel Formula:
=INDEX(FILTER('Bid Summary'!O17:O50,'Bid Summary'!Q17:Q50="Yes"),1)
on the 2nd sheet change the final1 to a 2 & then a 3 etc.
That did it! Thank you very much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,924
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,256
Messages
5,641,144
Members
417,195
Latest member
Vishal kumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top