Refer to previous sheet

[Swift4Play]

I've got a workbook that increments in sheets for each new week. I have a cell showing the current week number for that sheet ( lets say cell C1) and any formulas that need to reference data from the previous sheet do in 'indirect' function which basically looks at the current week number ( lets say week 20)in cell C1 and minus's 1 to navigate to the previous week (week 19).

I was wondering if there was a way to reference the previous sheet purely by the order they're in.

So say i have 5 sheets named "1,4,5,8,9" and these represent week numbers so the sheet named '8' in cell C1 would have '8' standanding for the week number but cells that wanted to reference the previous sheet couldn't do the 'indirect' C1-1 as there is no 'week 7'.

The weeks used could vary alot so a formula to reference the directly previous sheet is needed. Hope that makes sense and hope someone can help.


ok, thought best if i show you the actual formula

=IF(ISBLANK(C12),"",IF(ISERROR(VLOOKUP(C12,INDIRECT("'"&($AG$2-1)&"'!$C:$AG"),28,FALSE)),G12,SUM(G12,VLOOKUP(C12,INDIRECT("'"&($AG$2-1)&"'!$C:$AG"),28,FALSE))))

the '$A$G2-1' part is the bit that redirects to the previous sheet based on it's name, what i think i really need is to replace this part with a 'PrevSheet' function but i'm unsure how.

Much appreciated.

[darkyam]

I would suggest keeping a list of the sheet names on one of the sheets, say in X1:X5, then use =LOOKUP(C1-1,X1:X5) in the Indirect function.

[Swift4Play]

thank you for the suggestion but as it turns out it was as simple as replacing my AG2-1 with prevsheet lol, who'd of known.

[Swift4Play]

ok spoke to soon, doesn't work right

[teylyn]

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]

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

[Swift4Play]

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

[teylyn]

To find the sheet name for the next sheet after Index, use

=INDEX(WBSheets,MATCH("index",WBSheets,0)+1)

The sheet before Index use

=INDEX(WBSheets,MATCH("index",WBSheets,0)-1)

Wrap this in an INDIRECT to access a cell within that sheet

=INDIRECT(INDEX(WBSheets,MATCH("index",WBSheets,0)-1)&"!A1")

will return cell A1 from the sheet that is before Index.

All in relation to my previously posted workbook and the WBSheets name set up there.

hth

[Swift4Play]

Thanks for your continued help, it is appreciated, think i'm just being abit slow as these are functions that i havn't used before and trying to mix your formulas with my sum vlookups isn't straight forward.

i've attached the basic sheets without all the macros, perhaps you would be able to gave me more insight by on sheet 2 making the first cumulative Goss Total box sum the gross total of that sheet and the previous with this index/match feature.

appreciate you taking time to help me understand

[teylyn]

This file is too big for me to download. I'm on a data plan
could you possibly upload a smaller file, with just enough data to illustrate the issue. Five sheets and a few lines of data should be enough.

[Swift4Play]

ok, i managed to half the size of the file by cutting big chunks out but you should still get the picture, hope it's small enough

[teylyn]

sorry, but I don't understand your workbook all that well. Could you name the cell where you want a result and describe what that result should be?

Also, the workbook has links to external files, so a lot of the formulas come up with #REF! and I can't check if they're calculating right.