# Selection criteria

#### Falinaicare

##### New 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?

#### Attachments

• 17.8 KB Views: 7

#### NARAYANK991

##### Excel Ninja
Hi ,

See the attached file.

Narayan

#### Attachments

• 18.3 KB Views: 16

#### Falinaicare

##### New Member
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.

#### Falinaicare

##### New Member
Dear Narayan,

Great links. Thank you very much.

#### Falinaicare

##### New Member
Dear Ninja,

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

Thanks
.

#### NARAYANK991

##### Excel Ninja
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

#### Falinaicare

##### New Member
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

• 18.9 KB Views: 4

#### NARAYANK991

##### Excel Ninja
Hi ,

See the attached file.

Narayan

#### Attachments

• 19.8 KB Views: 2

#### Falinaicare

##### New Member
HI,

I have a question as below:-

1) I have chosen the Machine Reading selection with Version V10, it still displays V2.

#### NARAYANK991

##### Excel Ninja
Hi ,

In the workbook I have it doesn't.

Narayan

#### Attachments

• 19.7 KB Views: 5

#### Falinaicare

##### New Member
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

• 19.9 KB Views: 2

#### NARAYANK991

##### Excel Ninja
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

#### Falinaicare

##### New Member
Hi,

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

Is the VBA coding simple?

Thanks.

#### Attachments

• 20.8 KB Views: 4

#### NARAYANK991

##### Excel Ninja
Hi ,

See the attached file. Test it out thoroughly.

In case you do not wish to apply a criteria to any field , you can either leave it blank or select the Any option.

Narayan

#### Attachments

• 45.3 KB Views: 6

#### Falinaicare

##### New Member
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?

#### NARAYANK991

##### Excel Ninja
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

#### Falinaicare

##### New Member
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?

#### NARAYANK991

##### Excel Ninja
Hi ,

See the attached file.

#### Attachments

• 46 KB Views: 3

#### Falinaicare

##### New Member
Hi Ninja,

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

Will ask you if any questions.

Many thanks.

#### NARAYANK991

##### Excel Ninja
Hi ,

No problems. Any doubts / modifications / extensions , please post here in this same thread.

Narayan