Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Populate Excel table with userform data

Hi,

I need help. I'm trying to populate an excel table with userform data. The table in excel actually occupies F31:O1048576. I need all rows except headers to have the same colour.

So is there a way to input data into the table while maintaining that every row has the same colour?

Attached is how the table looks like.


Post your answer or comment

comments powered by Disqus
This may be a bit confusing, but I was puzzling over this over the weekend
and can't figure out a solution. I'm assuming the answer to this lies in VBA.

I have in mind a template for calculating bond interest payments. What I
would like to do is to enter the number of periods, say 30, and have Excel
automatically populate a table with 30 coupon payments.

I can figure out all the bond math by myself. What I don't know how to do
is, given a value entered in a cell, have a table populate with a number of
rows equal to that value. So, if I decided that instead of a 30 period bond
I was interested in a 15 period bond, I could change 30 to 15, and Excel
would automatically shrink the table.

I doubt this is impossible, but I do know it's beyond my VBA knowledge.

Thanks,

Dave
--
Brevity is the soul of wit.

Hi, I love to use Excel tables to organize data because of the sorting capabilities. However, the table I am currently working with has the data field headings on different rows instead of columns. You can still sort all the data by clicking Data->Sort, etc. but is it possible to make a table in this vertical orientation so that I can easily sort by a row by clicking on the cell in the first column?

I hope my issue is somewhat clear. Please let me know if I need to clarify.

Thanks in advance!

I have been looking for this answer for many days now. Let me try to explain what actually I want to achieve.

My Boss wants me to make an excel file in which the footer has to be repeated on each page.But this footer is not a normal footer its more like a 4X4 table with some data and the company logo.Somebody pls tell me how to get it.

Is there a way to get this functinality in Excel

Hello,

Is it possible to populate an Access database table with an array compiled from within excel? For example, I have an array "aEvent(1,10)" one row by 10 fields that I want to load into an Access table. I've seen examples using SQL to load data from a worksheet but not from an array.

If this is possible, what would the syntax look like? I've setup the connection already just need to see an example of the array load.

Many thanks,
J

Hello All,

I am trying to create a table that populates with the results of a series of calculations performed on a different work sheet, lets call the work sheets "table" and "calculator". FYI there is also a worksheet called "data" that "calculator" pulls from, but i don't think it enters into this problem.

the head of each column and row (topmost and leftmost) are filled with increasing numbers. for example:

1 2 3 4 5 6 7
1
2
3-------*
4
5
6

what i am trying to do is to have the calculator pull these numbers and return the end value to the intersecting cell (ie 5 across and 3 down*). the calculator involves a fairly large if statement and a vlookup, otherwise i'd just fill each cell on the table with a formula.

I have attatched the excel file so you can see exactly what I'm talking about, and I will be checking the forum regularly in case my would-be hero needs any more information.

Any help or ideas would be greatly appreciated. A big thank you in advance,

Autotelic

I have an excel file with 2 tabs at present - Tab 1 has a full list of our Company’s stock codes and suppliers – these have been filtered to find and show only one specific suppliers codes so the row numbers are not showing as 1 – 116,000 but now are 191, 278, 355 etc. I now have only 1869 rows left from the original 116,000

Tab 2 has the same full list of all of stock items unfiltered totalling 116,000 rows

I need to pick up the first stock code in Tab 1 Col A, and then find all of the corresponding duplicate entries in Tab 2 Col A.

Then I want to copy and move all of these duplicate entry rows into a third tab making sure that each set of data rows are under each other creating a new table. I need to repeat this for all of the1869 entries in tab 1
I hope this is understandable - I would appreciate any help.

Hi
I am trying to do the above with the attcahed file.
This is to monitor training module completions.
The objective is to have the 'output' table as the data for charts etc.
Any help will be greatly appreciated
cheers
Shane

Using Excel 2002 on an XP system, I have developed Pivot Tables on three different worksheets, with the data on the mainworksheet of the Workbook. I am having trouble updating the records and getting the Pivot Table to update. I am pasting the data from another seperate workbook into the same location as teh old data. And then selecting the range of data and resetting the Name. I have then tried to use the Pivot Table Wizard to update the range but get problems in the existing Pivot Tables and a new blank Pivot Table is created. Is there a better way?

Dynamically Sourced Range Pivot Tables with Excel VBA.
A common problem with automating pivot tables is how to make the VBA generic so that when the number of rows or columns changes that the code sitll works. In this example it shows how to make a pivot table no matter how many rows or colums there are and also how to place this pivot table in a specific destination. Enjoy.

