+ Reply to Thread
Results 1 to 13 of 13

Daily Reporting where item data changes on regular basis

  1. #1
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Daily Reporting where item data changes on regular basis

    Hi,

    I create a daily sale report in excel for merchants of the product to bill them as per sale/lead I generate on daily basis. My merchants send me price changes of the product very frequently almost twice-thrice in a week with daily amount limit I can bill them maximum per product. Based on those changes I have to create a report for them to bill monthly.

    I paste their changes request in different sheet date wise as given in attach file from where I take reference for product rate and daily spent limit. If i don't receive a change request on a particular date then I have to consider previous earliest date changes and use in my reporting. I am looking for a formula which can pull Rate and Limit/day from my change request sheet automatically by date and product id by delivery type.

    Any help will be appreciable.
    Attached Files Attached Files
    Last edited by sumit.tailshift; 04-14-2015 at 07:25 AM.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Daily Reporting where item data changes on regular basis

    Hi,

    Please see the file attached.

    I changed the format a bit..
    Attached Files Attached Files
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Daily Reporting where item data changes on regular basis

    Looking good to me, but I have a list of 1000 products and in your formula Rates and Limits on date 12 Apr, 2015 are 0 but it should be same as 11 Apr, 2015.

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Daily Reporting where item data changes on regular basis

    In my workbook the formula Rates and Limits on date 11 Apr, 2015 are zero as there is no data for that date on Change Request sheet. If you add the data for that date, the formula will work OK.

    Use this new file.. I have altered the formula to handle more change requests..
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,833

    Re: Daily Reporting where item data changes on regular basis

    Hi,

    Try this

    Punnam
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Daily Reporting where item data changes on regular basis

    Hi Punnam,

    Thanks for your time, but you put a formula in Change Request sheet which is static content, I need something for Report sheet. Any thoughts?
    Quote Originally Posted by Punnam View Post
    Hi,

    Try this

    Punnam

  7. #7
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Daily Reporting where item data changes on regular basis

    Can't we use HLOOKUP for date from report sheet to change request then INDEX/REFERENCE?

  8. #8
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Daily Reporting where item data changes on regular basis

    Thanks dear, but not solved my problem. Can't we use HLOOKUP for date from report sheet to change request then INDEX/REFERENCE?

    Quote Originally Posted by NeedForExcel View Post
    In my workbook the formula Rates and Limits on date 11 Apr, 2015 are zero as there is no data for that date on Change Request sheet. If you add the data for that date, the formula will work OK.

    Use this new file.. I have altered the formula to handle more change requests..

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Daily Reporting where item data changes on regular basis

    Didn't my solution get you the answer?

  10. #10
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Daily Reporting where item data changes on regular basis

    Your answer is very close but modification you made in Change Request is not feasible for me. Sorry for that
    Quote Originally Posted by NeedForExcel View Post
    Didn't my solution get you the answer?

  11. #11
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Daily Reporting where item data changes on regular basis

    Quote Originally Posted by sumit.tailshift View Post
    Your answer is very close but modification you made in Change Request is not feasible for me. Sorry for that
    What is wrong with the solution? Let me know, I will help you out..

  12. #12
    Registered User
    Join Date
    04-14-2015
    Location
    India
    MS-Off Ver
    Office 365
    Posts
    7

    Re: Daily Reporting where item data changes on regular basis

    Quote Originally Posted by NeedForExcel View Post
    What is wrong with the solution? Let me know, I will help you out..
    There are 2 reasons not using your solution which are:
    1. Format of Change Request, I have a list of 1000 products and for each product I may not receive price/budget change so I have to keep previous change request, if I paste in single column then it will be time consuming.
    2. As I said I may not receive daily change request in that case I have to use last date price/budget.

    Thanks

  13. #13
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Daily Reporting where item data changes on regular basis

    1. Format of Change Request, I have a list of 1000 products and for each product I may not receive price/budget change so I have to keep previous change request, if I paste in single column then it will be time consuming.
    2. As I said I may not receive daily change request in that case I have to use last date price/budget.
    Thanks
    For point number 1, it will be slightly time consuming, but that is the right way of handling data sets. for Point number 2, If you do not receive a change request, dont mention that product again, it will automatically get the price of the latest entered date..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pull data from one spreadsheet into another on a daily basis (workbook
    By david20 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2013, 10:53 AM
  2. need help with data summary based on a daily basis
    By randypang in forum Excel General
    Replies: 1
    Last Post: 04-26-2013, 11:45 PM
  3. Replies: 1
    Last Post: 03-21-2013, 10:45 PM
  4. Replies: 8
    Last Post: 10-12-2012, 12:35 PM
  5. [SOLVED] How do I set up report linking data that changes on daily basis?
    By SrMarsia in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1