How to create a new sheet for every item in a column

[Nathan2121]

Hi everyone,

I'm new to this website and forum but I am having a lot of trouble with a function in excel and I thought I would ask here to see if it is even possible.

I'm trying to create a workbook for my boss and he wants a list of all of our customers in the first sheet. However he wants to be able to click on a customers name and have it bring you to a new sheet specifically for that customer or company.

I started to do this with hyperlinking each customer name to their own sheet but this is not optimal seeing as we have over 300 customers and clients.

If anyone would help me out with this that would be amazing. If you need more information I would be glad to give you as much as I can.

Thank you!

 

[Smitty]

Welcome to the Board!

It sounds like you're trying to display some kind of customer metrics, so do you have aggregated customer data? If so, I'd create a Pivot Table (or a bunch of them) in a Dashboard type format, then use a Slicer to list the Customer names. Un-sliced and you display details for all customers, but click on a Customer name and the pivot table(s) automatically redraw to display just that customer's details.

If you follow the HTML Maker link in my sig you can post a screen shot of your data (sample data only, please). Once we see that we can come up with more recommendations.

 

[Nathan2121]

Thank you for your reply!!

As of right now I'm basically created this workbook from scratch so I don't have any data at the moment. I'm just trying to get it all setup so we can continually update it.
I'm sorry if this doesn't really make sense, I'm trying to figure it out as I go. I think a pivot table is a good idea though but unfortunately my version of excel doesn't have the slicers option (to my knowledge)

Again, thank you very much for the reply!

 

[Smitty]

The best place to start is with the raw data, then envision what/how you want to report it. Do you have a data source that you can pull from? Access, SQL Server, AS400, etc.? Excel can link to pretty much any ODBC compliant source.

As for Slicers, what version of Excel are you using. If it's 2010+ then you've got Slicers.

Otherwise you can put the Customer name in the Report Filter Field, but that's not as intuitive. But this does lead to a cool option if the boss insists on 300 sheets (which is just plain silly): with Customer name in the Report Filter Field, you can use the Show Report Filter Pages option, which will create a new Pivot Table worksheet for each name in the list. Then, if you do a search for "Zack Barresse Table of Contents" you'll find some code that will create a Hyperlinked TOC for you. Let me know if you can't find it and I'll see if I can dig it up.

But the single report worksheet with Slicers is definitely the way to go if you can!

 

[Nathan2121]

I was planning on entering in the data as I go because all of the information that we need is in quickbooks, and I'm not quite sure if you can pull the data from that source.

I have excel 2011 but it is for mac. I'm not sure if I pointed this out but my skills with excel are relatively basic unfortunately. I tried making a pivot table with some fake information just to get the format, but I am having trouble with just that basic tool. The column headers are customer name, Phone number, and Notes. I tried creating a pivot table with just that and it doesnt seem to be working for me at all. It keeps summing the phone numbers and notes for some reason.

I really appreciate you helping me with my problems and I'm sorry if I'm in the wrong section of the forum.

 

[Smitty]

There are plenty of options to download from QB into Excel. Here's a free add-in: QB2Excel - Free Utility to Convert QuickBooks® to Microsoft Excel® or Microsoft Access®

Or look at QB's support forum for options. I believe that there's a native functionality in QB to do it too.

Unfortunately, Slicers aren't supported in Mac Excel as of yet, but we've been asking for it. One option would be to use Parallels and run Windows/Office 2010+, but that is an added expense. You could do things programmatically with VBA, but if you're just getting the hang of Excel that's probably not an option.

This link might help for creating PT's on the MAC: https://www.youtube.com/watch?v=AvgAywFMfZA

As for the summing part, drag the Phone Number and Notes field from the Values field over to the Rows field and you should be fine.

I'm sorry if I'm in the wrong section of the forum.

No worries, you're in the right place (and if you weren't we'd move it for you. )

 

[sfournier]

Welcome to the Board!

It sounds like you're trying to display some kind of customer metrics, so do you have aggregated customer data? If so, I'd create a Pivot Table (or a bunch of them) in a Dashboard type format, then use a Slicer to list the Customer names. Un-sliced and you display details for all customers, but click on a Customer name and the pivot table(s) automatically redraw to display just that customer's details.

If you follow the HTML Maker link in my sig you can post a screen shot of your data (sample data only, please). Once we see that we can come up with more recommendations.

Hello, I'm new as well having just found this forum today and my excel knowledge is likely moderate at best. I'm trying to solve the same problem but for slightly different reasons, let me give you a breakdown of what I'm trying to accomplish and hopefully someone here can make some suggestions.

1. On a summary page, I'm listing each of my team members that will have billable time that week.
2. On that same page, I'm entering all their hours
3. I have a timesheet generated in a separate tab in which I was able to have it populate the name of the first person in the column using a vlookup and macro for renaming the tab.

I'm hoping to find a way where excel will automatically create a worksheet (timesheet) for each employee listed in the summary page. I'm thinking some macro could do it but I'm not quite good enough to figure it out .

Hopefully someone here has a better idea of it

Thanks in advance

 

[Jim Gordon]

Unfortunately, Slicers aren't supported in Mac Excel as of yet

Sorry, but this is not correct. Slicers are fully supported in Excel 2016 for Mac.