Custom Function Referancing Cell Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Abode - 20 Mar 2006 20:34 GMT

Hello. I'm having a little trouble making a custom function that will show
the value of another cell. Basically what I want is a Function that will get
a Cell location from a neighboring cell and then use that value to output the
answer of that cell. I'm not sure if it makes any sence but I'll post an
example of what I'm hoping I can do.

Column F
Name 1
Name 2
Name 3

Column G
=TestDisplay(H1)

Column H1
(Now in this column I can type in F1 and the function in G will display
"Name 1")

I'm not sure if any of that helps afterall. But is there a way to do this.
When I've tried I get either #NAME or #VALUE errors.

Reply to this Message

kassie - 20 Mar 2006 20:44 GMT

In G1 enter =IF(H1="","",INDIRECT(H1))

> Hello. I'm having a little trouble making a custom function that will show
> the value of another cell. Basically what I want is a Function that will get
[quoted text clipped - 16 lines]
> I'm not sure if any of that helps afterall. But is there a way to do this.
> When I've tried I get either #NAME or #VALUE errors.

Reply to this Message

Abode - 20 Mar 2006 23:10 GMT

Thanks, That helps a lot. I kind of want to expand on it and Make it so I
dont have to type the H everytime. Is there a way that I can somehow add a H
on there and have Indirect read it.. I tried with the Numbers Tab in "Format
Cells". It always returns a #REF error and I thikn I found that even though
it reads a Cell that says H1 on there it only reads the 1.

> In G1 enter =IF(H1="","",INDIRECT(H1))
>
[quoted text clipped - 18 lines]
> > I'm not sure if any of that helps afterall. But is there a way to do this.
> > When I've tried I get either #NAME or #VALUE errors.

Reply to this Message

kassie - 21 Mar 2006 20:31 GMT

Hi

You cannot use a number format, as a cell reference is in fact text. A
workaround would be to put the H in a seperate column, say Col I, and using
the following formula instead:

=IF(H1="","",INDIRECT(CONCATENATE(I1,H1)))

You can hide this column, or simply paint the font white to hide it, if that
is needed.

> Thanks, That helps a lot. I kind of want to expand on it and Make it so I
> dont have to type the H everytime. Is there a way that I can somehow add a H
[quoted text clipped - 24 lines]
> > > I'm not sure if any of that helps afterall. But is there a way to do this.
> > > When I've tried I get either #NAME or #VALUE errors.

Reply to this Message