Improve company productivity with a Business Account.Sign Up

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

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
Asked:
route217
  • 3
  • 2
1 Solution
 
Harry LeeCommented:
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
 
route217Author Commented:
Harry lee
Firstly thanks for the excellent feedback. .do u have any explain wrkbk you could kindly attached,  please.
0
 
route217Author Commented:
Example.
0
 
Harry LeeCommented:
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
 
Harry LeeCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now