download sample book
http://programminglibrary.com/Progra...ta sources.xls


	VB:
	
 CreatPivot() 
    Call AutoPivot("Data", "Name", "Animal Type") 
End Sub 
Sub AutoPivot(strPivsheet As String, strPivCol1 As String, strPivCol2 As String) 
     'USE-EXCEL VBA CREATE SIMPLE PIVOT TABLE FROM COLUMNIZED DATA REGARDLESS OF NUMBER OF ROWS OR COLUMNS USING DYNAMIC RC
NOTATION
     'CREATED BY MARK SLOBODA
     'DOWNLOAD COOL EXCEL STUFF :http://programminglibrary.com/Programming%20Library/DOWNLOAD/downloads.aspx
     
     '************NOTES*****************************
     '1)  ASSUMES DATA STARTS IN 1,1
     '2)  IN THIS EXAMPLE YOU ARE USING VARIABLES TO MAKE DATA SOURCE SIZE OF NUMBER OF ROWS AND COLUMNS AND SHEET REGARDLESS
OF SIZE
     '3)  YOU COULD PASS AN ARRAY OF PIVOT COLUMN FIELD NAMES OR SIMPLY ADD OTHERS
     
     '**************** VARS PASSED *****************
     'strPivsheet - SHEET WHICH PIVOT TABLE WILL BE CREATED FROM
     'strPivCol1 - PivotColumn1 (Used for Row and Data Fields)
     'strPivCol2 - PivotColumn2 (Used for Column Field)
     
     'DEC VARS
    Dim lngR As Long '#ROWS-ALWAYS USE LONG TO AVOID MAX INTEGER ERROR
    Dim intC As Integer '#COLUMNS
     
     'SET VALS
    Sheets(strPivsheet).Select 'SELECT SHEET TO CREATE PIVOT TABLE ON
    Range("A1").Select 'SELECT A CELL IN ACTIVE DATA RANGE
    lngR = Range("A1").CurrentRegion.Rows.Count 'GET ROW COUNT ON ACTIVE SHEET
    intC = Range("A1").CurrentRegion.Columns.Count 'GET COLUMN COUNT ON ACTIVE SHEET
     
     'BEGIN CODE
     'CREATE THE PIVOT 2 VERSIONS
     
     '******************* VERSION 1 NO SPECIFIC DESTINATION SHEET ********************************
     '********************************************************************************************
     
     'CREATE PIVOT
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ 
    "'" & ActiveSheet.Name & "'!R1C1:R" & lngR & "C" & intC, TableDestination:="", TableName:="PivotTable1" 
     
     'ADD CENTER DATA FIELD!!! IMPORTANT - DO THIS FIRST FOR PIVOT ROW AND COLUMN FIELDS TO WORK CORRECTLY - PASSED VARIABLE
USED FOR PIVOT COLUMN
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields(strPivCol1), "Count of" & strPivCol1, xlCount 
     
     'ADD ROW FIELD (LEFT HAND VERTICAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT ROW FIELD
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol1) 
        .Orientation = xlRowField 
        .Position = 1 'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
    End With 
     
     'ADD COLUMN FIELD (TOP HORIZONTAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT COLUMN FIELD
    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol2) 
        .Orientation = xlColumnField 
        .Position = 1 'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
    End With 
     
     
     '******************* VERSION 2 SPECIFIC DESTINATION SHEET ********************************
     '*****************************************************************************************
     
     '    Sheets.Add    'CREATE A NEW SHEET OR ASSIGN EXISTING ONE TO VARIABLE
     '    strDynamicSheet = ActiveSheet.Name    'ASSIGN DYNAMIC SHEET
     '    Sheets(strPivsheet).Select    'GO BACK TO PIVOT DATA SHEET
     '
     '    'CREAT PIVOT
     '    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
     '                                 "'" & ActiveSheet.Name & "'!R1C1:R" & lngR & "C" & intC, TableDestination:="'" &
strDynamicSheet & "'!R1C1", TableName:="PivotTable1"
     '
     '    'ADD CENTER DATA FIELD!!! IMPORTANT - DO THIS FIRST FOR PIVOT ROW AND COLUMN FIELDS TO WORK CORRECTLY - PASSED
VARIABLE USED FOR PIVOT COLUMN
     '    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
     '                                                        "PivotTable1").PivotFields(strPivCol1), "Count of" &
