Copy on change of value

[Guest]

I have a list of unique accounts in column A on a worksheet "Budget".

I wish to copy each account number from the worksheet "Budget" to another
worksheet called "Data" over 12 rows in column A then when the value changes
copy the next value down the next 12 rows and so on until the next value is
blank or empty.

The result would be the list of accounts in Col A of "Budget" are repeated
12 times each in Column A of worksheet "Data".

Any help would be much appreciated.

 

[Guest]

Taking your request literally. ANY change in column A will result in the
action - even new additions at the bottom of the existing list. To put this
code into use, right click on the Budget sheet's tab and choose View Code,
cut and paste this into it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestRange As Range
Dim Offset1 As Long
Dim Offset2 As Long
Dim LC As Integer

If Target.Column <> 1 Then
Exit Sub
End If
Application.EnableEvents = False
Set DestRange = Worksheets("Data").Range("A1")
If IsEmpty(DestRange) Then
Offset2 = 0
Else
Offset2 = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row
End If
Offset1 = 0
Do While Not (IsEmpty(Target.Offset(Offset1, 0)))
For LC = 1 To 12
DestRange.Offset(Offset2, 0) = Target.Offset(Offset1, 0)
Offset2 = Offset2 + 1
Next
Offset1 = Offset1 + 1
Loop
Application.EnableEvents = True
End Sub

 

[Guest]

The result would be the list of accounts in Col A of "Budget" are repeated

12 times each in Column A of worksheet "Data".

Going by the above .. here's a formulas option to try

Assume unique accounts running in A2 down in sheet: Budget

In the other sheet: Data,
place in any starting cell, say in A2:
=OFFSET(Budget!$A$2,INT((ROW(A1)-1)/12),)
Copy A2 down until zeros appear signalling exhaustion of data
This will produce the required results

Adapt the part: Budget!$A$2
to suit the location of the top data cell in Budget's col A.
(Change the "12" to say "15", if you want it to repeat 15 lines instead of
12)

---

 

[Guest]

Thanks for the response.
When I try to run the code it asks for a macro. Do I need to do anything
else?

I have several budget sheets to add to the data sheet I then import the lot
into our accounting software (12 mths x 100 accounts x 4 divisions). Will
this code run for each sheet separately and add to the data sheet? If the
import gets too cumbersome I will use separate import files for each division.

Cheers

 

[Guest]

Jim,

That code goes into each worksheet's own code section that you need it to be
used on. That's why I said copy it from the posting above, right click on
the "Budget" sheet tab and choose [View Code] from the list that appears and
paste it into the (probably) empty sheet that appears in the editor. It is
called, not as a 'macro' per se, but as a response by Excel to a change in a
cell in column A of that sheet. For multiple sheets, a copy of that code
would have to be placed into each equivalent of the 'Budget' sheet. It also
assumes that both the 'Budget' sheet(s) and the Data sheet are all in the
same workbook.

You might take a look at the solution Max offers - it might be more useful
to you if you are dealing with multiple sheets and multiple workbooks.
However, if you are working with multiple workbooks, then the book the 'Data'
sheet is in will be linked to each of the other workbooks referenced in the
formula. The big problem with his offering, is that if the 'Budget' sheet(s)
are in other workbooks, the formula cannot work properly unless those
workbooks are open at the same time the one with the 'Data' sheet is open.
Offset() doesn't work across workbooks unless both are open.

If you are working with multiple workbooks and want to do the data copying
'on demand' by the user by choosing Tools | Macro | Macros and picking the
macro from the list type of operation, let us know. That can be arranged.

 

[Guest]

Thanks guys, you are a big help. It was remiss of me not to reply sooner.
I've been distracted with another project and for some reason this post
dissapeared (took me an age to find again).

I have another request if you don't mind.

The formula =OFFSET(Budget!$A$2,INT((ROW(A1)-1)/12),) works well. The
manual drag down dosen't matter since the result will be a template and
should not need much maintenance.

However, I was wondering if you had a solution as to how I may match the
account codes now in column A with the corresponding amount for each month
(columns R to AC)

Currently the data is like this:
Account July Aug Sept etc
1010 $1,000 $2,000 $2,500

To import the data I need it to convert to this:
Account Date Amount
1010 July $1,000
1010 Aug $2,000
1010 Sept $2,500

and so on for 100 accounts over 12 months for seven divisions. As you can
see this would be over 8,000 lines of data. We recast our budgets each
quarter and would need to update the data regularly.

You can see why I would want to automate the import of the budget data so
any assistance you could provide would be very gratefully appreciated.

Cheers
Jim

 

[Guest]

Not to worry - things get shoved back very quickly around here, and lately
the system seems to have some real issues. I myself try to keep a record of
where I've been and check up on things because sometimes it seems the system
doesn't pay any attention to the "Notify me..." box. Found someone that I'd
assisted a month ago that needed to take it one more step and I'd seemingly
just abandonded him - made me feel pretty bad.

Thanks for the thanks, and hope things are working well for you.

 

[Guest]

Glad it wasn't just me going nuts!

Thanks again and I hope you get a chance to have a look at a solution for my
current dilemma.

