excel formula question 2

[JERD1074]

is there a way to have a formula look at a value to use as a reference?

here is what i am trying to do.

in sheet1,
in a2:
=Sheet2!D15
in a3:
=Sheet2!D24

is it possible to have the row number be a cell reference?
sort of like this...
=Sheet2!D(I4)

so i can change where the formula is looking without changing the formula?

i know the formula i have doesn't work, just trying to get the concept out there.


thanks

 

[AnotherHiggins]

Have a look at the INDIRECT function.

An example:
[COLOR=blue white]=indirect("Sheet1!D"&I4)[/color]

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[KenWright]

Also you might want to consider OFFSET, eg

Assuming you wanted a range of cells starting A1 to Ax, and in say cell C1 you had x, then assuming x was say 10, in any other cell

=OFFSET($A$1,,,C1)

will give you the range A1:A10, such that

=SUM(OFFSET($A$1,,,C1))

will give you the same as

=SUM(A1:A10)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Back to "normal," Ken? ;-)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[KenWright]

Hmmm - sounds fishy to me.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[JERD1074]

thanks guys.

I ended up using anotherhiggins post. it works great.

Thanks.

 

[GlennUK]

Hey Ken, talking of fish ... do you know what happened to the "combinations" thread? That's the hardest I've thunk this year, and the fruits of my labour seems to have disappeared.

Cheers, Glenn.

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

 

[xlbo]

erm - that might've been me
I suggested that the user simply wanted someone to write code for them rather than learning for themselves and it would appear that site management agreed...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Oh well, my brain's had it's exercise for the year ... good job I saved the code somewhere safe .... it might come in useful some day.

Cheers, Glenn.

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

 

[xlbo]

Sorry Glenn - just can't stand people that use "I want" anywhere in their "question" - to me that is a demand rather than a question and they have no place here

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Yes Geoff, have to agree with you. It did get me into thinking mode ... had great fun playing with recursion, so not a waste of time.

Cheers, Glenn.

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