multiple values

[Browne.t]

HI Everyone, I have this old sheet that works fine, however it has been decided that 2 additional values need to be added for an item to display. The below formula works with this Ctrl+Shift+Enter setup:
=IFERROR(INDEX(Participation!A:A;SMALL(IF(INDEX(Participation!K$5:T$228;;MATCH(A$1;Participation!K$4:T$4;0))="NVD";ROW(Participation!K$5:T$228));ROWS(B$1:B1)));"")
Now, next to "NVD", items should also display when the cell contains STOPPED or GK.

Thanks.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[Browne.t]

File attached. Formula on tab 2 to 10. All linked to fist tab. Should include "Exchange" too within values.

[Richard Buttrey]

Thanks, but you missed off the mst important point - i.e. some manually created results, plus some explanation of what you mean by 'Exchange'

But before we go too far with this layout, why do you choose to spread your database across ten tabs?
I believe you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd consider creatin a single sheet database that contains the following columns

The current G:J (excluding the blank column C)
Opportunity Number - or maybe not since Opprortunity #2 seems to be derived from #1
Resit Number - i.e number 1, 2 , 3 ...etc
Resit Value