x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 249

# amend current formula to amend table row height

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
route217
• 3
• 2
1 Solution

Commented:
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 Commented:
Harry lee
Firstly thanks for the excellent feedback. .do u have any explain wrkbk you could kindly attached,  please.
0

Author Commented:
Example.
0

Commented:
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

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

Dynamic Named Range
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.