• 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.

Need data automatically on sheet2 (result)

Hi Team,
In my excel their are two sheet 1st with All data and 2nd with Result.
needed data automatically such as invoice base , invoice tax and invoice total with split into currency such as INR and USD .
And also needed No of invoices as per Vendor name in sheet1 as per ( Workflow not intiated,All unpaid invoices,All paid invoices,Rejected By Finance,Pending for Processing with Finance,Rejected by CIPC,Pending with Regional Spoc,Rejected with L2)
Thanks in advance .
 

Attachments

  • new excel (2).xlsx
    52.7 KB · Views: 6
Last edited:
Hi Team,
As previous thread is closed now please help me with this
This is a new file
As per ur guidline i had posted this post here .......
In my excel their are two sheet 1st with All data and 2nd with Result.
needed data automatically such as invoice base , invoice tax and invoice total with split into currency such as INR and USD .
And also needed No of invoices as per Vendor name in sheet1 as per ( Workflow not intiated,All unpaid invoices,All paid invoices,Rejected By Finance,Pending for Processing with Finance,Rejected by CIPC,Pending with Regional Spoc,Rejected with L2)
Thanks in advance .
 

Attachments

  • new excel (2).xlsx
    52.7 KB · Views: 0
chiranjiv poojari
Previous is closed because ONE thread per thread.

Your have already two times copy & paste same Your texts.
You should active and add something new information instead old.
 
Hi Team,
In my excel sheet their are two sheet in 2nd sheet i want all data automatically such as (invoice base , invoice tax and invoice total with split into currency such as INR and USD and no of invoices count as per bifurcation in the excel sheet.
I had attached my file you can help me in that only
Thanks in adavence for your advice .
 

Attachments

  • new excel (2).xlsx
    54.9 KB · Views: 7
chiranjiv poojari
Did You give new information with previous file? ... I didn't find.
Did You give a sample results?
Hint: Instead of waiting
... You should give more information
... or learn to wait.
 
Hi Team,
Thank you for your guidness in future i will adhere to it .
In my excel sheet their are two sheet in 2nd sheet i want all data automatically such as (invoice base , invoice tax and invoice total with split into currency such as INR and USD and no of invoices count as per bifurcation in the excel sheet.
I had attached my new resolution file in that you can find sample data .how i want the file .
For eg : vendor : STT Global Data Centres India Private Limited invoices has been it may be Pending for Processing with Finance ,Rejected By Finance,Rejected by CIPC,Pending with Regional Spoc etc and also i want the number of count of that invoices in the no of invoice cell in sheet 2(result)
 

Attachments

  • new excel (2).xlsx
    55.1 KB · Views: 4
  • new excel (2) resolution.xlsx
    54.6 KB · Views: 2
Hi team,
In my Worksheet im trying to get data automatically in sheet2 by using sumifs formula but instead of getting the right result im getting something else.
Please rectify where im getting wrong?
Sheet1 as original data
I want data automatically in sheet 2 such as data should be split as per currency INR and USD with invoice base , invoice tax and invoice total and count of invoices as per status(Workflow not intiated ,All unpaid invoices , All paid invoices etc..)

I had resoultion sheet also how i want the data ????
 

Attachments

  • new excel (2) resolution.xlsx
    54.6 KB · Views: 3
  • new excel (2).xlsx
    54.8 KB · Views: 4
chiranjiv poojari
Excel works...
Have You checked that ONE formula ...
Screenshot 2019-10-20 at 13.46.33.png
Screenshot 2019-10-20 at 13.45.51.png
What else could Excel give for result?
a) do You would like to get sum from Q-column?
b) what are criteria ranges and those criterias?
c) Your result is 0 (zero) ... for some good reason it's shows as a date
d) for how many cells would You try to get results?
... for me, if for many cells ... I won't use formulas!
 
Hello,

Yes i had use this formula,=SUMIFS('All Data'!$Q:$Q,'All Data'!$J:$J,'All Data'!$H:$H,'All Data'!$AF:$AF,$C$8)

a) do You would like to get sum from Q-column? Yes... but its only a base amount but i also want tax amount and total amount which are in the column R and S should appear directly on sheet2 (result) under invoice tax and invoice total amount .

