Solved: dynamic formula update based on year extension

[Beatrix]

Hi Everyone ,


I have a spreadsheet which keeps back up log data. The structure works based on data source held in Planner tab. When you change the year in cover tab then all tabs are updated automatically. However data source needs to be extended for 2015 and onwards as the formula is not working when the year is changed 2014 to 2015. I can't update the formulas manually as the structure follows a logic which I'm lost on it. :doh:I've thought it might be possible to extend it by VBA.:think: If you worked on similar data scenario before could you please help me on this?: pray2:

I attempted to attach the sample file a few times and got this message. There seems to have been a problem with the VBA Express Forum database.:dunno


Any suggestions ??

Many thanks
Yeliz

[BrianMH]

If uploading to this site doesn't work try one of the upload sites and post a link to it. Really hard to help with out an example.

[mancubus]

pm for email. let me try to upload.

[mancubus]

your file is 1.05 MB which exceeds the vbax upload limit of 1 MB.

saved your file as xlsm and it's now 0.35 MB :)

[mancubus]

hi.
i think this file is inherited from a colleague :)

your time schedule is based on business days.
5 days make a week,
4 weeks make a cycle (month) and
11 cycles make a ..., say, period (year).
no national or public holidays are taken into account.

i just copied last full 11-cycle-period (220 rows X 18 columns) down until reaching 31 Dec 2019, corrected the vlookup formulas in the month sheets.

i just followed the pattern, so i cannot guarantee the results. :)

[Beatrix]

Hi mancubus ,

Thanks very much for your time. It's working perfect:beerchug:I got confused about that pattern and ended up copying formulas by dragging down however something went wrong as all X didn't the follow same pattern. Anyways it seems ok now:thumb

thanks very much again!:bow:

Cheers
Yeliz



hi.
i think this file is inherited from a colleague :)

your time schedule is based on business days.
5 days make a week,
4 weeks make a cycle (month) and
11 cycles make a ..., say, period (year).
no national or public holidays are taken into account.

i just copied last full 11-cycle-period (220 rows X 18 columns) down until reaching 31 Dec 2019, corrected the vlookup formulas in the month sheets.

i just followed the pattern, so i cannot guarantee the results. :)

[Beatrix]

thanks very much for your reply Brian:thumb


If uploading to this site doesn't work try one of the upload sites and post a link to it. Really hard to help with out an example.