[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need help with formulas in Excel 200 spreadsheet

Posted on 2009-12-31
5
Medium Priority
?
375 Views
Last Modified: 2013-12-08
I am having an issue with a complicated spreadsheet.  I created a smaller example version which is attached.   Although the attached shows three sets of lookups (as examples), the actual sheet would only contain the J1...K5 list.  The J column is Priority and the K column are container types.  Using drop downs, you can change the container priority order.

The data starts out in A8...J15.  I need a formula in column G that:

1.  Checks Priority 1 entry in Column K
2.  Matches that to the column heading on Row 8
3.  Checks for a "Y" where the Column/Row intersect
4.  If "Y"is found, put the Priority 1 Entry in Column G for that row
5.  If "N" is found, go to Priority 2 Entry and check for a Y.  Keep going until Priority list is exhausted
6.  Once a "Y" is found, stop checking - only show the first match in Column G
7.  If there are no "Y"'s on the entire priority list, put "No Fit" in "G"

Column G would need to be dynamic.  If the priority list order is changed, it should automatically change the calculation in each row.  I did a couple example priority lists and showed the corresponding results.  I am not too good with complicated formulas, I am hoping someone out there can help.

Thanks!

Joe
Container.xlsx
0
Comment
Question by:netwrked
  • 3
5 Comments
 
LVL 19

Expert Comment

by:david251
ID: 26154903
Have a look at the attached file.

I created range names for each of the colors.

Select a color in cell a7, and b7:f7 will update the priority and the g9:g15 will update

I hope this helps, let me know if you need anything further.

-David251
Container.xlsx
0
 

Author Comment

by:netwrked
ID: 26155298
Wow, that was fast.  I had never heard of Index or Indirect.  Is there any way to do it without the row 7?  On the actual sheet, there are no colors, only J1:K5.  I only used the other colors as examples to show various outputs based on various prorities.  The actual sheet would only check column K, no colors or other lookup columns.

I really appreciate the fix, this is incredibly close.

Joe
0
 

Author Comment

by:netwrked
ID: 26155308
Just noticed one other thing, the formula on Row 14 is different then the rest.  There are thousands of rows in the actual sheet and "No Fit" is a possibility on any of them.  Is it possible to include the No Fit check into all formulas and not lose functionality of the other formulas?

Thanks again,

Joe
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 26157894
Hello Joe,
Try this revison of david251's formula in G9
=IF(COUNTIF(B9:F9,"Y"),INDEX(K$1:K$5,MIN(MATCH(IF(B9:F9="Y",B$8:F$8,K$5),K$1:K$5,0))),"NoFit")
confirmed with CTRL+SHIFT+ENTER and copied down
See attached sheet where I re-ordered K1:K5 to be in the order of your original "green" example, so the results in G9:G15 match the sample results you show in G29:G35
Note: formula refers to K$5 (in bold). That would always be the last cell of any range should you change the setup......
regards, barry

25012994.xlsx
0
 

Author Closing Comment

by:netwrked
ID: 31671786
That works perfectly, I truly appreciate the effort.

Joe
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question