array formula

The problem with the data filters, that has to be done with each queery. I want to design the worksheet so that anyone can go into it and enter the worksheet a1 value and it will spit out the results with no filter required...

Try this, it does not look exactly like what you had in mind as I split column B into 2 columns to make it easier for the formula.


HTH

It is almost right however it doesn't seem to search if I only use cell A1. It is requiring me to enter a value in A2 as well. Is there a way to allow the formula to search by A1 if A2 is blank so that it would only search by value?

I am not following how you did a few steps on the formla. am I anble tojust copy the formula and past into wksht2 B5if I have 5 values in the table

I should be able to used conditional formating for the worksheet if I want the value #NUM to be "blank" by making that text white right?

Try this.

HTH

Can I ask one more quick question in regards to your awesome, amazing SS? Do I have to have the number identifier cell in column A on worksheet 2 and 3 or would the Row command (or some derivitive work)

Just curious... I can easily hide that. This is fantastic. I just hope I can replicate it in the real final version!

Yes, it is possible to replace column A with the function ROW(). Recently, I try to avoid providing that for people, esp'ly for people who don't know Excel very well. You would be fine as it appears that you have decent knowledge in Excel. However, for someone who doesn't know Excel very well or doesn't know how the formula is setup, it can be troublesome.

Let's say in the future someone needs to modify the spreadsheet to suit some new application and he/she needs to add a new header row. It will mess up all the formula since they are referencing the row number. Thus, adding new row at the top will push everything down one row and the formula would not work properly. If the person doesn't realize this change, what he/she gets at the end is one less output (missing one product). Thus, I don't recommend it and stopped providing this for people.

I'm so sorry to keep bugging you your test spreadsheet worked amazing..... but now I think I broke your formula somehow. What am I doing wrong here. I am trying to expand the index that the results are based off of. 4 was an easy test example but in reality there may be 100's listed.

All I did was add the new values into the index (names results key), then I adjusted the formula to include the new rows on the results key worksheet.... Now I am getting a #N/A.

When I made the adjustment I pressed Shift-Control-Enter as I saw it was an array.

I've attached the SS I've created from your helpful template.

You cannot have arrays that are different in sizes. If you have a total of 6 products, the maximum you can assign (meet the criteria) to company ABC is 6.

Shouldn't I just be able to change the index range to match the index tab in the formula wherever "Results Key" appears?

Ie if there are 6 products in the results key tab shouldn't the formula read:

=INDEX('Results Key'!$D$1:$D$6,SUMPRODUCT(SMALL(--('Results Key'!$B$1:$B$6<='Data Information'!$B$4)*IF('Data Information'!$B$5="",1,--('Results Key'!$C$1:$C$6='Data Information'!$B$5))*$A$3:$A$6,SUMPRODUCT(--('Results Key'!$B$1:$B$6<='Data Information'!$B$4)*IF('Data Information'!$B$5="",1,--('Results Key'!$C$1:$C$6='Data Information'!$B$5))-1)*-1+A3)))

You can change the index range ('Results Key'!$D$1:$D$6), but arrays inside SUMPRODUCT() need to be the same size. ie. in your test5 file, "$B$1:$B$6", "$C$1:$C$6", and "$A$3:$A$23" must match.

So should it be A3:A8? That refers to the results page right? Or is it results key A1:A6? I think that's where the formula is messing up.

Yes it should be A3:A8. Perhaps I had you confused when I put the "item#" in sheet3 (your Results Key sheet), that column is not used anywhere in the formula.

Let's try again.

Seems to work now. What did I do wrong?

I added another condition to the formula to look for blank spaces. As of now, the formula will see up to 100 items. Modify to suit your application, and of course larger the array longer it takes to finish the calculations.

Thank you so much for your assistance!

Sadly I need more assistance on this.....

I showed the outcome to my peers and they came back with requested enhancements to "put it up a notch"

I am not even sure if it can be done.... Ive attached the working SS.

The enhancements I need to make are as follows:

1) On the results tab, I need not only the description ( pulled from the results key tab) but also the cost as shown on the results key tab column E.

2) I need to know if there is a way to include a new search function to exclude a wellness code or codes. Currently on Data information B5 the code narrows down to include only that code. Is there a way to do the reverse ie exclude only that code.

Perhaps it would be easiest to have those codes pull a result onto a seperate tab which would then be the new results key.....

3) Does anyone know if there is a form that would allow me to customize how the results tab looks?

I know this is all pretty overbearing and possibly confusing so maybe we can tackle then one by one...


Just as background, the wellness profile is the source data. This pulls to the results key to form an index which is narrowed down from the data information tab lines B4 and possibly B5. The end result is on the wellness results tab.

Its a pretty neat workbook (Thanks again Morrigan!) that apparently needs to be "amped up".

Try this, I had to add a helper column to make things simpler. You will have to do some formatting. As of now, it does not look very professional~


HTH

If 2 entries in "Results Key" column D are the same, that formula will output the first one but not the second.