Refer to previous sheet

**teylyn** · 11-24-2009, 09:28 PM

Hi,

the attached file uses an old Excel 4 macro function to come up with a list of sheet names in the workbook. This function works only when used in a defined name, but it's almost as good as a UDF.

Using INDEX/MATCH in combination with that defined name, you can identify the name of the previous and the next sheet. Using INDIRECT with that sheet name can then return a cell value from the respective sheet.

You can also use the defined name to create a list of all sheets in the book.

You can now insert, remove, re-arrange, rename sheets and your formulas can reference to their immediate neighbours without a hitch.

**Swift4Play** · 11-24-2009, 09:40 PM

thanks matey, note sure i 100% understand it straight off but the working workbook will speed that process up, muchly appreciated thank you.

**Swift4Play** · 11-24-2009, 10:51 PM

Need alittle more help if pos. I've now got an index sheet which as name would suggest simply has an index of all sheets in correct order. I'm having difficulty adapting my formula to access this index, find which is the previous sheet and then route to it. Any help/advice appreciated