Previous and Next row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have the following scenario (much simplified):
Worksheet with 2 columns haveing labels at the top - Column1, Column2.

Each cell in Column2 needs to look at the value in Column1, say, one row
down (or up).

I use column labels in my formulas. Is there a compact way to reference a
cell in a previous row using the label of that column (a macro function will
do)?

Thanks
 
I'm sure your problem makes sense to you, but I am at a loss as to how
a column label can be used to distinguish between rows. Maybe, if you
share an example...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Utf-8?B?UGllcnJlIFNjZXJyaQ==?=" <Pierre
(e-mail address removed)> says...
 
Five comments.

First, the use of labels in formulas has been problematic. While I
cannot quote any specifics, I experimented with the idea when it was
first introduced and gave up on it. In fact, XL doesn't even enable
the capability by default. See 'About labels and names in formulas' in
XL help for more.

Second, there are many ways that are as easy, if not easier to use. In
B2, enter =A1. Copy B2 down col. B and the relative formula will
adjust automatically. Alternatively, name column A (Insert | Name >
Define...) and use that name in a formula. You could even use the text
in A1 as the name (as long as it meets the requirements for a
legitimate name).

Third, and this is a very powerful and oft-overlooked capability. You
can create a name that is a relative reference. With B2 selected,
create a name that refers to =A1. Ensure that it is a relative
reference (i.e., no $s) or a partial relative reference as in =$A1.
Now, in B2 enter =name. Copy B2 as far down col. B as needed.

Fourth, why the reluctance to use ROW()? It would fit in perfectly
with a named column A.

Finally, yes, a UDF can help simplify the complexity of a worksheet,
but often, a named formula/reference is just as effective.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top