b) what are criteria ranges and those criterias?
Vendor are in (J5:J115)
Base amount are in (Q5:Q115)
Tax amount are in (R5:R115)
Total amount are in (S5:S115)
Status are in (AF5:AF115)


d) for how many cells would You try to get results?Till now i had not got result for single cell


Can you suggest some thing else ...How in sheet 2 data should be split as per currency INR and USD with invoice base , invoice tax and invoice total and count of invoices as per status(Workflow not intiated ,All unpaid invoices , All paid invoices etc.)
 
chiranjiv poojari
Your used formula
... seems that You should check even my snapshots ...
then that would work as You need!

Do not copy & paste same phrases ... please.

Check this file
There is [ Do It ]-button
Click It
... and Your data has some challenges ... some of those I cleared - not all!
 

Attachments

  • new excel (2).xlsb
    40.6 KB · Views: 8
chiranjiv poojari
Can You ... :
I could ... but You won't need - as You couldn't see anything from my previous snapshots.

USD:
changed one letter

Issues:
... there were many spaces in unwanted places
... Pending with L2 ... Okay ... but that cannot show in Result!
Screenshot 2019-10-20 at 16.52.44.png
 

Attachments

  • new excel (2).xlsb
    40.7 KB · Views: 3
Hello,

I had added Pending with L2 column in result sheet
I had hided all the unwanted data

Needed rectification :
1) USD amount in USD cell in result sheet.

Error
Eg.For TATA TELESERVICES (MAHARASHTRA) LTD only 1 invoice is in rejected by Finance but its showing 2 in rejected by finance and this issue is for mostly all the vendor .
Needed as per satuts in no of invoices cell.

Please find the new rectified attachment.
 

Attachments

  • Macro file vendor.xlsm
    71.9 KB · Views: 11
chiranjiv poojari
You would express
... You would like to have ...
... if You need something ... then You also need to do something

1) eg If You still use older version then there cannot be the latest modifications.

'Error':
Have You explained, how would those values should solve? NO!

As many has written
... Do needed.
 
Hello,
1) I want the status of the vendor in sheet2 :
Vendor such as (Telstra Singapore Pte. Ltd ,Verizon Communications Singapore Pte Limited,Tata Communications Limited)
Status such as ( Pending for Processing with Finance,Rejected By Finance,Rejected by CIPC,Rejected with L2)
For eg : If Tata Communications Limited has 4 invoices out that 3 invoices got rejetced by cipc and 1 invoice is Pending for Processing with Finance then 3 invoices should show in no of invoices column under rejected by cipc with collated amount for all the 3 under single cell and 1 invoice should show under should Pending for Processing with Finance )
But some vendor has currency in INR as well as USD then amount should appear under INR cell and USD cell .

For eg : For your reference :

I had attach print screen for the same
63551

63552
 
Last edited:
1) Seems that NOTHING works, because You wrote so long text ... hmm?
... and You skipped to test that modified version --- it work other way!

2) 'Your' Sheet1 All Data
... I don't know which data do You use!
... I cannot find those terms ... nor datas --- I use data from my used file.

'Tata Communications Limited' ... or all near from that ...
Something like below could find from 'All data'
three different Vendor A's
Screenshot 2019-10-20 at 20.13.20.png
... and after [ Do It ] could see something like below.
Screenshot 2019-10-20 at 20.13.51.png
Seem that rows 10 & 107 have same Vendor/Status ... all results would fill to row #10.

Your 'some vendor has currency in INR as well as USD then amount should appear under INR cell and USD cell .'
is as useful as if I could explain that 'There are many people in India. Some are left handed - some are right handed ... and some don't use hands at all. Some left handed should count as right handed.' Can You solve that all values will be correct?
 
Hello Sir ,

I will modify the sheet2 .
I will make only 1 vendor with similar name in sheet2
For eg if TATA communications has 3 invoices in sheet1 i will create only one vendor name with tata communication in sheet 2 .
Means any name start with Tata communications in sheet 1 should directly collated under one vendor name n sheet 2.

Can you help me after that because it will be easy for you as well as for me .
 
Last edited:
Back
Top