Transferring Data Automatically between sheets.

[Boots6]

Hello, I'm trying to create a macro or even just a formula that will automatically move information from one tab to another if it is a certain kind of expense. I understand that I can do an If Then to get the information moved over but I want the chart to not have to read down every column and put a "0" if it doesn't contain the right code.



For example, I have =IF('Credit Card Expense'!E12="6575",'Credit Card Expense'!A12,0). I can copy this down and across the sheet, but then there will be 0s in the rows that do not contain code 6575. Is there a way to get the chart to input information in consecutive rows only if information is present? Also, I'm having a problem when I enter the code above, "A12" is a date on the Credit Card Expense tab. When it transfers over to my new tab, it reads as a different date. How do I get it to copy exactly what the original cell says? Thanks.

 

[SkipVought]

Hi,

Have you checked out either PivotTables or MS Query? faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boots6]

I don't want MSQuery or anything but a VBA code. Thanks,

 

[SkipVought]

Well, that's like someone building a delicate fretwork, saying, "I don't want to use a scroll saw, or anything but a 5 hp industrial table saw. Thanks"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boots6]

Whatever

 

[SkipVought]

Turn on your macro recorder and do what you want to do.

Then you will have some code.

Then post BOTH your question & code in forum707, where VBA code questions are much better addressed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boots6]

Sorry - thought you were like an advertisement person or something. That's the 2nd time someone has said Query stuff and I've tried to use it and just don't understand. Plus this is a company wide excel, so I just want people to be able to hit command+shift+ whatever and have it run this process. Thanks anyway.

 

[SkipVought]

That is EXACTLY what MS Query does, with much less fuss than your self-imposed process requiremnt will require.
[highlight]
It could accomplish in about 30 seconds![/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Boots6]

How does it work? I tried before and didn't get it.

 

[SkipVought]

Let's take a step back and explore what it is that you are attempting to accomplish.

You have some kind of expense form?

If yes, then don't you want to do some sort of aggregation of the 6575 rows? You probably could use the SUMIF function. That's my guess.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

Is there a way to get the chart to input information in consecutive rows only if information is present?
Yes, use an IF statement.
Something like this...

=[b][red]IF('Credit Card Expense'!E12 <> "",[/red][/b]IF('Credit Card Expense'!E12="6575",'Credit Card Expense'!A12,0[b][red],"")[/red][/b]).

How do I get it to copy exactly what the original cell says?
When you copy a formula, Excel adjusts cell references.
For example, if you have [blue]=A1 + A2[/blue] in cell A3
and copy that formula to cell B3, it will adjust to [blue]=B1 + B2[/blue]

If you want it to keep using A1, select the A1 in your formula and press the F4 key.
This will change your first formula to [blue]=$A$1 + A2[/blue]. The copied formula in B3 will now be [blue]=$A$1 + B2[/blue]

Randy