Change row number in excel formulas

[megabolic]

Hi all,

I have a lot of cells who copy a value from another sheet like this :

cell G8 ='sheet1'!C8
cell H8 ='sheet1'!C9
...
cell Y8 ='sheet1'!U19
cell Y9 = 'sheet1'!U20
...

what I want my macro to do is make all the row numbers +12. So the formula in cell G8 becomes 'sheet1'!C20, H8: 'sheet1'!C21, ...
So it has to change every formula in row 8 to 12 rows lower (higher in number).

I have no clue ...

[Richard Buttrey]

Maybe

Please Login or Register  to view this content.

[megabolic]

I don't think this will work. There is no correlation between the column where i'm changing the value from and the initial cell it refers to.

And sorry but I made a small mistake in my initial post. It's not the entire row, I will have to split it up in different ranges. But f.e. I want range F8 to J8 to change it formula so that the row of the referred cell goes +12.

[Richard Buttrey]

I don't think this will work. There is no correlation between the column where i'm changing the value from and the initial cell it refers to.

And sorry but I made a small mistake in my initial post. It's not the entire row, I will have to split it up in different ranges. But f.e. I want range F8 to J8 to change it formula so that the row of the referred cell goes +12.

I don't understand what you mean by no correlation. You need to be very clear when formulating what you actually want. You gave specific references so it's reasonable to assume that was the result you wanted.

I suggest you upload a workbook and manually add a sheet which shows EXACTLY what you want to see after the macro has run. Then explain how it might change when run again, e.g. are relative references involved and how is a macro to know where to start.

[megabolic]

I was very specific ... every formula goes 12 rows lower. Whatever may be in that formula as column.
Looking at your suggested code i realised it's not every cell in the row, but i can alter it myself to ranges. So for range ... the macro changes every formula to +12 rows, for range ... the same, ...

If a cell had the formula ="sheet1"!C9 I want it to become "sheet1"!C21. It might be the formula says D or E or .... instead of C, and 10 or 11 or 12 or ... instead of 9. I just want it to go +12 rows for all the cells in a range.

[Richard Buttrey]

..as requested please upload the workbook I asked for in post #4.

In addition are you saying that the formulae that need changing can be anywhere on a sheet, anywhere in a single column or row, or a specific range?

[megabolic]

Attatched

blad2 contains the formula and the macro, as you can see it refers to values on blad1.
When you click the button on blad2 it makes a new sheet, gives that sheet a name,
copys everything from blad2 to the new sheet en then it has to change the dates to the next "year".
That year is situated 12 rows lower on blad1.

So the formulas that have to be changed are on row8 at blad2.
The column that formula refers to does not need to change, only the row needs to go down by 12.

As there will be text, and not values in between those formulas that need to change, it will be best to make it work
for 1 range so I can just add other ranges to do the same.

I hope this makes everything clear.

[Richard Buttrey]

Is this simply a question of populating D8:H8 & M8:Q8 with the dates from the relevant rows in Blad1, or do they need to be formulae?

[megabolic]

blad2 contains current data, with current dates. The macro writes away the data from blad2 to a new sheet. The data (row 9 to ...) in blad2 will be deleted and row 8 will need new dates. Those dates are 12 rows lower then the ones used for the previous year (in blad1).
This can be done year after year, as long as dates are added to blad1

[Richard Buttrey]

..yes I understand that. My question was do the cells D8:H8 NEED to be formulae that refer back t0 sheet1, or is it sufficient that they contain the relevant VALUES from Sheet1? i.e. the dates from the relevant rows?

[megabolic]

only the values are needed

[Richard Buttrey]

Hi,

Does the following work for you?

Please Login or Register  to view this content.

[megabolic]

Altered the code and now it's perfect thanks for your help!

Please Login or Register  to view this content.

[Richard Buttrey]

My pleasure and glad we found a solution.

[megabolic]

For some reason i can't get anything posted ... hopefully it works with screenshots of what i wanted to say

My girlfriend changed her view of what the file should look like and what it has to do ... so now the code doesn't work anymore and i can't alter it ...

part1.JPG
part2.JPG
part3.JPG

[Richard Buttrey]

In this PC age I'll avoid the s exist jokes

Well that's a completely different layout with lots of additional stuff.

You'd be better advised to bring more structure to it.

I wondered last time what the dates in the two small blocks were but since there were only two blocks it was fairly trivial

Personally I don't see why you need to list all the various tables of dates and weeks when Excel is perfectly capable of using date arithmetic to work out the date of the first Monday in every year - and by extension every other week in the year. All that's needed is a rule

So the dates in row 8 of the Fiscale sheet could be formulae and the 1st sheet is redundant. For instance

F8:

Please Login or Register  to view this content.

Then the cells to the right are simply =F8+1 - adjusted when you enter a new week. So onece this row 8 is complete for the year simply changing B5 for a new year will automatically update row 8.

I'm not clear why you show 3 January 2017 on the 1st sheet as a Monday when in fact it's the 2nd January, however the formula above replicates what you're currently showing

[megabolic]

didn't know excel could filter out the independent dates however, I get an error when pasting your formula in cell F8

Knipsel.JPG

and when clicking OK it highlights the part $B$5,1,1. Now it seems that I have to change all the "," to ";". When doing this, the formula returns a value but for F8 it's just the third of january, so i altered the formula to

Please Login or Register  to view this content.

a little bit of testing now but again, thanks for all the help

[megabolic]

Works like a charm. I added an IF structure to determine the leap years and now it goes according to the correct calenders

Please Login or Register  to view this content.

[Richard Buttrey]

Works like a charm. I added an IF structure to determine the leap years and now it goes according to the correct calenders

Please Login or Register  to view this content.

Hi,

Since Excel's date arithmetic algorithm knows about leap years you could avoid having to worry about it. All you'd need to do is take the formula that you use for the 1st week of the year, put it in the first day of the first week of February and just change the date bit to

Please Login or Register  to view this content.