Lookup values through Interior Colors (Without VBA)

[Deepak Sharma]

Hi All,

I know excel doesn't read colors, But I also know that based on a criteria values can be looked up.

In the attached sheet, I need to pull green color data and without color (White Color) data from Sheet1 to Sheet2.

There is some Get.Cell function, But I am not able to use it properly. Please advice.


Regards,
Deepak

 

[bosco_yip]

Try...............



1] In D1, color criteria copy/paste from "ColorList" G2:G7

2] Define Name >>
>> Name : ColorMatching
>> Refers to : =GET.CELL(63,IF(1,+OFFSET(Sheet1!$A$1:$A$50,ROW(Sheet1!$A$1:$A$50)-MIN(ROW(Sheet1!$A$1:$A$50)),)))=GET.CELL(63,Sheet2!$D$1)

3] In "Sheet2" A2, copied across and down :

=IFERROR(INDEX(Sheet1!A$1:A$50,AGGREGATE(15,6,ROW(Sheet1!A$1:A$50)/ColorMatching,ROWS($1:1))),"")

p.s. : Get.Cell() is a Excel 4 Macro function, it is need to save file as xlsm or xlsb

Regards
Bosco

 

[Deepak Sharma]

Wow! Amazing Bosco !! Thank you so much!

Few things wanted to know:

1. What is ColorIndex in Name Manager is doing?

2. Is it possible to get data of two colors in one go? Like I need to pull data for Green & White color rows?


Thanks and Regards,

 

[bosco_yip]

Wow! Amazing Bosco !! Thank you so much!

Few things wanted to know:

1. What is ColorIndex in Name Manager is doing?

2. Is it possible to get data of two colors in one go? Like I need to pull data for Green & White color rows?


Thanks and Regards,

1] For Lookup 2 color criteria cells in D1 and D2, color criteria also copy/paste from "ColorList" G2:G7

2] Define Name >>

>> Name : ColorMatching

>> Refers to : =(GET.CELL(63,IF(1,+OFFSET(Sheet1!$A$1:$A$50,ROW(Sheet1!$A$1:$A$50)-MIN(ROW(Sheet1!$A$1:$A$50)),)))=GET.CELL(63,Sheet2!$D$1))+(GET.CELL(63,IF(1,+OFFSET(Sheet1!$A$1:$A$50,ROW(Sheet1!$A$1:$A$50)-MIN(ROW(Sheet1!$A$1:$A$50)),)))=GET.CELL(63,Sheet2!$D$2))

3] In "Sheet2" A2, formula copied across and down :

=IFERROR(INDEX(Sheet1!A$1:A$50,AGGREGATE(15,6,ROW(Sheet1!A$1:A$50)/ColorMatching,ROWS($1:1))),"")

4] See attachment

p.s. ColorIndex in Name Manager is used for extract the color index at the adjacent cell, for testing only, and doesn't used in the worksheet formula.

Regards
Bosco

 

[Deepak Sharma]

Wow!!! Unbelievable !! Fantastic Dude!
This is was something which was not possible without VBA, But you made it possible! Thank you so so much dear !!

Regards,