[Solved] Copy every 25th line

[gonzoid]

how do i assign a value when i want a different sheet plus 25 lines down. ie. sheet(i5) then copy sheet where next sheet will be sheet(30)

story, i have a check register showing all 365 days, with columns for credit card balances. then a sheet showing week 1 and balances, then week 2 balances, so week 1 credit card balance might be ='current balances'.i5, then week 2 will be ='current balances'.i30

but how do i copy week 1 where week 2 is automatically added the 25 lines, right now i am backspacing to get rid of the 5 and adding 30, for 15 entries every week

[FJCC]

It is not clear to me exactly what your goal is. I suspect it would be easily done with a Pivot Table, but I'm not sure. Could you post a small file showing your data layout and what you want to achieve to summarize the daily data? To post a file, click Post Reply and look for the Attachments tab just below the box where you type a response. The file only needs to have two or three weeks of data and you should use dummy data so your personal information is not revealed.

[gonzoid]

file too large to attach. value -1462.78 is a sheet called current balance line number 1067 for week 47 and 1090 for week 48
DATE TRANSACTION pending CHECKING 7633 CHECKING 6386 CRED O NE VISA
2207 $1,700.00
11/19/23 $1,630.83 $1,953.21 -$1,462.78
$1,630.83 $1,250.00 $3,203.21 -$1,462.78
$1,630.83 -$30.00 $3,173.21 -$1,462.78
11/20/23 -$33.85 $1,596.98 -$416.00 $2,757.21 -$1,462.78
$1,596.98 $2,757.21 -$1,462.78
W $1,596.98 $2,757.21 -$1,462.78
E 11/21/23 $1,596.98 -$95.54 $2,661.67 -$1,462.78
E $1,596.98 $2,661.67 -$1,462.78
K $1,596.98 $2,661.67 -$1,462.78
11/22/23 $1,596.98 $2,661.67 -$1,462.78
47 $1,596.98 $2,661.67 -$1,462.78
different sheet called week 47
CRED ONE VISA 2207 $1,950.00 -$1,462.78 $487.22
another sheet week 48
CRED ONE VISA 2207 $1,950.00 -$1,462.78 $487.22
when i move and copy 47 to 48 i need to backspace the value for 1067 and change to 1090
how can it automatically take the value 23 lines later that i dont have to backspace and replace

[gonzoid]

sorry it didnt submit the way i copy and pasted what i was explaining, i have team viewer if interested, email me at bryankannenberg@yahoo.com

[FJCC]

Posting a file is by far the best way to make progress. Make a copy of your full file and trim that file down so you can post it. Could you post the first two or three weeks of the year, deleting all sheets and data not associated with that? Or post the last two or three weeks and delete everything above that.

[gonzoid]

ok, so week 1 cell d10='current balances'. Q22,
then copy and move week 1 to make week2, cell d10 is still the same,
how can week 2 cell d10 automatically = 'current balances'.Q46,
each week sheet will have 24 lines so then week3 will be Q70, week4 Q94 without having to backspace the 22 to make 46 for week 2, then backspace 46 to 70

hope can understand more now

[FJCC]

The attached file shows a possible solution. I changed the formulas in the cells 'week 1'.D10:D12. In D10, there is

Code: Select all

=INDEX($'current balances'.Q1:Q10000;24*($C$1-1)+22)

That looks in the array of cells 'current balances'.Q1:Q10000 and it returns the value in the 24*($C$1-1)+22 position. I assume that C1 contains the week number, which is 1 in this case. So, 24*(1-1)+22 = 22 and the formula returns the value in 'current balances'.Q22. When that sheet is copied, as I did to make the sheet 'week 2_New', all you have to do is change the value of C1 to be 2. Then 24*($C$1-1)+22 = 24*(2-1)+22 = 46 and the formula returns the value of 'current balances'.Q46.
The formulas in D11 and D12 return values from column S and U.