Export based on cell contents

  • I have a number of tabs in a worksheet. I want to create the data in a new tab on the same worksheet based on the data that is entered in the other tabs.


    For example:


    Tab 'Business'
    Column A Column B Column C Column D


    R1 Expense A 50.00 25.00 6/8/4
    R2 Expense B 60.00 35.00 3/8/4
    R3 Expense A 50.00 130.00 9/8/4
    R4 Expense C 120.00 25.30 4/8/4


    I would now like to create more tabs, called Expense A, Expense B, etc and bring in the data from the other tabs based on the 'Expense' cell.


    Hope this makes sense!


    thanks

  • Hi,


    Add new sheet


    in cell A2
    =IF(COUNTIF(Business!$A$1:A1000,$A$1)>=ROW(A1),INDEX(Business!$A$1:$D$1000,SMALL(IF(Business!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),COLUMN(A1)),"")


    holding down Ctrl+Shift keys and press Enter to get out from the formula bar


    select cell A2 and drag to the right up to cell D2, then drag down as much as you need.


    Set the criteria in cell A1 e.g. Expense A


    copy entire sheet and add another sheet and paste, then set A1 again e.g. Expense B


    note: if you need to expand the data range, you must expand every 1000 in the formula and they must be identical.


    hope it helps

  • Thanks Jindon,


    It works for the first line in the new sheet but for some reason, the lines below have a number error. I've copied the formulae that is now for the following lines just to see if you can see where I've gone wrong.



    "=IF(COUNTIF(Sept03!$A$1:A1000,"ExpenseA")>=ROW(A2),INDEX(Sept03!$A$1:$D$1000,SMALL(IF(Sept03!$A$1:$A$1000="ExpenseA",ROW($A$1:$A$1000)),ROW(A2)),COLUMN(A2)),"")

  • Hi,


    Really sorry that I had mistake in my formula,


    should be


    =IF(COUNTIF(Business!$A$1:$A$1000,$A$1)>=ROW(A1),INDEX(Business!$A$1:$D$1000,SMALL(IF(Business!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),COLUMN(A1)),"")


    please note: the formula above must be in the first row which you desired to display the data from. it doesn't matter of which ROW. If you start to display the data from A3, put the formula in A3. Row(a1) and column(a1) are not related to row and column, they are substitute of numbers and they increase as you drag. If you want to change the sheet name and criteria, YES, you can but others, absolutely NOT.


    regards,
    jindon

  • Hi Jindon,


    I've copied the formula as you've said, but I still appear to be doing something wrong. On the formula I sent you back, had I amended the formula with the 'Expense A' and the Sept03' in the correct places?


    When I've amended the one you've sent me, it brings in for the row i've put it the Expense B and it also doesn't copy down and I get the num errors or just blanks.


    thanks for all your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!