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

Populate List of Names from data on another sheet

Michael Govea

New Member
Happy Monday Chandoo-ers!

I'm needing to run a daily report with data that consist of different store names that changes constantly.

Attached is an example of what I would like to do. One 3 tabs with data (invoice,pick & Open) and another with the report template "summary tab".

Instead of having to pivot a new report every time - I would rather open the same file, "plop" in new data everyday and have it automatically populate store names on a summary tab which remains constant, and from there total the numbers I need based on the data in invoice, pick and open buckets, based on customer name.

Am I over-complicating this? I also included an example in the last tab of how it should populate (excluded invoice, pick and open for example purposes)

Any thoughts or formula that would pull each customer name so all I would have to do it plop in the data and it's there? I feel like there should be a simple "list formula" if I'm not wrong.

Please feel free to ask any questions or need further explanation!

Thank you guys so so much! love this place. :)
 

Attachments

  • Report example.xlsx
    147.9 KB · Views: 4
Hi,

can you let us know how you get the disc rate from?

in the open tab, which one is considered as gross ?

also, if it is just to sum all three tabs with corresponding customer name, then you can put all three sheet data in one tab and make PT. in fact, there are n number of methods to achieve your results.

if you are looking for formula you can try like this in B5 cell last tab:
=SUMIF(INVOICE!A:A,$A5,INVOICE!B:B)+SUMIF(PICK!A:A,$A5,PICK!B:B)+SUMIF(OPEN!A:A,$A5,OPEN!C:C)

Regards,
Prasad DN
 
Hi Prasad,

Thank you for response! Oops, extended Price A is considered gross in the Open tab. I know how to sum all three with a sumif forumla :

I guess, i'm more interested on how to pull these list of names THEN sum based on the corresponding store name. So, how would i be able to pull those store names?
 
Hi Micheal,

Do you want to update customer list on summary tab whenever you open this file automatically?

Regards,

Hi SM,

That is correct! Technically, I would clear out the data from the day before and paste current days invoice, pick and open data, which new customer names are added regularly
 
@Michael Govea

See the file, it uses VBA + Named formula, whenever you will open file, it will update the customer list.

EDIT: Changed the file.

Regards,
 

Attachments

  • Report example.xlsm
    169.6 KB · Views: 2
Last edited:
@Michael Govea

See the file, it uses VBA + Named formula, whenever you will open file, it will update the customer list.

EDIT: Changed the file.

Regards,

Thank you.

Hmm.. i believe there's an error. I don't believe the VBA work when opened, as nothing populated some said customer list - (believe it has to do with merged cells) Also, when I past new data each day, would the customer list automatically populate on summary tab?
 
See the file now on my comment, I removed the bug.

Regards,


Hmm. One more thing. I'm a novice to VBA so i'm confused on where this "customer list" is. If I was to open this file, wipe out previous days invoice, pick and open data and paste new data - will it automatically populate the customer list and list again on the summary page - especially if there's a new account name?

Thank you for everything! I believe we're getting somewhere! resaved file
 

Attachments

  • Report example-1.xlsm
    113.6 KB · Views: 0
Actually the List of all the unique customer are on List Sheet G Column, which is updated automatically whenever you open the file. The a named formula customeList will generate the List from Sheet List G column.

This system will work when you paste new data and save the file then Open it again, do want to update as and when required?

Regards,
 
Ok great - makes sense now.

And yes, i would love to update when required - such as changing the template to this sheet

SM YOURE THE BEST
 
See your file.

Regards,

Hi again SM!!

Hoping you could help me out once more.

I'm trying to do the same thing but with a different formatted INVOICE, PICK & OPEN Data. I want the Customer Names to populate onto the summary page, however the VBA is coded to only pull from column "A2:A" from each tab.
I tried revising myself, which is why you'll see "F2:F" but it changes it on all three sheets to code the same thing.

Anyway to have Invoice Pull from column "F2:F", Pick E2:E and OPEN E2:E?
File attached, with new set of data.

Also, is it possible for you to add the button where it would refresh each time so I don't have to save file then open back up for it to populate?

YOU ARE THE VERY MOST HELPFUL HUMAN BEING!!

Thank you so so so much.

I believe we're really getting somewhere.

Michael
 

Attachments

  • Report example-5.xlsm
    263.3 KB · Views: 2
Hi Michael,

Check the file, I had added a button(BLUE SHAPE) on the SUMMARY sheet, if you press that it will refresh.

Just check and write back.

Regards,
 

Attachments

  • Report example.xlsm
    271.3 KB · Views: 10
Hi Michael,

Check the file, I had added a button(BLUE SHAPE) on the SUMMARY sheet, if you press that it will refresh.

Just check and write back.

Regards,

Wow SM, this looks like it's working.
I was checking the VBA and not sure what's changed but it seems to be working swimmingly.

If you have a moment, i'm interested on what was added or changed to reflect the different Customer Name ranges in each tab. Perhaps, i'm overlooking it

Thank you so so so so so much again!
 
Look for the line:

Code:
 k = Application.WorksheetFunction.Match("Customer Name", Sheets(i).Range("1:1"), 0)

This is matching and getting Customer Name header in row 1 to get column Ref.

Regards,
 
Back
Top