Help With an Excel Formula

  • Thread starter Thread starter lj
  • Start date Start date
L

lj

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 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?
Click to expand...

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
 
Back
Top