strPivCol1, xlCount
     '
     '    'ADD ROW FIELD (LEFT HAND VERTICAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT ROW FIELD
     '    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol1)
     '        .Orientation = xlRowField
     '        .Position = 1    'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
     '    End With
     '
     '    'ADD COLUMN FIELD (TOP HORIZONTAL PART OF PIVOT TABLE) - PASSED VARIABLE USED FOR PIVOT COLUMN FIELD
     '    With ActiveSheet.PivotTables("PivotTable1").PivotFields(strPivCol2)
     '        .Orientation = xlColumnField
     '        .Position = 1    'IMPORTANT ONLY IF MULTIPLE FIELDS ADDED
     '    End With
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


How do I use the "auto-fill" capability in Excel to fill in a table with cell
data from several tabs?

Dear,

Can someone help me with the following please:
I created an access database and I import data from an excel sheet. With this excel sheet it is very easy to import into access because it is a normal table with first row containing fields. Nevertheless it is difficult for people to write data in it, because they have to repeat certain info all the time. Therefore, I rearranged the table so it is easier to use, but now, more difficult to import into access (or impossible). Can someone help me with a macro (and VBA) to get the data from one table to the other? I'm afraid it is difficult to understand, so I wanted to add an example file where it will be clear in one view. Unfortunately, I don't think we can add files, so please check the url: example excel file for an example...
Some info: Rows where value 3 is missing must be deleted while transponing!
Thank you very much for your help!!!
Kind regards,Olivier Nuytten

Hi,

I'm using Excel 2003 and hope someone here can help me.
With the help of other forum users I managed to get a macro which distributes values across a number of rows (code below).

In my Excel table shown below the yellow rows have been inserted with a macro.
The macro basically looks whether there is a value under SiteBalance (column P) and distributes that value across the number of rigs working for a site.
So for the example in yellow, the SiteBalance is $64,517.
The site in cell N21 is shown as 'NMM'.
The macro now looks at cell F8 and knows there are 8 rigs on that site so it's $64,517/8.
Row 13 to 20 are then inserted.
What I need help with is finding a way to insert the rig number as well in cell M13 to M20. Currently it only has the site description 'NMM' but I'm trying to get it to show all the rig numbers from the grey underlayed section on the top.

So for example if the site is NMM, the value s devided by 8 and in cells M13 to M20 the number of the rig is entered from cell G2 to T2.

With a formula I would do it like this:
=IF(COUNTIF($1:$1,M13),$E$1,IF(COUNTIF($2:$2,M13),$E$2,""))
I'm looking for a way to include this in my code however.

I'm really stuck so any help is greatly appreciated!

My Excel Table

Sub Spread_values()
    Dim aRows, AMatchCols
    Dim LR As Long, r As Long, RwsReqd As Long
    Dim i As Long, j As Long, k As Long, x As Long, z As Long
    Dim Amt As Single
    Dim Site As String, CSS1 As String, CSS2 As String
    
    Const FR As Long = 11 '<-- First Row of actual data
    Const NumSites As Long = 4 '<--No. of possible sites
    AMatchCols = Array("J", "K", "N") '<--Cols that must match (CSS)
    
    Application.ScreenUpdating = False
    x = UBound(AMatchCols)
    aRows = Range("E1:F" & NumSites).Value
    LR = Range("P" & Rows.Count).End(xlUp).Row
    Range("E" & FR & ":P" & LR).Sort Key1:=Range("J" & FR),
Order1:=xlAscending, _
        Key2:=Range("K" & FR), Order2:=xlAscending, Key3:=Range("N" & FR), _
        Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal
    r = LR
    Do
        If Cells(r, "P") <> 0 Then
            Site = Cells(r, AMatchCols(2)).Value
            i = 0
            Do
                i = i + 1
            Loop Until aRows(i, 1) = Site Or i = NumSites
            If aRows(i, 1) = Site Then
                RwsReqd = aRows(i, 2)
            Else
                MsgBox "Site not found in table"
                Exit Sub
            End If
            Amt = Cells(r, "P").Value / RwsReqd
            CSS1 = ""
            For j = 0 To x
                CSS1 = CSS1 & "|" & Cells(r, AMatchCols(j)).Value
            Next j
            For k = 1 To RwsReqd
'                r = r - 1
                If r >= FR - 1 Then
                    z = r
                    CSS2 = ""
                    For j = 0 To x
                        CSS2 = CSS2 & "|" & Cells(r - 1, AMatchCols(j)).Value
                    Next j
                Else
                    z = FR
                End If
                If CSS2 = CSS1 Then
                    Cells(r - 1, "Q").Value = Amt
                Else
                    With Rows(z)
                        .Insert
                        Cells(z, "E").Resize(, 11).Value = _
                            Cells(z + 1, "E").Resize(, 11).Value
                        Cells(z, "Q") = Amt
                        r = r + 1
                    End With
                End If
                r = r - 1
            Next k
        End If
        r = r - 1
    Loop While r >= FR
    Application.ScreenUpdating = True
End Sub


I am trying to set up traffic flow network with Excel Macro.
The first macro( in command tab) creates unique ID's for each traffic movement at an intersection with have start-node, mid-node, and end-node. if a particular movement starts at Int 1, pass through 2, and would eventually end up at 3, its node sequence ID would be 1S2M3E. For new trips generated by "A", a user will enter pathway for Auto In. if Auto In starts its trip at Int 1, and passes through 4, 2, and terminates at 6, we can determine all IDs that this pathway would use - 1S4M2E, and and 4S2M6E, Loop stops when nodes are fewer than 3.
1) how can I automatically create nodesequence ID’s for all used movements once the user enters the pathway intersection no.s and 2) how can I populate the table automatically with percent distribution for each pathway?

