I used the formula for updating sum while adding rows above or below a list for summation.
Formula:
Please Login or Register to view this content.
The issue I am facing is that when I am copying this formula to a different cell, I have to change the reference inside the quotes. How to use formula such that I need not change the reference manually each time?
May be something like this
Formula:Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use
Formula:Please Login or Register to view this content.
Last edited by Azam Ali; 02-16-2018 at 03:40 AM.
Azam
If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
For prompt answer, be descriptive, concise, short, direct, and to-the-point.
Why do you feel you need to use INDIRECT in the 1st place?
Care to share some sample data and expected outcome?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The example is as follows
There are different Heads like Head-1, Head 2 … which are in Col. A
If I add a row above A7 or above A2, the total of Head-1 should automatically be updated.
Or if I add a row above A15 or above A10, the total of Head-2 should automatically be updated.
The formula =SUM(INDIRECT("A7:A"&ROW()-1))
and =SUM(INDIRECT("A15:A"&ROW()-1))
in the “Total” cell used to achieve what I want to. But the problem is that I have to copy the same formula to various other “total” cells and afterwards have to change the cell reference because the cell reference within INDIRECT “” does not update itself.
The attached workbook is completely devoid of data, so please attach the correct workbook instead.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
revised file attached
There are no INDIRECT formulae there. Please try again. The workbook is of no use if it does not contain the things you are talking about.
I have attached the file with INDIRECT function in the cell where total is required.
This works fine for me. But, if I copy this formula to a different cell, I have to change the reference inside the quotes.
So the question posed in post #4 remains pertinent: WHY do you need the INDIRECT function at all? Please answer this question, because I can see no reason for your needing it in your sample workbook.
I have tried SUM-OFFSET function also. But, as far as I know, it takes care of only fixed number of rows or columns added above. In my case, the number of rows intended to be added above the top or bottom row varies.
I give up. The sample workbook is inadequate - sorry. It does not make it clear why you need this at all. I cannot help unless you answer my questions fully and illustrate your problem in full.
I have sample numerical data from A2 to A5. The sum is displayed in A6. I wish to hold the range as dynamic while summing. Hence, I used the following formula in A6
=SUM(A2:OFFSET(A6,-1,0))
The above formula considers extra rows that are added below A5 only. If I add cells above A2, they are not being considered in the calculation of sum. How should the formula be modified in order to consider the rows above A2 also in the sum?
Note: I am aware that =SUM(INDIRECT("J5:J"&ROW()-1)) is a solution to this problem. But, the difficultly I am facing is that, when I copy this sum formula to a different cell, the reference inside the quotes has to be modified manually. Hence, I desire a formula such that I need not modify the reference cells manually each time when I copy the SUM formula.
I tried this formula and it works
Formula:Please Login or Register to view this content.
I am posting this for the benefit of the community. As explained previously, even though SUM(INDIRECT("A6:A"&ROW()-1)) is a solution to the problem, one has to edit the reference manually, which is cumbersome. I hope this helps others in this forum.
In the image below, observe the formula entered in A6. If you add any number of cells added above A2 or below A5, the sum will be automatically updated.
Last edited by melvinkoshy; 02-17-2018 at 01:47 AM.
That's clearer. OK, well the easiest way around this is to convert the range into a table, then Excel will know that rows entered above or below need including. Use the SUBTOTAL function for your sum at the bottom.
The following can also do the trick. Thanks for the help and suggestion. As suggested by you, table also solves the problem. But, the default headers in tables is quite irksome. So I preferred a formula instead of the table.
Last edited by melvinkoshy; 02-17-2018 at 05:13 AM.
A7=SUM(INDEX(A$1:A6,MATCH("zzz",A$1:A6)):INDEX(A:A,ROW()-1))
Try this and copy towards down
Note:- The headers must be in text only
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Well, you can format the column headers in a table to suit yourself and change their titles. I think you are making a rod for your own back, but it's your choice.
Originally Posted by nflsales
This is absolutely amazing and this is exactly I wanted.
This saves my time with the fact that I need not edit any part of the formula after copying. Moreover, the sum is dynamic also.
Yes, I understand it is not a standard way of achieving the result. However, thanks for the professional solution to the problem. Also, the title of the problem is misleading also which made you to think that I am particular in using INDIRECT function for achieving the desired result.
Last edited by melvinkoshy; 02-17-2018 at 05:45 AM.
This is preciesely why we encourage you to use thread titles that explain your PROBLEM, not what you think the solution might be. Lesson learned, I hope!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks