Relative Worksheet References To Visible Sheet Only (VBA)

[blak9]

Hi there,

I've been searching the internet for quit some time now, but I can't find the answer I'm looking for and now my hope is pinned on you guys ;-)

I'm using the following code to refer to a previous sheet (which works great btw), but my problem lies in the fact that it doesn't skip hidden sheets.
I would like the "PrevSheet" formula in sheet 3 to refer to sheet 1 instead of sheet 2 if sheet 2 is hidden.

Please Login or Register  to view this content.

I've tried several modifications already, but noob as I am, without succes.

Many thanks in advance...

[nilem]

Hi blak9,
maybe so

Please Login or Register  to view this content.

[blak9]

Ni nilem,

Thanks a million for your quick reply. It really does kinda work, however...

These are the results when I hide sheet 3.

Sheet 1 > A1: 10
Sheet 2 > A1: =PrevSheet(A1)+1 returns 11
Sheet 3 Hidden
Sheet 4 > A1: =PrevSheet(A1)+1 returns 13

But, (re)entering the value of 10 in Sheet 1 > A1 does give the proper result of
Sheet 1 > A1: 10
Sheet 2 > A1: =PrevSheet(A1)+1 returns 11
Sheet 3 Hidden
Sheet 4 > A1: =PrevSheet(A1)+1 returns 12

Same thing when I unhide Sheet 3. The update of the other sheets occurs only when I reenter the value in Sheet 1 > A1.

Is it possible to update the various sheets live, so that when I hide or unhide a particular sheet, the value of the other sheets change accordingly..?

Thanks in advance...

[nilem]

your function is recalculated for any change in the worksheet
Try to hide Sheet3 and change the value of any cell on the active worksheet, you'll see that it works.
Or try to add this code in the ThisWorkBook module (not sure that's a good idea)

Please Login or Register  to view this content.

[blak9]

Wow, thanks..!
I didn't know that 'any change' in my workbook would update the specified values. I can truly live with that, so that in itself solves my challenge.
But, ofcourse I had to try your second suggestion (Private Sub routine), which is beyond awesome and works like a charm...! :-) Just for my understanding, why would you think that's not a good idea though..?

[nilem]

if you have a lots of formulas in the worksheet, all of these formulas will be recalculated. It may take a long time.
Although you can use only one cell for example, something like

Please Login or Register  to view this content.

[blak9]

Thanks for the additional option.
Just tested both option, and since speed isn't really an issue I'll stick to the former one...

I guess we can close this thread as SOLVED. Thanks again for helping me out...