Good morning,

I have cells A1 and B1. Cell A1 has a value "APPLE" and a define name
"APPLE1", and Cell B1 has a numeric value 1.

Is it possible to write a formula to use define name vs. regular cell
reference (A1)?

For example, if a value in Cell B1 is 0, use regular cell reference (A1); if
a value in Cell B1 is not 0, us define name "APPLE1" for Cell A1. I want to
have the value ("APPLE") in Cell A1 stays the same.

Can it be done through Define Name area (Insert|Name|Define)?

Thanks.

I'm *really* not clear on what you're asking, but perhaps:

=IF(B1=0, A1, INDIRECT(A1 & B1))

so if B1=2, the formula refers to the defined cell APPLE2

soIn article <CA1BD196-BA31-4A8D-B8E9-(E-Mail Removed)>,
AccessHelp <(E-Mail Removed)> wrote:

> Good morning,
>
> I have cells A1 and B1. Cell A1 has a value "APPLE" and a define name
> "APPLE1", and Cell B1 has a numeric value 1.
>
> Is it possible to write a formula to use define name vs. regular cell
> reference (A1)?
>
> For example, if a value in Cell B1 is 0, use regular cell reference (A1); if
> a value in Cell B1 is not 0, us define name "APPLE1" for Cell A1. I want to
> have the value ("APPLE") in Cell A1 stays the same.
>
> Can it be done through Define Name area (Insert|Name|Define)?
>
> Thanks.

Hi JE,

Thanks for your response. I was wondering whether I can hide the define
name of Cell A1 and use the regular cell reference "A1" (instead of Define
Name) when there is a zero value in Cell B1. On the other hand, if the value
in B1 is not 0, then unhide/use the define name in Cell A1.

I hope that helps. Thanks.

"JE McGimpsey" wrote:

> I'm *really* not clear on what you're asking, but perhaps:
>
> =IF(B1=0, A1, INDIRECT(A1 & B1))
>
> so if B1=2, the formula refers to the defined cell APPLE2
>
> soIn article <CA1BD196-BA31-4A8D-B8E9-(E-Mail Removed)>,
> AccessHelp <(E-Mail Removed)> wrote:
>
> > Good morning,
> >
> > I have cells A1 and B1. Cell A1 has a value "APPLE" and a define name
> > "APPLE1", and Cell B1 has a numeric value 1.
> >
> > Is it possible to write a formula to use define name vs. regular cell
> > reference (A1)?
> >
> > For example, if a value in Cell B1 is 0, use regular cell reference (A1); if
> > a value in Cell B1 is not 0, us define name "APPLE1" for Cell A1. I want to
> > have the value ("APPLE") in Cell A1 stays the same.
> >
> > Can it be done through Define Name area (Insert|Name|Define)?
> >
> > Thanks.
>