sheet name extraction

yes thats great. thanks.

so theoretically i could use it like this:

=IF(ISERROR(MATCH(("'"&MID(SheetNames!A9;FIND("]";SheetNames!A9)+1;256))!B5;(OFFSET(("'"&MID(SheetNames!A10;FIND("]";SheetNames!A10)+1;256))!$B$5;0;0;COUNTA(("'"&MID(SheetNames!A10;FIND("]";SheetNames!A10)+1;256))!$B:$B)-1;1));0));1;0)

but it gives me somehow an error.. is this possible at all?

the A9 and A10 are the cells where the sheet names are saved..

allright, thanks again

is it possible to use the indirect like this?:

=IF(ISERROR(MATCH(_13.05.2009!B5;(OFFSET(_14.05.2009!$B$5;0;0;COUNTA(_14.05.2009!$B:$B)-1;1));0));1;0)

-> use the reference to the sheet which is called _13.05.2009

=IF(ISERROR(MATCH((INDIRECT("SheetNames!B4"))!B5;(OFFSET((INDIRECT("SheetNames!B5"))!$B$5;0;0;COUNTA((INDIRECT("SheetNames!B5"))!$B:$B)-1;1));0));1;0)


in sheet names B4 is the name of the sheet hence _13.05.2009...
and sheetnames B5 is _14.05.2009

ok what i need is basically the formula that is pointing on two different sheets:
_13.05.2009 and
_14.05.2009

the formula is comparing cell B5 of the _13.05.2009 sheet with all cells of B in _14.05.2009

kind of: if value B5 is in list B then do this and that...

and the problem i have is that i dont want to have the sheetnames hardcoded in the formula. so i would like to create a reference to a third sheet where i have a list with all the sheet names of my workbook. so that i only have to do changes in that place instead of in everyplace where i have the formula.

hope this makes it a little more understandable...

Originally Posted by Stormseed

What do you mean by changing hardcoded sheetnames in formula ? I am still in a confusion about what you need to accomplish

let my try another approach

my sheet is a list with problems we have here. so just a normal list. then i need to compare over time which problems are new and which are solved. so for that i have to compare the new list with the old one.
and everytime we have a new list we add a new sheet with the new list.
for this i copy the whole old worksheet into the new one (with formulas and everything) and just insert the new problemlist where the old one was.

and for that i have to change all the formulas to the new sheet. so that they are working on it. (every formula points to another worksheet)

now what i would like to have is a list of my sheets. fex:

15.05.2009 sheet1
16.05.2009 sheet2
17.05.2009 sheet3

so that i can use the sheetnames in the formulas. (like in the above example)
it would be simpler if a just can add a new sheetname to this sheetlist (every date i create a new sheet) and change the reference in the formula. (maybe with just increasing the cellnumber of it) instead of changing everytime the sheetname in the formula (fex. if(stuff(sheet1.B5)) -> if stuff(sheet2.B5))

(-> means what i have to change)

with pseudocode it could look a little like this:

the way it is now: if(stuff(sheet1.B5)) -> if stuff(sheet2.B5))
the way it should be: if(stuff(sheetlist.A1.B5) -> if(stuff(sheetlist.A2.B5))

so that means: sheetlist.A1 == sheet1 (as string)
and: sheetlist.A2 == sheet2 (as string)

do you understand something?

allright. have a nice evening or whatever you are going to have

allright the problem is solved. i used an indirect nested with an address

thanks for the time you spent helping me!

regards