I am attaching the xls file that somewhat explains what I am trying to do.... the first tab"AM_Auto1_IN" shows the populated tables (in green) that would need to be created with macro.
Thanks!

Hello All,

Here is the issue I am having... I have a tab with all the associated data and would like to populate another tab (which I can print a report from) with that data according to the Week Ending Date (column G). Data includes names, employee ID, specific date worked, standard hours, overtime hours, standard and overtime rates, area worked, sub area worked and the description of work. All of that information is on a single row. I have tried VLOOKUP but it only sees the first matching date. Not sure how to pull the information that I need and put it into the specific printable format. Any assistance would be greatly appreciated.

Thanks,

Bill Sublette

Hi,

I have a problem that concerns data analysis by formulas from an excel table with two columns. The first column is an ID which is non-unique and can be listed any number of times. The second column is a text value related to the ID. It could look like this (but with a lot more data)

1 A
1 A
1 B
2 A
3 A
3 B
3 C
4 A
4 B

What I want to get information on is a count of the IDs which only have corresponding text A; A and B; A, B and C etc. (7 combinations in total). For the above example the analysis would be:

1 ID with text A (ID 2)
2 IDs with text A and B (ID 1 and 4)
1 ID with text A, B and C (ID 3)

This would make a summary of how many IDs have only text A, both A and B etc. I know how to make this analysis through filtering and pivot tables but I'd like to have a formula rather.

I hope I managed to make this clear enough to get some IDs. I haven't been able to find a way to provide this data since the IDs are non-unique and there might be several entries with same ID and text (duplicate entries).

I appreciate any help, thanks!

I have a list of 2700 leads that I need to sort in to an excel table. Who ever created this list did not define the data very well, and I am having major difficulty creating an automation to sort the data. Defiantly over my head.

Column Headers Should Read
Studio Name
Address 1
Address 2
City / State
Phone 1
Phone 2
Web Site
Email

Any Suggestions?

Thanks - jay

Hi everyone,

I have the following problem!

I have one table with some data in it. At the top of each column, there is a Yes or No droplist cell. All the items in the Table underneath the Yes/No cells can be updated from a second workbook via a macro, and so the data in each column can change. The Table in the current workbook can also have its columns sorted via another macro in the current workbook. Hence the column headers in the Table are changing continuously and the data is not static.

I want the Yes/No cell to blank all rows beneath it, let's say for that for Column B. The Yes/No cell is cell B1, and I want to blank all the data in cells B2:B20 if the Yes/No cell is set to No. And after then having sorted the table, I want to be able to click the Yes/No cell and set it to Yes (the Yes/No cell is also sorted and may be in for example D1 now) and have the existing data show up.

As an example, let's say I update the data in the table via the existing macro, then click No on the current data in Cell B1 so B2:B20 go blank, and then I sort it according to a parameter in the rows. The sorter will also move the relevant Yes/No cells. So the same Yes/No cell corresponding to the existing data could, for example, now be in Cell D1 and the data in B2:B20 would be completely different. I would want to click set No in Cell D1 and have the existing data that used to be in B2:B20 show up in D2:D20.

