Transfering data if condition is met, or something like that help... 1

[TonyU]

Hello again all,

I earlier received helpful assistance with a formula (=IF(AND(C11=&quot;&quot;,D11=&quot;&quot,&quot;&quot;,IF(D11<C11,&quot;U&quot;,&quot;F&quot))from xlbo, luceze and Wray69 and now I have another dilemma

See sample below

If someone could provide a link on how-to or solution to this I would appreciate it.

 

[Zathras]

One way is to use Auto-Filter:

1. Click on &quot;Flag&quot;
2. Select Data/Filter/AutoFilter from the menu
3. Click on the drop-down button and select &quot;U&quot;
4. Select the rows to copy
5. Paste the copied rows wherever you want them
6. Go back to the main sheet and de-select Auto-Filter

You may actually want to leave Auto-Filter on for your users to enjoy. It's a very powerful and nice feature.

 

[TonyU]

Thanks Zathras, I'll try it and get back to you...

 

[TonyU]

Z, I tried doing what you suggeted but I can't get to copy the row needed..

One thing I'm not clear on is:
Do I apply the auto-filter to the report sheet or the first sheet of the book?

 

[Zathras]

Apply the auto-filter to the data table displayed beneath the caption &quot;Administration Sheet&quot; in your original picture.

The idea is to hide all the rows that don't have the letter &quot;U&quot; in the &quot;Flag&quot; column so that you can easily select and copy the rows you want. Then switch to the report sheet and paste as usual. (Or paste values, if you prefer.)

 

[PenelopeC]

If your data is rolled up into a filter, go to Edit, GoTo, Special -- then click the Visible Cells Only radio button. Then click Copy. This way Excel only copies the rows filtered, not all the rows of data you will get if you just do a Copy/Paste.

Let me know if this isn't clear.

PenelopeC
~~~>-/O~~~~~swimming right along

 

[TonyU]

Ok, your suggestions worked great. Except, I did do a good job in explaining what I'm trying to do.

Based on what I got now the data with &quot;U&quot; will copy over fine, the problem is that if the Administration sheet changes the report does not, and this is what I'm trying to accomplish. Only those that change to &quot;U&quot; need to be copied to the report dynamically...

 

[Zathras]

Ok, forget about auto-filter. Sounds like what you want is advanced filter. Here is a quick and dirty way to do it:
Set up a test workbook this way. (I tried to use your data in the cells where you have it as best I could.) You should then be able to adapt the code to your needs:

Sheet1:[blue]

B10: 'Office Expenses
B11: 'Equip Lease
B12: 'Equip Lease2
B13: 'Equipment
B14: 'Equipment Repairs
B15: 'Expenses
B16: 'Expense3
B17: 'Supplies
C10: 'Actual
C11: '157 
C12: '3400 
C13: '54268 
C14: '2513 
C15: '5747 
C16: '138 
C17: '1041 
D10: 'Budget
D11: 482
D12: 23800
D13: 54268
D15: 15000
D16: 1700
D17: 12000
E10: 'Balance
E11: =D11-C11
   (copy E11 to E12:E17)
F10: 'Flag
F11: =IF(AND(C11=&quot;&quot;,D11=&quot;&quot;),&quot;&quot;,IF(D11<C11,&quot;U&quot;,&quot;F&quot;))
   (copy F11 to F12:F17)
H10: 'Flag
H11: 'U

[/color]
Format columns C, D, and E as currency.
Now put this macro in a code module and run it:
[blue]

Option Explicit

Sub Macro1()

[green]

'
' Macro1 Macro
' Macro recorded 7/15/03 by Zathras
'

[/color][/code]
Sheets(&quot;Sheet1&quot.Activate
Range(&quot;B10:F17&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(&quot;H10:H11&quot, _
CopyToRange:=Range(&quot;J10:N10&quot, Unique:=False
Range(&quot;J10:N72&quot.Select
Selection.Copy
Sheets(&quot;Sheet2&quot.Select
Range(&quot;C5&quot.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range(&quot;C5&quot.Select
End Sub
[/code][/color]
Look in the help file for details about the advance filter and its features.

 

[TonyU]

Thanks Zathras, I will try it now and let you know

 

[TonyU]

Well, Thank you very much Zathras for all your help.

As of today (July 16th 3:00 pm) I have lost my job.

The company I worked for announced that due to recent lossess in profits they had no choice but to seriously consider cutting down some expenses. And I was the first to go after 6 years with the company. (I smell something fishy here, but anyway.)

Thanks again Z

 

[CDN]

sorry about that TonyU
keep ur head up it'll be ok

 

[Zathras]

TonyU, sorry to hear that. I wish I could think of something clever to say. Here's wishing you great success in the future.

 

[TonyU]

Well, after speaking today with a couple of friends (ex co-workers) they just told me that it was not just me that was let go, but 8 employees in total from different departments. Eventhough it's not a good feeling to be layed off at least it help knowing that one was not the only target and or was because it was something one did but a company employee cleaning spree... oh well

Thanks Zathras