get select data

[rahulshewale1]

Dear sir,

Please find the attached sheet.data sheet has big data of receipe .i want get receipe data only yellow colour is hightlight only.



Thanks
Rahul shewale

 

[PCosta87]

Dear sir,

Please find the attached sheet.data sheet has big data of receipe .i want get receipe data only yellow colour is hightlight only.



Thanks
Rahul shewale

Hi Rahul

For that you can use conditional formatting, taking advantage of the fact that the cell below the recipe name always has the word "Ingredient" (or, to be precise, Ingredient + some spaces)

Add conditional formatting to cell "A1" using the following formula:

=$A2="Ingredient              "

Then copy formats and apply to the desired range... in this case "A1:A139" (or the entire column "A").

Hope it helps

 

[rahulshewale1]

hii ,

thanks for reply,but i want get data of particuller recipe name only please see "data sheet" and "Requirement like this sheet".i hope it will clear.

Thanks
Rahul shewale

 

[bosco_yip]

Try,

1] "DATA SHEET" Sheet, Helper column F2, formula copy down :

=IF((COUNTIF('REQ RECEIP NAME'!A$2:A$110,A2)>0)*(B3="Unit Price"),1,IF((F1=1)*(COUNTIF('REQ RECEIP NAME'!A$2:A$110,A2)=0)*(B3<>"Unit Price"),1,0))

2] "REQUIREMENT LIKE THIS" sheet, F1, formula copy across and down :

=IFERROR(INDEX('DATA SHEET'!$A$2:$D$139,AGGREGATE(15,6,(ROW('DATA SHEET'!$A$2:$D$139)-ROW('DATA SHEET'!$A$2)+1)/('DATA SHEET'!$F$2:$F$139=1),ROWS($1:1)),COLUMNS($A:A)),"")

Regards
Bosco

 

[Nebu]

Hi:

This is another cheeky way of doing it. I have used indirect formula to achieve this, select the drop down on cell J2 it will pull values from data sheet.

Thanks

 

[rahulshewale1]

thanks you very much bosco sir......

 

[PCosta87]

hii ,

thanks for reply,but i want get data of particuller recipe name only please see "data sheet" and "Requirement like this sheet".i hope it will clear.

Thanks
Rahul shewale

Hi, I totally misunderstood that one
@bosco_yip has it

 

[bosco_yip]

thanks you very much bosco sir......

Further improvement of formula.

Changed the 1st helper cell formula resulted from 1/0 into Serial no. 1,2,3… and the 2nd formula can be simplified.

1] "DATA SHEET" Sheet, Helper column F2, formula copy down :

=IF((COUNTIF('REQ RECEIP NAME'!A$2:A$110,A2)>0)*(B3="Unit Price"),MAX(F$1:F1)+1,IF((F1>0)*(COUNTIF('REQ RECEIP NAME'!A$2:A$110,A2)=0)*(B3<>"Unit Price"),MAX(F$1:F1)+1,0))

2] "REQUIREMENT LIKE THIS" sheet, F1, formula copy across and down :
=IFERROR(INDEX('DATA SHEET'!$A$2:$D$139,MATCH(ROWS($1:1),'DATA SHEET'!$F$2:$F$139,0),COLUMNS($A:A)),"")

Edit : By compare with the above 2 files memory used 57.00KB and 31.70KB,
it is noted that through the formula improvement, around 44.40% memory was saved.

Regards
Bosco