The only way I thought I could do this is if I had two separate tables (one for the Yes/No, and one as a reference) , the reference table is updated via the existing macro and the data is copied to the Yes/No table, and both are sorted when the Yes/No table is sorted. Except the second (reference) table does not have any Yes/No function, so whilst all the columns are sorted, the second reference table still contains all the data in the relevant columns. I want to be able to link clicking No to that data, which may have been sorted, in the reference table.

I expect there would be a duplicate macro for each Target Yes/No cell in each column with the cell references altered between each. If anybody can help that would be wonderful.

I'm also wondering if the method could be adopted if the columns in the first table could, in addition to the above options, be manually arranged (via shift+click). I think the method for the above would break down as the columns in the reference table wouldn't move at all. Is there any way to get it working with this additional requirement?

Many thanks.

Hi--

I am trying to create a pivot table with data filtered by 'unique entry', but when I highlight the data and create the pivot table, it uses all of the rows, even those that are repeated (i.e. not unique).

How can I make a pivot table and only use the unique entries? Any help would be much appreciated. Thanks!

hello,

i have the following problem :

i am using extremely large excel sheets with specific data in it.
i can report approx 10 weeks into one sheet before it is full. then i
need to use a 2nd sheet.

i want to combine those sheets into 1 pivot table, but the layout of
the sheets is exactly the same (and that should stay this way)

when creating a pivot table with multiple ranges i get an error
message that there are similar column names. is there a away to allow
this and to combine those columns ?

for example in sheet 1 there is a lot data from week 1 to 9.
sheets 2 is containing data from week 9 to 19.

so for week 9 there data in both sheets.
i want to create a pivot table that combines this.
so 1 column name with week 9 and from there the calculation from both
sheets.

is that possible ?

thanxxx a million !!!

Most Searched Demo: Organize your data by using an Excel table

Make your data come alive. Use Excel tables to summarize and emphasize related data: Organize, sort, filter, and calculate your data any way you like.

Hi,

I have 2 Excel tables with information about a datamodel. (Excel 2003, by the way)
The sheets have almost the same 1st two columns: data table name and data column name, and then different columns for different information.
Table #1 with 2 extra columns indicating foreign key and comment and table #2 with 2 extra columns indicating data type and data lenght.
Most rows (identified by data table name and data column name) are in both tables; however some rows are only in one table.
I want to reconcile and merge those 2 tables into a single sheet with
- all 8 columns and with one single row when a match can be found between table #1 and table #2 (based on identical data table name and data column name)
- and, in between, the rows from either tables, which haven't found a match in the other table
- all the while keeping the order by (first data table name and second data column name), which both tables already have.

I hope I wasn't too complicated in my explanations ;-)
Can anyone help me? Thx a lot!

I am trying to build a little calculator which suits my personal needs. I want to avoid having to create huge tables of data manually and looking for corresponding values in the table from a given input using lookups. Ultimately I would like to avoid having to create the tables as a whole and only use reference values and let excel derive a formula/function for the rest of the values. This would save time as I would only have to feed function/formula the reference value. I am not trying to plot a graph!

Example 1, Complete set of data:

I have a table with Y-Values and corresponding X-Values. Everything has a linear behaviour except that the point of the 1.0 Mark changes depending on the Y- and X-Values.

So my question is if it is possible for Excel to derive a formula/function from such a set of data which can then be used in a calculation where the input solely consists of the Y- and X-Value and the formula/function will return the correct result? Furthermore I would like to know if I enter a value between say 11 and 12, would this derived formula be able to return the correct value? If not, then it would not be a big deal as I could just round or truncate the input.

Example 2, Limited set of data:

This is the same table as before except that I only have the reference values. Is it possible using those reference values only to derive a formula/function to either populate the table with the data from example 1 or to derive the a formula/function I am trying to get from example 1?

This would basically enable me to change the reference values on the fly and the formula just calculating the rest in the background which would result in much higher flexibility.

Thanks in advance for any pointers/help

-timmeh

Can anyone help me please.

I have a pivot table that I formatted etc and now want to update the source data. I dont want to create a new pivot on the new data but use the existing pivot table with the new data.

Is there a way I can tell the pivot table which data to now look at ?

Many thanks for any help.

Mark

Hi, I have a worksheet that has a large table that I filter by a certain column. Is it possible to populate a table on a second worksheet that is the filtered result of the first worksheet's table?

For instance, if I have a table listing types of cars and their color / make / etc. I sort it to see only Red cars. Is it possible to have my second worksheet populated with a table of red cars, and my third worksheet populated with a table of green cars?


No luck finding an answer? You could always try Google.