filter or pivot style by formula

**arctushar** · 06-04-2013, 01:29 AM

Hi
I have attached an excel file. Here "Specification" is the main data entry sheet. Now I want that in temporary sheet of the attached file Store Report Buildtrade.xlsx . I will input only the Challan number in green background cell. The date of of the challan will be automatically, I can do that. But I want that
Automatically it will pull data from specification sheet that challan number is as my givven challan number on sheet temporary. for example here I have input challan number 27009, and corresponding values r below. How can I do that ?
thanks

**:) Sixthsense :)** · 06-04-2013, 01:56 AM

In J1 Cell

Formula:

=IFERROR(VLOOKUP(C1,'Main store'!D:T,17,0),"")

In A4 Cell - Array Formula Requires CTRL+SHIFT+ENTER

Formula:

=IFERROR(INDEX('Main store'!$A$5:$N$756,SMALL(IF('Main store'!$D$5:$D$756=$C$1,ROW('Main store'!$D$5:$D$756)-ROW('Main store'!$D$5)+1),ROW($A1)),COLUMN(A$1)),"")

Drag it down....

In B4 Cell - Array Formula Requires CTRL+SHIFT+ENTER

Formula:

=IFERROR(INDEX('Main store'!$D$5:$N$756,SMALL(IF('Main store'!$D$5:$D$756=$C$1,ROW('Main store'!$D$5:$D$756)-ROW('Main store'!$D$5)+1),ROW($A1)),COLUMN(A$1)),"")

Drag it down and right....

Refer the attached file for details.