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


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