Help With an Excel Formula

I am in excel and I have the following formula = Q:26 typed into Cell
C:2. What I want to do is have a second cell D:2 that takes what ever
cell reference I enter into C:2 and adds 1 row to it. So cell D:2
would be equal to Q:27, which is 1 row down from row Q:26. Is there a
way to do this?

Not possible unless you use an add-in or user define function that reads the
text of the formula in C2

D McRitchie has UDF for that

http://www.mvps.org/dmcritchie/excel/formula.htm


then you could use

=OFFSET(INDIRECT(SUBSTITUTE(getformula(C2),"=","")),1,)

Regards,

Peo Sjoblom

"[email protected]" wrote:

> I am in excel and I have the following formula = Q:26 typed into Cell
> C:2. What I want to do is have a second cell D:2 that takes what ever
> cell reference I enter into C:2 and adds 1 row to it. So cell D:2
> would be equal to Q:27, which is 1 row down from row Q:26. Is there a
> way to do this?
>
>

On 10 Jun 2005 10:46:24 -0700, [email protected] wrote:

>I am in excel and I have the following formula = Q:26 typed into Cell
>C:2. What I want to do is have a second cell D:2 that takes what ever
>cell reference I enter into C:2 and adds 1 row to it. So cell D:2
>would be equal to Q:27, which is 1 row down from row Q:26. Is there a
>way to do this?

I find I receive an error message if I try to type =Q:26 into some cell. Is it
possible you are typing something else into this cell?

If, instead of =Q:26 you mean Q26, I don't believe you can do exactly what you
want without using VBA.

However, if you entered Q26 (just the string, no "=" sign) into some cell, let
us say B2, you could then use the INDIRECT function:

C2: =INDIRECT(B2)
D2: =OFFSET(INDIRECT(B2),1,0)

Another method, which may not be stable, especially with older (pre 2002)
versions of Excel and copying the cell from one workbook to another, is to do
the following:

Inset/Name
Define
Names In Workbook: FormulaC2
Refers To: =GET.CELL(6,Sheet1!$C$2)
<OK>

Then in D2 use this formula:

=OFFSET(INDIRECT(MID(FormulaC2,2,255)),1,0)

This method assumes that the only thing you have in C2 is

=cell_reference






--ron

Depends what you are trying to achieve.

If you want cell D2 to return you whatever value is in the cell that is 1
row down from the reference in C2, then assuming you enter the cell
reference in C2 as text and in the correct format, ie just Q26, then in cell
D2 you should be able to use:-

=OFFSET(INDIRECT(C2),1,0)

If however you just want a reference returned that is incremented by 1 row
from whatever is in C2, then how about:-

=LEFT(C2,2-ISNUMBER(--MID(C2,2,1)))&--MID(C2,3-ISNUMBER(--MID(C2,2,1)),LEN(C
2))+1

If this doesn't give you what you want because you need cell C2 to actually
return what is in say cell Q26, then can you not simply use a helper cell
(I'll assume say cell H1) that contains the text Q26, and in cell C2 use
=INDIRECT(H1) and then in cell D2 use

=LEFT(H1,2-ISNUMBER(--MID(H1,2,1)))&--MID(H1,3-ISNUMBER(--MID(H1,2,1)),LEN(H
1))+1

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

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

<[email protected]> wrote in message
news:[email protected]...
> I am in excel and I have the following formula = Q:26 typed into Cell
> C:2. What I want to do is have a second cell D:2 that takes what ever
> cell reference I enter into C:2 and adds 1 row to it. So cell D:2
> would be equal to Q:27, which is 1 row down from row Q:26. Is there a
> way to do this?
>