vba to call a referenced range

[scottwilsonx]

Help gratefully received on this one please!

I have an Excel 2000 file with a main worksheet called "Weekl
positions":
The main data in "weekly positions" runs from E3 to AZ66.
Where Column E contains the name of the various companies, Column
contains a text value showing which row the data is in (eg: E1
contains the value 10, E30 the value 30......etc). Row 3 contains th
time period (usually text like "01/04/2004" etc.
Each cell within the range contains a value based on the companie
results in each period.

A main sheet called "interactive" contains a lookup in cell A1 of th
row numbers in column G of the worksheet "weekly positions"

What I need to do is this:
In a main sheet called "interactive", create a macro / or formula
that will, for time period, return the value in the specific ro
selected.
For example, if the user wanted to see the results in row 10, then the
would see the values in "weekly positions" for cells F10 to AZ10. etc.

I hope if i have explained this adequately. Please let me know if yo
need further information. I have played with INDIRECT formulaes but
think a macro might be the way forward !?

All help gratefully received.
Thanks

Scott

 

[Bernie Deitrick]

Scott,

On sheet "interactive"

=INDIRECT("'Weekly Positions'!" & ADDRESS($A$1,COLUMN(F1)))

and copy over as far to the right as your need

HTH,
Bernie
MS Excel MVP

 

[scottwilsonx]

Bernie, thank you very much for your help!
Just what I am after.

Thanks again

Scott