Have absolutely no idea what formula to use

[17670457]

ok so next question for you lovely people
below is a sample of my data.
What I want to do is copy every row where the location ID=M AND the Purchase Date is after 30/06/2013
Its ok if they dont copy under each other i can use filters to filter out blank rows.
If its too hard to get the whole row to copy, just the asset ID and the final carrying amount would suffice.

Asset ID	Asset Item	Asset catergory code	Asset category	Location code	Location	Cost price	Purchase date	Depreciation method	Depreciation rate	Useful life	Depreciation expense (1/07/12 to30/06/13)	Accumulated Depreciations (as at 30/06/13)	Carrying Amount (as at 30/06/13)	Depreciation expense (1/07/13 to30/06/14)	Accumulated Depreciations (as at 30/06/14)	Carrying Amount (as at 30/06/14)	Depreciation expense (1/07/14 to30/06/15)	Accumulated Depreciations (as at 30/06/15)	Carrying Amount (as at 30/06/15)	Disposal date	Disposal value
C001	DELL computer ST C20167	C	Computer	A	Adelaide	$100,000	1/08/12	Diminishing value	33.33%	4	$30,407.92	$30,407.92	$69,592	$23,195.04	$53,602.96	$46,397.04	$3,855.44	$57,458.40	$0.00	31/03/15	$5,200
C002	DELL computer ST C20168	C	Computer	A	Adelaide	$5,600	1/08/12	Diminishing value	33.33%	4	$1,702.84	$1,702.84	$3,897	$1,298.92	$3,001.77	$2,598.23	$185.06	$3,186.83	$0.00	13/04/15	$291
C003	DELL computer ST C20169	C	Computer	A	Adelaide	$7,800	1/08/12	Diminishing value	33.33%	4	$2,371.82	$2,371.82	$5,428	$1,809.21	$4,181.03	$3,618.97	$1,206.20	$5,387.23	$2,412.77
C004	DELL computer ST C20170	C	Computer	A	Adelaide	$10,000	1/08/12	Diminishing value	33.33%	4	$3,040.79	$3,040.79	$6,959	$2,319.50	$5,360.30	$4,639.70	$1,546.41	$6,906.71	$3,093.29
C005	DELL computer ST C20171	C	Computer	A	Adelaide	$5,500	1/08/12	Diminishing value	33.33%	4	$1,672.44	$1,672.44	$3,828	$1,275.73	$2,948.16	$2,551.84	$850.53	$3,798.69	$1,701.31
C026	Cannon laser printer CLP1	C	Computer	A	Adelaide	$3,500	1/08/12	Diminishing value	33.33%	4	$1,064.28	$1,064.28	$2,436	$811.83	$1,876.10	$1,623.90	$541.24	$2,417.35	$1,082.65
C022	Dell Printer/photocopier PP1	C	Computer	A	Adelaide	$8,900	10/08/12	Diminishing value	33.33%	4	$2,633.16	$2,633.16	$6,267	$2,088.74	$4,721.90	$4,178.10	$1,392.56	$6,114.46	$2,785.54
E001	Cutting machine CM1	E	Equipment/machine	A	Adelaide	$100,000	1/08/12	Straight line	20.00%	5	$18,246.58	$18,246.58	$81,753	$20,000.00	$38,246.58	$61,753	$8,164.38	$46,410.96	$0	1/02/15	$10,000
E004	Packing machine PM1	E	Equipment/machine	A	Adelaide	$150,000	1/08/12	Straight line	20.00%	5	$27,369.86	$27,369.86	$122,630	$30,000.00	$57,369.86	$92,630	$12,246.58	$69,616.44	$0	1/02/15	$6,300

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col span="5"><col><col></colgroup><tbody>
</tbody>

Please and thankyou
Jo

 

[mole999]

Re: Have apsolutely no idea what formula to use

could it be sorted in to that order then they would all be together and you just need to copy the headers over

 

[17670457]

Re: Have apsolutely no idea what formula to use

yeah i know i could do it that way but I need it to be automatic.
I was thinking the If function might work but I cant get the date bit right,

IF(Location code="M", IF(Purchase Date Is after 30/06/2013), sheet1A2, otherwise " ")
so in the second row then copied downwards.
IF(Sheet1!E1="M",IF(H2>(30/06/2013)),"Sheet1!E1"," ")

i think its got wrong bits in it but i think that would work if it was fixed

so on the new sheet i would end up with many empty rows and only the ones i want will have values, and i can just filter out the empty ones.

 

[dreid1011]

Re: Have apsolutely no idea what formula to use

At first glance, there is a ) out of place.

This:
=IF(Sheet1!E1="M",IF(H2>(30/06/2013)),"Sheet1!E1"," ")

Should be:
=IF(Sheet1!E1="M",IF(H2>"30/06/2013",Sheet1!E1," "))
Move the red ) and change the blue ()'s to "" and remove the blue ""s.

Also, you could combine the two IFs into an AND like this:
=IF(AND(Sheet1!E1="M",H2>"30/06/2013"),Sheet1!E1," ")

 

[17670457]

Re: Have apsolutely no idea what formula to use

sorted, just used the if function to only copy the M location then used the filter to filter the purchase date

 

[dreid1011]

Re: Have apsolutely no idea what formula to use

Glad you found a solution.

 

[17670457]

Re: Have apsolutely no idea what formula to use

OMG i feel the fool, turns out there is an even easier way, i didn't even know existed but am now proficient at.....PIVOT TABLES, sooooooo useful

 

[mole999]

Re: Have apsolutely no idea what formula to use

if you use a named range in your pivot tables and extend the range to more than you currently need, then when you add more data it will be accommodated, just make sure you refresh your data source before relying on them