• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Invoice Automation

A

ashish mehra

Active Member
Hello all,

I have attached couple of workbooks, Invoice Base & Invoice Format.

Working:

Invoice Base fIle has 2 sheets, from each of the sheet 2 sheets needs to be populated in Invoice Format file.
I have used links for sample/demo point. There are 30 + fields in each of the sheet.

To start with I have taken 1 case in Base file, there are many cases in real data.

We need to consider Client Name & State as Unique identifier.
Ex: If same client has taken Asset in another state then another Invoice Format sheet to be created at desktop folder.

Regards,
AM
 

Attachments

  • Invoice Base Data ver 2.xlsx
    42.4 KB · Views: 11
  • Invoice Format ver 3.xlsx
    69.5 KB · Views: 8
You are looking automated invoicing based base data but bit confusing here. Think how will u share the idea of invoicing to novice & then write it here!
 
Deepak said:
You are looking automated invoicing based base data but bit confusing here. Think how will u share the idea of invoicing to novice & then write it here!
Click to expand...
Dear Deepak,

Yes you are right.
I have started this project, I will take care of any novice activity.

In Invoice Base file there are Company (Col Z) & State (Col AF), if we concatenate them then we can have unique identifier.
As requirement is to generate invoice on the basis of Company & State separately, we can lookup the unique field & fetch the matching values.

To avoid any confusionI, have made sample file with links from Base to Template.

Or if you suggest I can break the process into 2-3 steps.

Regards
AM
 
Yes, Invoice is unique.

I got it wrong of concatenating Company & State.

The idea is from list of unique Invoice number,
we have to create multiple templates saved on desktop folder.

Regards
AM
 
ashish mehra

We need to consider Client Name & State as Unique identifier.
Could You name any Client Name from any of You sample files?
>> 1st User-ID >> after that could know who is who!

... created at desktop folder.
Where ... is there any files?

>> Please, reread You writings ... and think ...
what kind of image of this case would others get?

Ps. You could/should wait ... until someone could/would reply to You case here.
 
vletm said:
ashish mehra
We need to consider Client Name & State as Unique identifier.
Could You name any Client Name from any of You sample files?
>> 1st User-ID >> after that could know who is who!
... created at desktop folder.
Where ... is there any files?

>> Please, reread You writings ... and think ...
what kind of image of this case would others get?

Ps. You could/should wait ... until someone could/would reply to You case here.
Click to expand...
@vletm

I got it wrong. Unique identifier is Invoice number (Col AI).
Files are attached.
 

Attachments

  • Invoice Base Data ver 2.xlsx
    42.4 KB · Views: 2
  • Invoice Format ver 3.xlsx
    69.5 KB · Views: 2
ashish mehra ... same files?
As I wrote in #6 Reply.
Still,
I didn't find any 'Client Name'!
nor
I didn't get any answers!
..
If Invoice Number is something,
then why You won't use it?
or
Could it be even user-ID? no! If You don't use it as user-ID!

>> I already once asked ... please and so on ...
 
vletm said:
ashish mehra ... same files?
As I wrote in #6 Reply.
Still,
I didn't find any 'Client Name'!
nor
I didn't get any answers!
..
If Invoice Number is something,
then why You won't use it?
or
Could it be even user-ID? no! If You don't use it as user-ID!

>> I already once asked ... please and so on ...
Click to expand...
@vletm

Sorry for confusion I read Company as Client.

Initially, I thought of concatenating Company & State & use as unique identifier but as per suggestion from Deepak about taking Invoice number as Unique identifier. I drop my idea & taken Invoice number (Col AI).

1st User-ID >> after that could know who is who! (I did not get you)
... created at desktop folder (Each template needs to be saved at desktop in a specific folder).

Hope I answered your concerns except one.
 
ashish mehra

Header should be same in the bold file as per invoice.

I have linked the "Invoice-Leasing" sheet

If every thing is ok then few line of code will make it automatic.

>Make sure base dump is open.
>Make a list of Invoice Id's.
>Start looping it.
>Do Advance filter & pate data to "Invoice Annex-Leasing"
> Add the subtotal.
>Put data in 3 orange cells

Print/Save....
 

Attachments

  • Invoice Format ver 3.xlsx
    79.3 KB · Views: 13
ashish mehra
Invoice number as User-ID ...
What about next time?
Do You really use always same Invoice number?
How do You know which Invoice (number) has completed and which not?
For me, Invoice number is unique too.
but ... if it is okay for You ... no way!
But then there cannot be challenge with 'user & state' neither.
Every 'user' ( = Invoice number) will use always same ID,
no matter from which state!
... this do not match with me!
Okay!
 
vletm said:
ashish mehra
Invoice number as User-ID ...
What about next time?
Do You really use always same Invoice number?
How do You know which Invoice (number) has completed and which not?
For me, Invoice number is unique too.
but ... if it is okay for You ... no way!
But then there cannot be challenge with 'user & state' neither.
Every 'user' ( = Invoice number) will use always same ID,
no matter from which state!
... this do not match with me!
Okay!
Click to expand...
@vletm

Invoice number as User-ID ...
Ans: Got it

What about next time?
Do You really use always same Invoice number?
Ans: I have reconfirm from concern person.

How do You know which Invoice (number) has completed and which not?
Ans: We are in stage 1 of this project. I will enquire on this.

For me, Invoice number is unique too.
Ans: Great.

but ... if it is okay for You ... no way!
Ans: No comments

But then there cannot be challenge with 'user & state' neither.
Ans: Yes, we can save by Customer name & state (helps when we search for any Company or state)

Every 'user' ( = Invoice number) will use always same ID,
no matter from which state!
Ans: This can be because I have made lots of changes from original data.

... this do not match with me!
Hope I clarify your all the questions.

Can you please consider Invoice number as Unique identifier as of now & share the code.

Regards
AM
 
Deepak said:
ashish mehra

Header should be same in the bold file as per invoice.

I have linked the "Invoice-Leasing" sheet

If every thing is ok then few line of code will make it automatic.

>Make sure base dump is open.
>Make a list of Invoice Id's.
>Start looping it.
>Do Advance filter & pate data to "Invoice Annex-Leasing"
> Add the subtotal.
>Put data in 3 orange cells

Print/Save....
Click to expand...
@Deepak

Thanks for your time but this is not my requirement.

Output template has 4 sheets & Invoice-Leasing sheet is one of them.
 
ashish mehra
1) As I wrote ... this do not match with me!
If Invoice number is used as Unique identifier.

2) Where need to get some code?
You wrote that "I have reconfirm from concern person"
then there won't be any challenge anywhere!
Everything would go as a dream ....
every client has own Unique identifier!
 
You must log in or register to reply here.
Back
Top