Cheers

 

[Guest]

If I've understood this correctly
Source data looks like this now and is on Sheet1

A ... Q R S T AB AC
1 Account ??? July Aug Sep ... May Jun
2 1010 $$$ $$$ $$$ $$$ $$$
3 2020 $$$ $$$ $$$ $$$ $$$

and you already have Account #s/Months set up in A & B elsewhere ready to to
associate values with it - you just need a formula to get the amount for each
account for each month. (and is on a different sheet)
Account Date Amount
1010 July need
1010 Aug formula
1010 Sept for
....
2020 July entries
2020 Aug in this
2020 Sept column

Lets assume your 'Current data' list has accounts from A1 down to A701 (100
accounts, 7 divisions), and we know the months go from R1:AC1
In column C where you need the formula, in cell C2 enter

=OFFSET('Sheet1'!$Q$1,MATCH($A2,'Sheet1'!$A$2:$A$701,0),MATCH($B2,'Sheet1'!$R$1:$AC$1,0))

Changing the sheet name as required
Note that the Offset refers to Q1 on the source sheet; that allows the
offsets determined by the two MATCH() functions to pick up the correct
values. This formula can be extended or filled down the sheet for all
entries. But since you've got 8400 entries, might be a bit tedious, so
here's a quicker way, assumes you've already got columns A and B filled down
as far as needed:

Click in C2 (where you put the first formula), press [F8], press [Ctrl]+[End]

If more columns than just C are selected, hold the [Shift] key down and use
the arrow keys to get it down to just Column C (can adjust rows also if it
went too far for some reason). Then use
Edit | Fill | Down
to fill up the column with the formula.

 

[Guest]

You have the structure correct as:
(Sheet ATWS = Divisional budget)
A B R S...........to AC
5 Account Name July-06 Aug-06......to June-07
6 (Header)
7 1010 Sales1 $$$ $$$ $$$
8 1020 Sales2 $$$ $$$ $$$
9 1030 Sales3 $$$ $$$ $$$
....to row 121
(Columns C to Q are prior year calcs and are ignored)

The formulas are in Sheet "ImportDataATWS" creating the data to suit our
accounting software in this format:

Account Date Amount
1010 July $$$
1010 Aug $$$
1010 Sept $$$
....
2020 July $$$
2020 Aug $$$
2020 Sept $$$

Account numbers start at row 6 (import code is in the first 5 rows).
I have used =OFFSET(ATWS!$A$7,INT((ROW(A1)-1)/12),) In column A to create 12
instances of each account number in "ImportDataATWS". (Thank you Max!)

I used =INDEX(ATWS!$5:$5,ROWS($1:18)) in column B to create the first 12
dates to match the account numbers. Unfortunatley I couldn't make this repeat
from the first date for the next 12 account numbers so I copied down blocks
of 12 to create the match.

To finalise the template I would like a similar solution that makes
inserting these 12 dates into column B of "ImportDataATWS" easier.

I managed to get the account to match with the value for each month with:
=SUMPRODUCT((ATWS!$A$7:$A$121=ImportDataATWS!A6)*(ATWS!$R$5:$AC$5=ImportDataATWS!B6)*ATWS!$R$7:$AC$121)

Your formula looks a bit tidier, so I might use it instead.

Sorry for the long dialog I hope it's not too confusing...I will have many
future instances where this will prove extremely useful.

Cheers
Jim



--
Jim

If I've understood this correctly
Source data looks like this now and is on Sheet1

A ... Q R S T AB AC
1 Account ??? July Aug Sep ... May Jun
2 1010 $$$ $$$ $$$ $$$ $$$
3 2020 $$$ $$$ $$$ $$$ $$$

and you already have Account #s/Months set up in A & B elsewhere ready to to
associate values with it - you just need a formula to get the amount for each
account for each month. (and is on a different sheet)
Account Date Amount
1010 July need
1010 Aug formula
1010 Sept for
...
2020 July entries
2020 Aug in this
2020 Sept column

Lets assume your 'Current data' list has accounts from A1 down to A701 (100
accounts, 7 divisions), and we know the months go from R1:AC1
In column C where you need the formula, in cell C2 enter

=OFFSET('Sheet1'!$Q$1,MATCH($A2,'Sheet1'!$A$2:$A$701,0),MATCH($B2,'Sheet1'!$R$1:$AC$1,0))

Changing the sheet name as required
Note that the Offset refers to Q1 on the source sheet; that allows the
offsets determined by the two MATCH() functions to pick up the correct
values. This formula can be extended or filled down the sheet for all
entries. But since you've got 8400 entries, might be a bit tedious, so
here's a quicker way, assumes you've already got columns A and B filled down
as far as needed:

Click in C2 (where you put the first formula), press [F8], press [Ctrl]+[End]

If more columns than just C are selected, hold the [Shift] key down and use
the arrow keys to get it down to just Column C (can adjust rows also if it
went too far for some reason). Then use
Edit | Fill | Down
to fill up the column with the formula.

Glad it wasn't just me going nuts!

Thanks again and I hope you get a chance to have a look at a solution for my
current dilemma.

Cheers