Value of cell Defines Range

[BEETHOVEN]

Hello,

I am trying to set a picture to a range of cells which I can do by
setting the picture to a range like =$A$1:$C$5. My problem is that the
range can change anytime. It will always start in $A$1 and end in
column $C but the row for column $C can vary. I know what row column $C
should be but I cannot not change the range automatically but have to
do it manually. I can put the value for the row in column $C in a cell
like D1 but I cannot think of a way to put that in the range
automatically (See Example below). Any ideas?

Example
Range is =$A$1:$C$?. D1 contains the value of 8. So I want my range to
be =$A$1:$C$8. So when the value in D1 changes I want the range to
change also.

Thanks in advance

 

[Ken Johnson]

Hello,

I am trying to set a picture to a range of cells which I can do by
setting the picture to a range like =$A$1:$C$5. My problem is that the
range can change anytime. It will always start in $A$1 and end in
column $C but the row for column $C can vary. I know what row column $C
should be but I cannot not change the range automatically but have to
do it manually. I can put the value for the row in column $C in a cell
like D1 but I cannot think of a way to put that in the range
automatically (See Example below). Any ideas?

Example
Range is =$A$1:$C$?. D1 contains the value of 8. So I want my range to
be =$A$1:$C$8. So when the value in D1 changes I want the range to
change also.

Thanks in advance

Hi,
are you able to use =INDIRECT("$A$1:$C$" & D1) ?

Ken Johnson

 

[BEETHOVEN]

Your suggestion did not work. It gave me the following error message
-The text you entered is not valid reference or defined name. On the
other hand though your suggestion gave me an idea. I knew I could set
the picture to a "Name" using Insert-Name-Define. My picture is on
Sheet1 and the range I want my picture to equal is on Sheet2. So I
created a "Name" (Insert-Name-Define) called "Picture_1_Range"
and it has the formula of =INDIRECT("Sheet2!$A$1:$C$" & Sheet2!$D$1).
Then for my picture "Picture 1" I set it to = Picture_1_Range. Your
suggestion did help me after all. Thanks Ken.

Gary

 

[Ken Johnson]

Your suggestion did not work. It gave me the following error message
-The text you entered is not valid reference or defined name. On the
other hand though your suggestion gave me an idea. I knew I could set
the picture to a "Name" using Insert-Name-Define. My picture is on
Sheet1 and the range I want my picture to equal is on Sheet2. So I
created a "Name" (Insert-Name-Define) called "Picture_1_Range"
and it has the formula of =INDIRECT("Sheet2!$A$1:$C$" & Sheet2!$D$1).
Then for my picture "Picture 1" I set it to = Picture_1_Range. Your
suggestion did help me after all. Thanks Ken.

Gary

You're welcome Gary.
Thanks for the feedback.

Ken Johnson