formula "INDIRECT" with dynamic column

• used formula :

=INDIRECT("scores!M"&activerow)

this formula puts the value in cell M5 on sheet "scores" (if activerow = 5) in the cell with the formula above

problem =
i want to be able to change the "M" column to another column depending on a value that is somewhere else

in detail :
i made a score sheet for my students
with scores on different tests
taken on a certain date (testdate1)
so test 1 in column K
test 2 in column L
test 3 in column M

these tests are again in column N, O and P
but these are taken on a different date (testdate2)

i made a scoresheet to print out
with
name of the student
and then with
the formula above for the score on test 3 in column M

i want to use the same sheet as a template for the second date (and 4 more)
so i want the score in column P now and later the ones in column X,...

can i change the "M" to "P" to "X" knowing that i can have values 1,2,3,... in a certain cell refering to the dates on which the test where taken

i've tried the "offset" but does not seem to work to change the column-character (this function works with specific ranges only i suspect)

simplified
it should be something like
column M on date 1
column M + 3 on date 2 (because the same test on the second date is THREE columns further
columm M + 6 on date 3
column M + 9 on date 4

but how can i "add" column numbers :?

• Re: formula &quot;INDIRECT&quot; with dynamic column

Hi MaxDV,

I'm not sure that I 100% understand the question, but does the attached help?

Regards,
Batman.

Files

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: formula &quot;INDIRECT&quot; with dynamic column

Hopefully some good ideas for you in this code file.

Files

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

• Re: formula &quot;INDIRECT&quot; with dynamic column

i will try it and let you know

will have to wait for a couple of hours

thanks anyway

• Re: formula &quot;INDIRECT&quot; with dynamic column

I hate waiting. :drunk:

If there are any questions, let me know.

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

• Re: formula &quot;INDIRECT&quot; with dynamic column

It gets quite tricky, especially when double letter columns are involved (3 in Office 12!).

If you could store a coilumn number in a cell it is easier,

where A1 holds that column number

HTH

Bob

• Re: formula &quot;INDIRECT&quot; with dynamic column

:thanx:
FANTASTIC

thanks mr fengore

the testrun example you made gave me the perfect idea

i changed the formula a little bit and it works perfect

never would have solved the problem without that

thanks very much!!!!

• Re: formula &quot;INDIRECT&quot; with dynamic column

Cool. Always glad I am of use.

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!