Hi,

I have a number of graphs that look at fields within Excel

ie:
=Adj2005!$C$154:$L$154

Each month I have to update the last 3 digits to reference the latest data

ie:
=Adj2005!$C$154:$L$155

This is time consuming, so I thought I could create a field add a name and and the 3 digits and update the info by referencing the one field.

ie:
Field Name: Period
So it might look something like this:
=Adj2005!$C$154:$L$"&Period&"

However I can't seem to be able to get the above format correct in the values field, can someone advise please.

Thanks

Darrell....

Is the range a continuous area? If so you can simply select the first cell, select the whole area, then apply a range name. You can then use this range name in your graph.


Worksheet("Adj2005").Select
Range("C154").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="YourRangeName", RefersTo:=Selection

=Adj2005!$C$154:$L$154

Each month I have to update the last 3 digits to reference the latest data

ie:
=Adj2005!$C$154:$L$155


Some questions:

Is there any data above Row 154? (If no, then use formula below)

Will it always be Columns C to L? (If yes, then formula below will work)

If I understand correctly, define the name Period, then use this formula in the Refers to box:

=OFFSET(Adj2005!$C$154,0,0,COUNTA(Adj2005!$C:$C),1 0)

Sorry, I explained it slightly wrong in my first posting.

Amend each month for example:
=Adj2005!$C$154:$M$154
to
=Adj2005!$C$154:$N$154

So the letter changes and not the last 3 digits.

Darrell..

So have you tried my example?

It throws up an error of 'sorry that function is not valid' and highlights 'offset'

=OFFSET(Adj2005!$C$154,0,0,1,COUNTA(Adj2005!$154:$ 154))

This assumes that there is no data in A154 or B154. If so, then subtract 1 or 2 from the COUNTA portion.