• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selection criteria

F

Falinaicare

Member
Dear Excel expert,

How to make the selection criteria, that I want to select any one criteria rather than I have to select the 3 criteria?

Kindly advise with thanks.
 

Attachments

  • Machine Status.xlsm
    17.8 KB · Views: 7
Dear Narayan,

Great ninja in excel. Thanks for your sample test. It's really help me with my assignment.

The queries codes as below:-
=IF(ROWS(I$2:I7)<=$F$11, INDEX(Table1[Machine No],SMALL(IF(Table1[Machine Status] = IF($F$2 = "Any", Table1[Machine Status], $F$2),IF(Table1[Version] = IF($F$5 = "Any", Table1[Version], $F$5),IF(Table1[Country]=IF($F$8 = "Any", Table1[Country], $F$8),ROW(Table1[#Data])-MIN(ROW(Table1[#Data]))+1))),ROWS(I$2:I7))),"")

1) May I ask what is the meaning of using the [#Data]?

Kindly assist with thanks.
 
Dear Ninja,

I have a problem with the image below. I got the first results, but not the rest. Why it shows #NUM?

Could you advise me?

Thanks
.Capture.PNG
 
Hi ,

What is $F$11 displaying ?

Is it possible the formula might not have been entered as an array formula , using CTRL SHIFT ENTER.

Can you upload your workbook with this data in it ? The earlier version does not have this data.

Narayan
 
Hi,

Sure, I was practicing using your earlier sample excel which is good, but I can't find where is my error after I am using different data.

Could you tell me, where is my missing part?

Thanks.
 

Attachments

  • Sample Test (1) (2).xlsm
    18.9 KB · Views: 4
Hi Ninja,

I have attached another workbook.

My Questions are:-
1) There are many different tabs of Mall, how can I select any one selection, e.g. Machine status, version or location and it still view at the Masterlist spreadsheet?

Hope to hear from you soon.
 

Attachments

  • Different tab to Masterlist.xlsm
    19.9 KB · Views: 2
Hi ,

Having the data in separate tabs makes data extraction through formula much more difficult.

VBA can help.

If the data is together in one table , then data extraction is simple.

The workbook you have uploaded does not contain the data validation lists ; can you include them and then upload the revised workbook ?

Narayan
 
Hi,

Apologized on the data validation. I have updated into the excel.

Is the VBA coding simple?

Thanks.
 

Attachments

  • Different tab to Masterlist.xlsm
    20.8 KB · Views: 4
Hi Ninja,

Wow, you are super ninja. I was going to faint when I saw the codes at the visual basic. A good option of reset button. Just wondering, it is normal to have a blinking button every time I select the data validation?

Secondly, if this test save. Xlsm is put on our servers, will it be slow when the data is increasing and the tab spreadsheets as well?

Kindly advise with thanks.
 
Hi ,

Blinking is not easily done in VBA.

It is easier to change the colour of the cell where a data validation selection has been done. Is this acceptable ?

Narayan
 
Hi Ninja,

Your suggestion is good with the color of the cell where a data validation selection.

How do you change the color of the cell in excel? Is it by conditional formatting?
Could you show us some sample from the testsave.xlsm?

Kindly advise with thanks.
 
Hi Ninja,

Thank you for your attachment.

I will have to work both samples that you have created earlier.

Selection criteria

Dear Excel expert, How to make the selection criteria, that I want to select any one criteria rather than I have to select the 3 criteria? Kindly advise with thanks.
chandoo.org

Selection criteria

Dear Excel expert, How to make the selection criteria, that I want to select any one criteria rather than I have to select the 3 criteria? Kindly advise with thanks.
chandoo.org

Will ask you if any questions.

Many thanks.
 
You must log in or register to reply here.
Back
Top