Hi There,
I'm inspired by many of using master list to do the repetitive data into invoice and packing cert.
Im using simple formula "=Master!B10",=Master!B11" to fill fields of invoice and packing cert.
As extra caution against document tampering, I would like to have my total summary dynamically to show right after the last item ie. if my last item is in row 13 then total summary will in row 14.
How do I do so?
Attached example - Inv_Master_Template
Thank you in advance.
VBA not required. Cell L11:copied downFormula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
It is ok like this:
Capture.JPG
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Wow.. Thank you TMS, KOKOSEK and another user who has been banned for their use of an obsence username.
Your solution is much simpler than expected.
Aesthetically, can we have the double line underneath the total figure?
I need remark row ie. "Total Quantity +/- 10% allowed" to follow right after total row.
Last edited by Glenn Kennedy; 12-21-2021 at 05:28 AM.
JJW. After a brief exchange of PMs... one of your helpers has been banned because of their refusal to change their obscene username. Hopefully someone else will complete your solution.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
No problem:Originally Posted by JJW_SG
Capture.JPG
Check conditional formatting in file. You can add underline in there.
Noted Glenn Kennedy and KOKOSEK solution solve 90% of what im trying to achieve.
Give me couple of days to test before closing as solve.
Wishing all a Merry Christmas!!
Hello KOKOSEK,
I got too carried away and forgot about additional fees, remarks and bank details to be mention on the invoice. See attached.
Additional fees are bit tricky as it can be either 1,2,3 or none of them.
What conditions are to apply extra fees? When 1, when 2 and when all 3?
I've added 'dynamically' located Discount (as I understand is always applied) and also 'dynamically' coloured Remarks fields.
Based on conditions in IFs I believe that You should be able to to do the same for bank details coloured field. (Check also CF)
p.s. Did you consider to create this in VBA? I quite sure that can be much easier (loop through lines to shows them on inv then add few lines of summary that's it).
Hi KOKOSEK,
Yes. I was expecting a VBA solution initially but you came out with much simple solution.
From what i have gather so far, vba should be like
1. Set the 20items in dynamic range.
2. Name the Total, Remarks and Bank detail as "Footer"
3. Move footer according to last item of the range.
What do you think?
The condition for the fees is when i key in the master!$p$2:$r$4 and the color green and yellow is just to catch your attention not intended to show on actual
Check attached file. I've written very simple code, so I suppose that you should not get any problems with tweak the rest.
Pressing button recreate INV, and adding remarks etc.
Thank you KOKOSEK. I twitch slightly to the vba and works wonder for me.
Happy 2022 ahead!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks