Forum Discussion

deecoy's avatar
deecoy
Copper Contributor
Nov 27, 2019

Excel formulaqs and functions

I am trying to transfer data from one master file to another sub file(tab) within the same file, using the formula:  =INDEX(Master!$A4:$A$2400,MATCH($A$35,Master!$C4:$C$2400,""),1) where col A4 cont...
excel
Formulas and Functions
Deecoy88's avatar
Deecoy88
Copper Contributor
Nov 28, 2019

SergeiBaklan 

Thanks Sergio for your offer of assistance.  I've attached a sample file.

The Master file contains the daily egg collections by egg # and pen#.

The sub file A1 pulls the data from the Master file from Pen A1 into the A1 file, which works as you can see.

The problem is that there will be many blank cells (2-5,000 rows) where the egg is not from pen A1, but where the data will go to other sub files (ie A2,A3 etc).  The filter function seems to only work with data already in A1,  but not for new egg data generated, so I'm trying to find a formula to sequentially put new data in the next row in A1(to consolidate the data in A1), rather than have all the blank rows of data in between each row.

Your thoughts/suggestions will be greatly appreciated.

Thx again.

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 28, 2019

Deecoy88 

If to locate result as

you may add to D5 formula

=IFERROR(INDEX(Master!$A$2:$A$20000,AGGREGATE(15,6,1/(D$4=Master!$B$2:$B$20000)*(ROW(Master!$B$2:$B$20000)-ROW(Master!$B$1)),(ROW()-ROW($D$4)))),"")

drag it to the right and when entire row (D5:E5 in this case) down till empty cells appear. Or more for future expanding of the source data.

Using of absolute/relative and proper references is critical.

  • Deecoy88's avatar
    Deecoy88
    Copper Contributor
    Nov 29, 2019

    SergeiBaklan 

    Hi Sergei,

    I copied your formula into cell D5 and dragged formula over to E5 cell and down the rows.

    Attached is my result (all zeros), unlike your result.  What am I doing wrong? Many thanks.

Resources