Solved

# amend current formula to amend table row height

Posted on 2014-02-14
243 Views
Hi Experts excel 2007

I am using the following formula in cell a6 =row(a\$53)-row(a \$6)-1

In a6 I have formula =max (a6,1)

In colc.                     Cold.
Sheet1! C6:c53.       Sheet1! D6:d53

I want the value of A53 (formula in a6) to increase and decrease as row are add or deleted from table.
Assume table A6:d53
0
Question by:route217
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 12

Expert Comment

ID: 39860562
route217,

Have you considered using dynamic named range?

BTW, in your question there seem to have some cell reference error.

You said A6 formula is =Row(A\$53)-row(A\$6)-1

Then you said
A6 formula is =Max(A6,1)

Set a named range Called LastRw or something with the following reference
=row(OFFSET(\$A\$1,COUNTA(\$A:\$A)-1,0))

This way, the LastRw is always reference to the last cell with data in column A.
0

Author Comment

ID: 39860581
Harry lee
Firstly thanks for the excellent feedback. .do u have any explain wrkbk you could kindly attached,  please.
0

Author Comment

ID: 39860582
Example.
0

LVL 12

Accepted Solution

Harry Lee earned 500 total points
ID: 39860602
Take a look at the sample workbook.

Without seeing your spreadsheet, this is a basic sample I can put together.
Dynamic-Named-Range-Sample.xlsx
0

LVL 12

Expert Comment

ID: 39860606
For more guideline, you can take a look at this microsoft post.

Dynamic Named Range
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month3 days, 13 hours left to enroll