Excel - Sheet name in formula

[binaryfingers]

Hi

I am trying to create named range that pulls the current worksheet into the named range so that it is relevent to that worksheet.

I have been advised that INDIRECT is the right way, although I have not been able to get this to work,

Is it possible?

Thanks,

 

[GlennUK]

Please give an example of exactly what you want to happen.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[binaryfingers]

Hi

I would like to use the named range in a chart.

=OFFSET('worksheet 2'!$BC$254,0,0,1,COUNTA('worksheet 2'!$254:$254))

I would like to know if the worksheet 2 can be substituted for an INDIRECT that pulls from the local sheet...so that I can use the named range on any sheet that is formatted the same way...it just use the worksheet name as the reference point..

Can it be done?

Thanks for your help,

 

[xlbo]

what have you tried ?? All you need to do is wrap the sheet name formula from the FAQ within the INDIRECT function:

=Offset(Indirect("Sheet Name Function" & "$A$1"),,,counta($A:$A),1)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[binaryfingers]

xlbo

Im confused where the $A$1 comes from?

sorry,

 

[xlbo]

change to "$BC$254" from your example - all my named ranges like that start in the top left corner of the sheet - hence A1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question