• 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.

fetch data if cell value is specific

A

Ajinkya

Member
Dear Friends,


I have sample sheet which is uploaded on following path....


https://hotfile.com/dl/179686300/89b7266/fetch_data_if_cell_value_specific.xlsx.html


where i am looking to fetching data from "data sheet" into the "report sheet" if the data sheet's "column R" met with value "1" as like as highlighted cells in the report sheet.


thanks for the same...

:)

ajinkya
 
Hi Ajinkya,


Do you want to fetch all the data(from Col B to Col H) from "Data" sheet if the values in Col R of data sheets = 1.


Below here is the formula for fetching the values of Col Q of "Data" sheet based on the criteria. Write the formula at report sheet (say at I3) and press CTRL+SHIFT+ENTER (not just enter), and drag it down as needed:


=IFERROR(INDEX(data!$Q$2:$Q$13,SMALL(IF(data!$R$2:$R$13="1",ROW(data!$R$2:$R$13)-1,""),ROW(A1))),"")


Hope this helps.


Kaushik
 
Ok I missed your comment regarding highlighting cells. Here is all the formula:


At G3:

=IFERROR(INDEX(data!$J$2:$J$13,SMALL(IF(data!$R$2:$R$13="1",ROW(data!$R$2:$R$13)-1,""),ROW(A1))),"") CTRL+SHIFT+ENTER (not just enter)


At F3:


=IFERROR(INDEX(data!$E$2:$E$13,SMALL(IF(data!$R$2:$R$13="1",ROW(data!$R$2:$R$13)-1,""),ROW(A1))),"") CTRL+SHIFT+ENTER (not just enter)


At E3:


=IFERROR(INDEX(data!$D$2:$D$13,SMALL(IF(data!$R$2:$R$13="1",ROW(data!$R$2:$R$13)-1,""),ROW(A1))),"")CTRL+SHIFT+ENTER (not just enter)


At C3:


=IFERROR(INDEX(data!$B$2:$B$13,SMALL(IF(data!$R$2:$R$13="1",ROW(data!$R$2:$R$13)-1,""),ROW(A1))),"")CTRL+SHIFT+ENTER (not just enter)


Kaushik
 
Info on how these types of formulas work:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/
 
Dear Ajinkya,

If you want to avoid array formula, you can follow simple INDEX+MATCH Formula.

1. In S2 of the Sheet DATA Type the formula =IF(R2="1",COUNTIF($R$2:R2,1),"") and copy down.

2. From A3 type serial number (A3=1, B3=2, C3=3........)

3. Type the formula in B3 =INDEX(data!$A:$Q,MATCH($A3,data!$S:$S,0),MATCH(B$2,data!$A$1:$Q$1,0))

Copy B3 and paste destination cells (B3:H20)


Done!


Regards,


Muneer
 
Hi Ajinkya,


This is with reference to your sheet, enter in B3 on Sheet: Report and drag to right and down:


Code: 
=OFFSET(data!$A$1,SMALL(IF(data!$Q$2:$Q$13<=0,ROW(data!$Q$2:$Q$13)),ROW(A1))-1,CHOOSE(COLUMN(A1),0,1,2,3,4,9,16,17))


Press Ctrl+Shift+Enter, hopefully just one formula for all your needs. ;)


PS: I replied to your email. Plz check. :)


Regards,

Faseeh
 
Hello friends,


gud morning.


Thanks, just saw your guidelines, as per latest of Faseeh's formula have tried, Thanks dear its working.


also thanks Kaushik, Sir Luke M and nazmul muneer for your valuable help, let me go through it, dam sure these also definitely work, but sure these all shall take some time to digest :)


thanks,

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