I have a multi-tab worksheet and have added a summary report to pull the totals from each row of the different worksheets. The issue is that as the columns expand (insert new columns) my formula does not updated to pick up the "new" total column but is locked to the orignial column that I first entered. Is there a way to enter the formula so that it will change as new columns are inserted? The formula I have used is =INDIRECT("'"&H$9&"'!Z28") and the issue is that as columns expand past "Z" my formula does not.
The other option I am considering as I write this is to insert a total column at the beginning of each worksheet that I can reference that will not change as new columns are inserted.
Any thoughts on this would be appreicated.
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Thanks Hughes. The report was already taking an hour to run as I was running it on all income and expenses across all of our companies and I did not want to complicate the report any more. So I ended up putting a total column at the left of the replicated columns in each worksheet and referenced it using the indirect function on the summary tab and hide total column on hte individual sheets. This worked fine, thanks again!
-
Jet Reports Historic Posts Hi,
There are a couple things you can do. One would be to just write a Jet function to get the sum of all the things you are replicating and totalling on the individual sheets. With this approach, you wouldn't have to reference anything on the individual sheets at all. This is probably the easiest approach when it is feasible, but in some cases, because of complex Excel formulas in the things being totalled, it is not feasible.
In your case, if it's not possible to just write Jet formulas to get the totals, I would go with your idea of creating a total column to the left of the replicated columns. Then you can reference the total columns on your summary sheet and it won't change positions. Of course, you can also hide the total columns if you don't want them visible on the individual sheets. Does this help?
Regards,
Hughes
Please sign in to leave a comment.