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.

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.

- Enter a value and have excel populate a table with that many rows
- Excel Table with Row Headings
- Footers - 4X4 table with some data and the company logo on each sht
- Populate Access table with excel array
- Populating a table with the sum of a formula
- Creating a new table with specific data from two existing tabs
- Auto populate 1 table with data sourced from multiple tabs and criteria by picklist.
- Updating Pivot Table with new data
- Rc Notation Part 2 - Dynamic Pivot Tables With Dynamic Data Source Range
- Using auto-fill to populate a table using data from several tabs
- Transpone Excel tables with macro/VBA
- Search in range and insert match in Excel table with VBA
- Populating a table with unique ID and matching value
- Populating a table with data associated by Week Ending date
- Data analysis of table with duplicate entries through single formula
- How do you create a table with this data...
- Yes/No Cell to delete/copy data in a Table with changing columns
- Creating pivot table with filtered data
- Creating pivot table with similar columnames
- Most Searched Demo: Organize your data by using an Excel table
- Reconciling and merging 2 tables with almost identical 1st two columns?
- Deriving a function/formula from a complete and an incomplete set of Data
- How to refresh a pivot table with new data
- Populating a table with filtered result of another table

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.

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

Thanks in advance!

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

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

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

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.

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

data from several tabs?

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

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

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!

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

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!

Column Headers Should Read

Studio Name

Address 1

Address 2

City / State

Phone 1

Phone 2

Web Site

Any Suggestions?

Thanks - jay

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.

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!

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 !!!

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.

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!

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

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

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?