How To Lookup Field Data (1 Viewer)

q582gmzhi

Registered User.
Local time
Today, 13:14
Joined
Dec 23, 2002
Messages
48
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....
 

reclusivemonkey

Registered User.
Local time
Today, 13:14
Joined
Oct 5, 2004
Messages
749
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.

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

shades

Registered User.
Local time
Today, 08:14
Joined
Mar 25, 2002
Messages
516
=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),10)
________
Sable
 
Last edited:

q582gmzhi

Registered User.
Local time
Today, 13:14
Joined
Dec 23, 2002
Messages
48
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..
 
Last edited:

q582gmzhi

Registered User.
Local time
Today, 13:14
Joined
Dec 23, 2002
Messages
48
It throws up an error of 'sorry that function is not valid' and highlights 'offset'
 

shades

Registered User.
Local time
Today, 08:14
Joined
Mar 25, 2002
Messages
516
=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.
________
SUZUKI DR-Z SERIES HISTORY
 
Last edited:

Users who are viewing this thread

Top Bottom