# 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:

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,

in cell A2

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

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.

• Hi,

Make sure that holding down Crtl and Shift keys and press Enter to get out of the formula bar after you paste the formula.

It will give the formula bracket { } automatically.

jindon

• Hi Jindon,

I've attached my spreadsheet so perhaps you can see what i'm doing wrong!

thanks! (I did do the ctrl, shift, enter and it gives me the message 'which workbook do you want to up date).

Thanks!

## Files

• Yap!

Just select cell A1 and click on the formula bar, then
press Enter while you holding down Ctrl and Shift keys.

## Files

• Ah ha! Now I see!

Sorted and it works perfectly. thank you so much!

## Participate now!

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