Create tables on multiple sheets from list

Hey everyone,

I have been tinkering around with excel a bit, and I am curious is I could 
get the following done in a macro.

I have a sheet with some lists. Each list has different values from the 
other list. Now I want to combine these lists into a table (not a pivot, 
because data has to be inserted later) along with a calendar, maybe in a 
template if that makes it easier, and then add new sheets with the name and 
referenced table according to the lists. To give the example, starting from 
A1 on Sheet 1.


List A           List B           List C             Calendar
   1                 2                 3                January
   4                 5                 6                Feb


Then I want to have the following to end up in a table and be put in a new 
sheet

Sheet 2 (named List A "Something")

List A                  January                                     February
               # Days                 %                    # Days            
    %
   1       'manual input'    'formula here'    'manual input'    'formula 
here'
   4       'manual input'    'formula here'    'manual input'    'formula 
here'

Sheet 3 (named List B "Something")

List B                   January                                      February
               # Days                  %                    # Days           
   %
   2       'manual input'    'formula here'    'manual input'    'formula 
here'
   5       'manual input'    'formula here'    'manual input'    'formula 
here'

And so on, untill it has processed all the lists. (There are now 4 in total, 
but more can be added later). Regarding the formula, I am planning to put a 
'manual input'/vlookup(###) kind of formula in there.

This is, for me, quite difficult, and I am not sure if that is even 
possible. Maybe making a template of the tables are better, I have no idea. 
Haha. But any help is welcome.

Thanks!
0
Utf
4/12/2010 9:26:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
941 Views

Similar Articles

[PageSpeed] 40

What you want is doable but you may want to consider keeping on ONE 
sheet>adding a column a,b,c and then using data>filter>autofilter>>>>>
List   etc
1
2
3
4
5
6

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"monden2" <monden2@discussions.microsoft.com> wrote in message 
news:0B2BFD14-3FAA-4698-9DC6-DF2EC3D020DE@microsoft.com...
> Hey everyone,
>
> I have been tinkering around with excel a bit, and I am curious is I could
> get the following done in a macro.
>
> I have a sheet with some lists. Each list has different values from the
> other list. Now I want to combine these lists into a table (not a pivot,
> because data has to be inserted later) along with a calendar, maybe in a
> template if that makes it easier, and then add new sheets with the name 
> and
> referenced table according to the lists. To give the example, starting 
> from
> A1 on Sheet 1.
>
>
> List A           List B           List C             Calendar
>   1                 2                 3                January
>   4                 5                 6                Feb
>
>
> Then I want to have the following to end up in a table and be put in a new
> sheet
>
> Sheet 2 (named List A "Something")
>
> List A                  January 
> February
>               # Days                 %                    # Days
>    %
>   1       'manual input'    'formula here'    'manual input'    'formula
> here'
>   4       'manual input'    'formula here'    'manual input'    'formula
> here'
>
> Sheet 3 (named List B "Something")
>
> List B                   January 
> February
>               # Days                  %                    # Days
>   %
>   2       'manual input'    'formula here'    'manual input'    'formula
> here'
>   5       'manual input'    'formula here'    'manual input'    'formula
> here'
>
> And so on, untill it has processed all the lists. (There are now 4 in 
> total,
> but more can be added later). Regarding the formula, I am planning to put 
> a
> 'manual input'/vlookup(###) kind of formula in there.
>
> This is, for me, quite difficult, and I am not sure if that is even
> possible. Maybe making a template of the tables are better, I have no 
> idea.
> Haha. But any help is welcome.
>
> Thanks! 

0
Don
4/12/2010 1:12:09 PM
Hey Don,

Thanks for the tip.

However, it is not necesarly the adding of column that is the problem. The 
point is that these lists need to be converted into tables as the headers and 
columns of the table, and then copy/pasted on new sheets. 

After that, the title of the table as well as the sheets should reflect the 
value of a cell in the list (in this case the first one of the list)

And then, finally, the table should have a formula (i.e. =B3/D3) in the % 
column of each month.

I was hoping that there would be a macro fo some sort, that could do this 
process for me. The 'copy/past on new sheet' event is no problem, but the 
'convert list to table headers' event is a dousy.

Thank for the help! :D

"Don Guillett" wrote:

> 
> What you want is doable but you may want to consider keeping on ONE 
> sheet>adding a column a,b,c and then using data>filter>autofilter>>>>>
> List   etc
> 1
> 2
> 3
> 4
> 5
> 6
> 
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "monden2" <monden2@discussions.microsoft.com> wrote in message 
> news:0B2BFD14-3FAA-4698-9DC6-DF2EC3D020DE@microsoft.com...
> > Hey everyone,
> >
> > I have been tinkering around with excel a bit, and I am curious is I could
> > get the following done in a macro.
> >
> > I have a sheet with some lists. Each list has different values from the
> > other list. Now I want to combine these lists into a table (not a pivot,
> > because data has to be inserted later) along with a calendar, maybe in a
> > template if that makes it easier, and then add new sheets with the name 
> > and
> > referenced table according to the lists. To give the example, starting 
> > from
> > A1 on Sheet 1.
> >
> >
> > List A           List B           List C             Calendar
> >   1                 2                 3                January
> >   4                 5                 6                Feb
> >
> >
> > Then I want to have the following to end up in a table and be put in a new
> > sheet
> >
> > Sheet 2 (named List A "Something")
> >
> > List A                  January                           February
> >               # Days                 %            # Days               %
> >   1    'manual input'  'formula here'  'manual input'  'formula here'
> >   4    'manual input'  'formula here'  'manual input'  'formula here'
> >
> > Sheet 3 (named List B "Something")
> >
> > List B                  January                           February
> >               # Days                 %            # Days               %
> >   2    'manual input'  'formula here'  'manual input'  'formula here'
> >   5    'manual input'  'formula here'  'manual input'  'formula here'
> >
> > And so on, untill it has processed all the lists. (There are now 4 in 
> > total,
> > but more can be added later). Regarding the formula, I am planning to put 
> > a
> > 'manual input'/vlookup(###) kind of formula in there.
> >
> > This is, for me, quite difficult, and I am not sure if that is even
> > possible. Maybe making a template of the tables are better, I have no 
> > idea.
> > Haha. But any help is welcome.
> >
> > Thanks! 
> 
> .
> 
0
Utf
4/13/2010 6:49:01 AM
      If desired, send your file to my address below. I will only look if:
      1. You send a copy of this message on an inserted sheet
      2. You give me the newsgroup and the subject line
      3. You send a clear explanation of what you want
      4. You send before/after examples and expected results.


-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"monden2" <monden2@discussions.microsoft.com> wrote in message 
news:6433B660-0DEA-4AB8-A10A-C2EEBBF6FE01@microsoft.com...
> Hey Don,
>
> Thanks for the tip.
>
> However, it is not necesarly the adding of column that is the problem. The
> point is that these lists need to be converted into tables as the headers 
> and
> columns of the table, and then copy/pasted on new sheets.
>
> After that, the title of the table as well as the sheets should reflect 
> the
> value of a cell in the list (in this case the first one of the list)
>
> And then, finally, the table should have a formula (i.e. =B3/D3) in the %
> column of each month.
>
> I was hoping that there would be a macro fo some sort, that could do this
> process for me. The 'copy/past on new sheet' event is no problem, but the
> 'convert list to table headers' event is a dousy.
>
> Thank for the help! :D
>
> "Don Guillett" wrote:
>
>>
>> What you want is doable but you may want to consider keeping on ONE
>> sheet>adding a column a,b,c and then using data>filter>autofilter>>>>>
>> List   etc
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>>
>> -- 
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett@gmail.com
>> "monden2" <monden2@discussions.microsoft.com> wrote in message
>> news:0B2BFD14-3FAA-4698-9DC6-DF2EC3D020DE@microsoft.com...
>> > Hey everyone,
>> >
>> > I have been tinkering around with excel a bit, and I am curious is I 
>> > could
>> > get the following done in a macro.
>> >
>> > I have a sheet with some lists. Each list has different values from the
>> > other list. Now I want to combine these lists into a table (not a 
>> > pivot,
>> > because data has to be inserted later) along with a calendar, maybe in 
>> > a
>> > template if that makes it easier, and then add new sheets with the name
>> > and
>> > referenced table according to the lists. To give the example, starting
>> > from
>> > A1 on Sheet 1.
>> >
>> >
>> > List A           List B           List C             Calendar
>> >   1                 2                 3                January
>> >   4                 5                 6                Feb
>> >
>> >
>> > Then I want to have the following to end up in a table and be put in a 
>> > new
>> > sheet
>> >
>> > Sheet 2 (named List A "Something")
>> >
>> > List A                  January                           February
>> >               # Days                 %            # Days 
>> > %
>> >   1    'manual input'  'formula here'  'manual input'  'formula here'
>> >   4    'manual input'  'formula here'  'manual input'  'formula here'
>> >
>> > Sheet 3 (named List B "Something")
>> >
>> > List B                  January                           February
>> >               # Days                 %            # Days 
>> > %
>> >   2    'manual input'  'formula here'  'manual input'  'formula here'
>> >   5    'manual input'  'formula here'  'manual input'  'formula here'
>> >
>> > And so on, untill it has processed all the lists. (There are now 4 in
>> > total,
>> > but more can be added later). Regarding the formula, I am planning to 
>> > put
>> > a
>> > 'manual input'/vlookup(###) kind of formula in there.
>> >
>> > This is, for me, quite difficult, and I am not sure if that is even
>> > possible. Maybe making a template of the tables are better, I have no
>> > idea.
>> > Haha. But any help is welcome.
>> >
>> > Thanks!
>>
>> .
>> 

0
Don
4/13/2010 12:18:42 PM
Reply:

Similar Artilces:

create a roster
How can you create a weekly roster in which functions can be assigned to more than 10 people. The roster should be updatable, so that when holidays and days off are marked on the weekly roster they will be taken account of it future rosters. ...

How to stop "Undo" across multiple spreadsheets
When I am working between multiple separate worksheets, Undo will work backwards across all of the worsheets that I have open. How do I get the Undo function to work only on the worksheet that I am currently on? ...

importing or creating reference footnotes
I am trying to import a word document into publisher and it has reference footnotes within the document. The reference footnotes are not coming into the publisher document with the reference footnotes. Correct, they will not up to Publisher 2003. In Publisher 2003 depending on which version of Word you are using if they will or not. -- "If you don't know where you are going, any road will take you there!" ...

Mailbox not created when AD account created
A couple of days ago I noticed that I created a user account but the 'Email Addresses' tab of the user properies did not list any email addresses. Subsequent new user accounts have all experienced the same. I researched the knowledge and tried the recommended solutions to either manaully start the recipient update service or rebuild te recipient update service. Neither recommendation has resulted in a mailbox being created for this particular user or any additional user that I have created since this error first showed up. I performed a Windows update last Thursday and so I bel...

Print Autocomplete list
I am using Outlook 2003 and have been unable to find out any way of viewing and printing a copy of all of the entries in the Autocomplete list. Is this possible? A reference to a helpfull article or tutorial would be appreciated. TIA There's this tool which may help: http://www.nirsoft.net/utils/outlook_nk2_autocomplete.html "KiwiBrian" <briantoz@ihug.co.nz> wrote in message news:h99lja$fdl$1@lust.ihug.co.nz... > I am using Outlook 2003 and have been unable to find out any way of > viewing and printing a copy of all of the entries in the Autocomplete > lis...

Task Create - Status
I'm trying to make a simple task, when a user owner is assigned a task in CRM, it receives an email letting it know it has one. I have the following below, but it is not generating one when i assign one manually in the CRM. What to do? I've tried both the following: When task is created E-Mail to:[owner];Subject test As well as: When task is created if Task activity status = Open then E-Mail to:[owner];Subject test I've checked the mailserver many times over, and I can send emails manually from the CRM so you can rule that out Tested out ok on our end. Some ideas:...

Distribution List Max Number of Recipients
Does anyone know what the maximum number of recipients allowed in a distribution list is in Exchange 5.5 Thanks. "L. Wagoner" <anonymous@discussions.microsoft.com> wrote: >Does anyone know what the maximum number of recipients allowed in a distribution list is in Exchange 5.5? It's somewhere in the vicinity of 5,000. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Query on Group field in Pivot Table
I have a large database wherein under Date column, data is in the format "Thu Oct 1 12:00 AM". When I use Pivot Table Under OPTIONS->GROUP-> Group Field is disabled. I used this to group Date field to either present data by Month, quarter at one click. Can anybody guide me why this seems to be disabled here. I want to present my data only by Monthwithout changing the format ""Thu Oct 1 12:00 AM". Thanks Hi Shewta Check you Data Source of your Pivot. it should come up like "Sheet6!$G$1:$I$821" and not like "'\Documents and...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

Pivot Table update without opening the Excel file.
Hi, I have a spreadsheet saved as an xlt. It has three pivot tables which connect to CSV data files using Microsoft Text Driver. The CSV files are updated overnight by an external application. The Excel file is in \\Server\Viewers The Data files are in \\Server\Data Local users use a shortcut to open the Excel file on \\Server\Viewers which the does an auto-update from the CSV files. The pivot table data is updated. All that has been working perfectly for years, internally. Now I need to send the same excel file to another company by email. I have succeeded in writing a vbscript that creates...

Help Creating XmlNode / XmlAttribute Elegantly
I am creating a configuration class to read / write a standard configuration file. <?xml version="1.0" encoding="utf-8"?> <configuration> <appSettings> <add key="ConnectionString" value="server=(local);" /> </appSettings> </configuration> I am using a XmlDocument for my base implementation. I am looking to see if there is a better way to add a new node / attribute than using XmlDocument.InnerXml. I would consider this to be a hack but couldn't really see another way of doing this without definin...

Bug with cell merging in tables using Applescript
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Once you have merged cells in a table in Word 2008 using Applescript, any further reference to the table's cells in the script produce an error. <br><br>As an example, open Word 2008 and run this script: <br> tell application &quot;Microsoft Word&quot; <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;make new table at active document with properties {number of rows:4, number of columns:4} <br> &nbsp;&nbsp;&nbsp;&nbsp...

Splitting one table into multiple tables/tabs
Hi I was wondering if anyone can help me split a table into multiple tables or tabs. If I have a table of 20 customers with various transactions over time all in one table. I'd like to know if it's possible to seperate the table into multiple tables based on the change in customer number and have them broken into multiple tabs without manually creating each tab and then cut and pasting. I really appreciate any help! Thanks! ...

tabls in GP90 database
Does anyone know or have a description or documenation on the tables in the database for GP90? Maybe all I need is the important ones. thanks Within the program itself, Tools > Resource Descriptions Also, there are ERD's on the distribution CD's that you got when you purchased or upgraded the system - you have to install the SDK to get at them. -- Lyle U Adam Clark wrote: > Does anyone know or have a description or documenation on the tables > in the database for GP90? Maybe all I need is the important ones. > > thanks ...

Create external database
Hi all. I need to create an external database (file). On a site I found this code: ------------------------------------------------------ Function CreateLinkedExternalTable(strTargetDB As String, strProviderString As String, strSourceTbl As String, strLinkTblName As String) As String 'strTargetDB = Source Database Name 'strProviderString = Not used, currently hard coded 'strSourceTbl = Source Table name in the database we are linking too. 'strLinkTblName = Table name we would like to see in the Access Database. Dim catDB As ADOX.Catalog Dim tblLink As...

Data Tables
Has anyone ever had a problem where a data table is producing incorrect values? I have a (two dimensional) table which is producing unexpected values. When I try to replicate these values through manually changing the inputs (and changing nothing else) I get a different result to that produced by the data tables. Just to check I'm not going completely mad, I've had a colleague confirm the symptoms. Any suggestions gratefully received before I head into investigating for corruptions etc Mike The only time I've ever had a problem with charting is when I had merged c...

How to create pie chart with %
I have got a problem that I don't if excel could help me to calculate the % within a column and draw a pie chart. My case is I have a column containing a series of numbers. e.g. 1,4,8,1,3,9,11,2,4,5,6,.........etc I want to draw a pie chart that showing 1-3 , 4-6, 7-9, >9 with the corresponding % inside that column. Can anyone tell me how can I make it ? Ray - You need to calculate the frequencies in your data. Let's assume your data is in A1:A12. In B1:B3 enter these numbers, the upper limits on the groupings: 3 6 9 You can enter ">9" into B4 witho...

how do I copy a range to a new sheet
Now that I this group solved my problem of flagging dates of my club members as "expired", how would I automatically copy all of the members that are expired to a new worksheet so I can use this for a mail merge? I would need to check the condition of the expired column, YES or NO, then copy the yes's name and address columns only to a new page. TIA Why not just add that column and when you use mailmerge, just include the records with that flag set the way you need? If you really want to copy the rows, you could apply Data|Filter|Autofilter and filter to show just those mem...

Email address not created when a new user is created
All, This issue recently happened in my company. Upon creating a new user, the policy was set to create the smtp and X400 address automaticaly. Now when we create a user the smtp and x400 addresses are not created, and when attempting to setup the new user in outlook, the address cant be found. We are using exchange 2003 SP1 on Win2k3 servers. On Wed, 13 Jul 2005 06:49:07 -0700, "Bryan" <Bryan@discussions.microsoft.com> wrote: >All, >This issue recently happened in my company. Upon creating a new user, the >policy was set to create the smtp and X400 addres...

when form created
I have a form which was created by someone else (not a form template) which is like a questionnaire with empty boxes to be completed. When the boxes are typed in all the margins move to accommodate the words being typed. Is there a way to fix/lock the original form and then just fill in the blank boxes? I suspect this is set up as a table with auto resizing enabled. Displaying table gridlines (Table | Show Gridlines) will help you see what you're dealing with. Then select the table, go to the Table tab of Table Properties, click Options, and clear the check box for "A...

Exchange 2010 MRM, no log files being created
I'm hoping someone can help me with this issue....it has been driving me crazy for days. I have enabled MRM logging on our exchange server, and I can see that some of the policies are working when I run start-managedfolderassistant. We have a retention policy tag on the deleteditems folder, and this does work. The problem is I am not getting any files in the MRM log directory. C:\Program Files\Microsoft\Exchange Server\V14\Logging\Managed Folder Assistant Events 9021 and 9022 are logged in the Application log, but where are the files? Below is the output of my mai...

changing the message list in outlook 2003
i just upgraded from office 2000 to 2003. in the message look in outlook, it separates the messages by date. is there any way to turn this off, so i just have a long list of messages? Right click on the header in the message list, uncheck "show in groups." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Lee Benningfield asked: | i just upgraded from office 2000 to 2003. in the mess...

how many lines per sheet in excel 2003
ave they increased the number of lines in excel 2003 or is it still 65536 Still, ever heard of 'categorizing'? Danny On Sun, 17 Jul 2005 16:04:01 -0700, Des53 <Des53@discussions.microsoft.com> wrote: > ave they increased the number of lines in excel 2003 or is it still 65536 -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ Hi Des, Row and column specifications are unchanged in xl2003 (65536 x 256). I have heard no suggestion that these are likely to change any time soon --- Regards, Norman "Des53" <Des53@discus...

Select items from list and show in text box
I want to be able to select an items from a list I have setup and have them show in a text box (possibly with the aid of a select button). Could someone please explain to me how to do this as I have no idea about Visual Basic and macros don't work? Thanks in advance Jeanette You can create a dropdown list with data validation: http://www.contextures.com/xlDataVal01.html Or, create a combobox on a UserForm: http://www.contextures.com/xlUserForm02.html rosscojet@tiscali.co.uk wrote: > I want to be able to select an items from a list I have setup and have > them show in a ...

Creating a Brochure
How do you create a 3-column brochure from scratch for Publisher 2002? I can only find the directions for the 2003 version. Quick help would be appreciated, project is due Monday. Create a three-panel or four-panel folded publication http://office.microsoft.com/en-us/assistance/HP011646681033.aspx Scroll down to the section that says "Build a three-panel or four-panel publications from scratch." -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Derek" <Derek@discussions.microsoft.com> wrote in message news...