refer to cell in previous sheet

[ewibolo]

Hi, I have looked and looked 'til I'm now blue in the face and I still cannot find the answer.

Thanks for your help all, I know you'll come through!

OK, here is my situation. It seems so easy, but I can't find the answer anywhere.

Let's say I have 2 open sheets "111" and "222"
In "111", cell A1 I have a number 1
in "222", cell A1, I want to type a formula that automatically adds 1 to the sheet directly to the left of "222", or if I copy sheet 222 (and rename "333"), cell A1 will add 1 to cell A1 in 222 ( which is now the sheet directly left of the current sheet.

It is always the same cell (adjacent sheet to the left),that I want to add plus one.
It has to work with all added/copied new sheets I add to the workbook.

Thanks for any input!

 

[AlphaFrog]

Here's a custom User Defined Function (UDF) that does what you ask.
Reference previous sheets

 

[ewibolo]

Thanks for the reply, but I've been to that page before and tried to use the custom UDF =PrevSheet() , but it doesn't work. It gives me a #zero for any entries. I don't have any idea where to go from here.

thanks,

Eric

 

[AlphaFrog]

The PrevSheet UDF worked for me. I'm not sure what else to tell you.

Alternatively, you could use just a standard reference in a formula e.g.
=Sheet1!A1+1
Then if you want to change the sheet referenced in all the formulas, you can use the Replace (Ctrl-H) feature to replace all Sheet1 references with the new sheet name. Not exactly what you asked for but easy enough to do.


Or you could use the INDIRECT function. The sheet you want to reference is listed in say cell A1 and you want to reference cell B1 on that sheet
=INDIRECT(A1&"!B1")+1
If you change the sheet name listed in cell A1, the INDIRECT formula will reference cell B1 on the new sheet.