• 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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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