Hi, Please see the attached excel documents. The cells highlighted in RED are manually done. I'm hoping to find a way to automate this by selecting the bottom right hand corner of a cell and dragging in across.
Basically I want to avoid the following which requires that i manually insert the relevant cells:
Cell 1: =$O$47-D46 Cells2: =$O$47-D46-E46 Cell3: =$O$47-D46-E46-F46
I want a way where I can just Highlight the cell I'm working in and it will auto-complete.
Last edited by concatch; 06-19-2012 at 02:26 PM.
Try:
=$M$3-SUM($B2:B2)
copied across
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
How about
=$M$3-SUM($B$2:B$2)
in o5
=$M$3-SUM($B$2:B2)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
anyone else lol
Mine's the winner since you can also copy down
Brilliant thanks a million!!!!
I'll see your winner and raise you a generic that could be dragged indefinitely for subsequent yearsOriginally Posted by NBVC
Mine's the winner since you can also copy down
=INDEX(3:3,CEILING(COLUMNS($A:A),13))-SUM(OFFSET($A2,,CEILING(COLUMNS($A:A),13)-12,1,MOD(COLUMNS($A:A),13)))
Impressive...
Another alternative:
=INDEX($B$3:N$3,MATCH(2,INDEX(1/(LEFT($B$1:N$1,4)="Year"),0))-1)-SUM(INDEX($B2:B2,MATCH(2,INDEX(1/(LEFT($B$1:N$1,4)="Year"),0))-12):INDEX($B2:N2,COLUMN()-14))
More impressive
I was trying to do it with index but wasn't getting the right results, hence the offset formula posted.
Best I could come up with was =INDEX(B3:N3,MOD(14-COLUMN(),13))-SUM(INDEX($B2:B2,FLOOR(COLUMN(B2)-1,13)+1):B2) which, unlike yours, assumes that the layout will remain consistant.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks