Windows Support Forum

Solved: Changing Data in a Acess Table column

Q: Solved: Changing Data in a Acess Table column

I am having a problem Changing Data in a Acess Table column I have a access table called quot ITEMS quot with one field called quot IDNo quot and one field called quot ItemNo quot I have records IDNO field is thru and in the ItemNo field with the following same data string I want to in Solved: Changing Acess column Table Data a loop thru these records changing the to for Solved: Changing Data in a Acess Table column example to using an Input Box varible Attached is the small procedure of what I have Problem is it s not changing the string to string but it s looping thru all records Any help appreciated boaterjohn Private Sub Command Click Dim intCount As Integer Dim strNewItemNo As String DoCmd OpenTable quot tItems quot DoCmd GoToControl quot ItemNo quot strNewItemNo InputBox quot ENTER Solved: Changing Data in a Acess Table column THE NEW ITEM NUMBER quot quot NEW ItemNO IS quot MsgBox quot NEW ItemNO IS quot amp strNewItemNo input the no of records for intCount using input box intCount DoCmd GoToRecord acFirst itemno strNewItemNo DoCmd RefreshRecord Do While intCount gt DoCmd GoToRecord acNext itemno strNewItemNo DoCmd RefreshRecord intCount intCount - Loop End Sub nbsp

Relevancy 100%
Preferred Solution: Solved: Changing Data in a Acess Table column

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Relevancy 94.17%

I have a database that i'm creating that has 3 different tables. Its for safety violations at work. One table is the innitial one that has all the pasic information on it. I set it up so that it automatically assigns a number to each complaint filed. The other two are for follow up. One is for how the problem is going to be fixed and who is supposed to do it, And the other one is for verification to make sure that the problem has been fixed. My problem is that i want the two follow up tables to automatically import the complaint number when a new complaint is filed. I tried to set everything up myself, but had no luck. Can someone please help? Thanks.

A:Solved: update the column in one table based on the column of another table
Relevancy 87.29%


I have created new book and i copied the source data and pivot table from the file, but all my pivots are referring my old data source but i want to change into current active sheet source data. I have used the below codes but i dont know how i can use the entire sheet range;

Sub RefreshPivotTables()
Dim ws As Worksheet
Dim PT As PivotTable
Dim SourceName As String
x = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
SourceName = Worksheets("Overview_Data").Columns("a1:BN" & x)

For Each ws In ActiveWorkbook.Worksheets
For Each PT In ws.PivotTables
PT.SourceData = SourceName
Next PT
Next ws

End Sub

i am getting error code 1004 application defined or object defined error.

Please help me on this.
Relevancy 79.55%

I have a very large spreadsheet of data, 365 columns and 290 rows. I would like to convert the data into 1 very long column, with all of the data consecutively listed. Is there a function that allows me to tell the formula to look to the first line of the next row of data, and copy down from there, and then go to the next column, etc? Right now, I'm just referencing the first cell in each column, dragging down to fill the 290 following cells, and then referencing the next column. It's really time consuming because I have 48 of these spreadsheets. Any suggestions would be greatly appreciated!

A:Solved: How to move data in a series of column into one column


if you name the range of 365x290 cells as, for example, 'MyRange', then the following (downwards extendable) formula should work:


Relevancy 75.25%

Hello I ve been struggling with getting a simple InfoPath form to work with a test MS Access database I feel the best way to explain what I am doing is by giving you an example Background of scenario Lets say I am a distributor of gas cylinders I have different gas cylinders that I fill and send to customers When they are done with the gas they return the cylinders and I fill them back up Each cylinder has specific data related to each manufacturer serial specific cylinder ID and material of construction carbon steel stainless steel etc I have this data in my MS Access database in an individual table called quot CYLINDER DATA quot Every time I refill a cylinder I need to record the from another table, (same - table data 2007 one Retrieve data submit to InfoPath data date and measure the pressure and temperature among other things of the gas InfoPath 2007 - Retrieve data from one table, submit data to another table (same data in the cylinder This data along with the specific quot CYLINDER DATA quot at this date of measurement must be added as a new record in a different table called quot CYLINDER MEASUREMENTS quot in the same database What I would like to do So I want to create an InfoPath form for operators to use whenever they need to perform measurements on a newly filled cylinder So I created an InfoPath form that starts with a drop-down list box control that is populated with the different cylinder IDs A user would choose one of the quot cylinder IDs quot from the drop down list and then click quot retrieve data quot This would then populate the fields directly below manufacturer serial material of construction etc from the quot CYLINDER DATA quot table Below this section would be a section to quot fill in quot measurements to the quot CYLINDER MEASUREMENTS quot table It would have blank fields for quot pressure quot quot temperature quot quot date quot and other measurement fields Once filled in the operator would hit quot submit quot and a new record in the quot CYLINDER MEASUREMENTS quot table would be created This new record would included the everything populated on the InfoPath form the cylinder specific data AND the measurements on the given date What I ve tried doing I have my database set up in the two tables outlined above I created an InfoPath form with the quot CYLINDER DATA quot as the Main Connection I am easily able to retrieve cylinder specific data from the CYLINDER DATA table into my form by use of the queryfields and datafields However I cannot seem to get the quot submit quot to go to the quot CYLINDER MEASUREMENTS quot table The form seems to only want to submit to the quot CYLINDER DATA quot table I also tried making the quot CYLINDER MEASUREMENTS quot table the Main Connection so I could submit to it But then it seems that I cannot create a database query to quot retrieve quot data from the quot CYLINDER DATA quot table Where to go from here I am looking to get any insight from you to see how I can configure this appropriately to get what I need I also do not want the user to be able to change the data in the quot CYLINDER DATA quot table so I would likely have those fields quot read-only quot Any information support is appreciated Thank you nbsp
Relevancy 74.39%

I have a spreadsheet where users use a dropdown list to select site one and site 2. I have another table that has a list of sites1 and sites2 and the third column has the distance. How would I create a formula that when the user selects their site in column A and Column B on the input screen that it matches both columns on the table and get the value in C and put that value on the input tab under distance. I am attaching a sample that I hope is not confusing. I appreciate any help.

Relevancy 73.96%

I am reposting retroboy s question since I went to answer it and this forum had closed the thread due to inactivity Hey everyone I am only familiar with the basics in Word help! - in column 2003 - change table Solved: widths a Word I am a bit better Solved: Word 2003 - change column widths in a table - help! with Excel but I have encountered a problem that I am hoping for some help with at work I am handed over a Word document every monday morning that is roughly - pages long that consists of roughly - tables on it All tables have columns with identical headings for each Every Monday I spend a lot of time making these into the exact same format adjusting column widths to the exact same specs I created a macro to make all of my adjustments but it fails when it comes time to adjust the column widths for all the tables I would like to be able to run a macro with the columns widths to finish as follows column column column column column column Please help me get to the bottom of this to save my Mondays Thanks nbsp

A:Solved: Word 2003 - change column widths in a table - help!

From what I’ve seen, Word 2003 doesn’t let you iterate through columns like you would in Word 2007. So, when writing this as a 2003 macro, you have to brute force it. This has worked for me (this assumes that you have already set the table width to the sum of all of these columns):

Dim t As Table
For Each t In ActiveDocument.Tables
t.Cell(1, 1).Select

Selection.Expand Unit:=wdColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(0.73)
Selection.MoveRight Unit:=wdCharacter, Count:=1

Selection.Expand Unit:=wdColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(0.35)
Selection.MoveRight Unit:=wdCharacter, Count:=1

Selection.Expand Unit:=wdColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(0.66)
Selection.MoveRight Unit:=wdCharacter, Count:=1

Selection.Expand Unit:=wdColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(0.51)
Selection.MoveRight Unit:=wdCharacter, Count:=1

Selection.Expand Unit:=wdColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(0.38)
Selection.MoveRight Unit:=wdCharacter, Count:=1

Selection.Expand Unit:=wdColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(7.56)
Selection.MoveRight Unit:=wdCharacter, Count:=1

Next t
Relevancy 67.51%

I have an entire column of data that I need in one cell. When combined I need each piece of data to be surrounded by " " and a , between each one.


results = "151","183","2001"

A:Solved: Excel Column of data place in one cell with "data", "data" results

ASAP's "Merge row data" ("Columns and rows" category) will do 99% of the work for you.

(edit: "entire column" -- do you mean full from top to bottom? )
Relevancy 67.08%

Hi everyone

I'm a newbie here, so hoping someone can help me!!
I have Names and Mobile Number in Column A. I need to find a way of moving Mobile Numbers into Column B - please tell me there is a simple way in which this can be done.....HELP!

Relevancy 66.22%

Okay - still very new to excel formulae so bear with me...

ColA.......Col B
Open..... 23
Closed... 7
Open..... 8
Closed... 19

Okay let us assume in C1 I want a single formulae that wil:
a: count the rows that have 'Open' in column A
b: sum the corresponding values in column b

With this example, my visible value in C1 should be 35. I am hoping to not have to 'duplicate' data in another column/area and the sum that new column (such as copying only the 'B' values that have "Open" in the 'A' column and then running a sum off that new column....

I am sure there is an easy way to do this but I keep getting zeros. I have been trying this:

I have one other formula in D1:
=COUNTIF(AF1:AF5000,"*Open*").. which displays 3....

Thanks in advance


A:Solved: Excel - count and sum separate column data

Relevancy 66.22%

I would like to find duplicates in a column for fields that contain "_" only and place on new sheet in column A. If found, I would like to then get the data in the adjacent field only if it I not blank and put the first instance value of adjacent column in Column B on new sheet, second instance Column C, etc. Probably no more than three instances. I have an example spreadsheet that has sample data and a results tab of how I would like the valid data to look. I am not sure where to start on coding it and which formula would be the right ones to use. I don't necessarily want the exact code but some guidance on the steps to take and formulas to try.

Relevancy 65.79%

I use Word 2000. I create a lot of very long tables where the first column is the row number (1, 2, 3, etc.). Is there a way to automatically populate that column? It's a real pain when I need to delete rows and manually renumber.

Relevancy 65.79%

I am importing an excel sheet into my database into a new table (TRANSACTIONS) using VBA's Transferspreadsheet by clicking a button.
The 2nd row of the excel file are the column names of my table. however, when the data gets imported access assigns the default column names to the TRANSACTIONS table as (f1, f2, f3,f4...)

1. how do i make access read the 2nd row of my excel as column names for the TRANSACTIONS table?

2. I also tried storing the column names into a new table called NAMES - but dont know how to make them apply to the TRANSACTIONS table.

Please help. thanks in advance.

Relevancy 65.79%

I am having trouble getting a form - subform relationship to work the way I need it to I hope I can find some help here I have a form with a subform that needs to deal with three tables and be able to two of them Here is an simplified overview of what I am trying to accomplish Table names are in bold and field names will be encased in asterisks PersonList One Table PersonID AutoNumber - Unique Key PersonName Text CategoryNo Number - Byte BookList One Table BookID AutoNumber - Unique Key BookName Text Category Use Y N Category Use from changing one-to-many table subform Solved: with Problem Y N etc BookCollection Many Table PersonID Unique Multi-Key linked with BookID in this table BookID Unique Multi-Key linked with PersonID in this table BookOwn Y N BookRead Y N Now for a description of the form and subform that is based on these three tables The main form has a text field for entering the PersonName and a combobox for selecting the CategoryNo The subform datasheet view is a list of the books from the BookList table filtered through a select query based on the category chosen for the person The book list for each category is different but there are books in the list that all categories Solved: Problem with changing one-to-many table from subform can use some that only a select set of categories can use and finally some books that only one category can use Using a set focus macro a requery macro and the lost focus event on fields in the main form I Solved: Problem with changing one-to-many table from subform keep the subform populated with the correct list of books for the selected category This is the extent of what I have been able to get to work correctly Now on to the problem child I need to be able to use this setup or one that works the same to selectively add records to the BookCollection table I tried adding the BookOwn and BookRead fields to the query that populates the subform but after that I could only get the books that had been already entered by hand into the BookCollection table as test entries not the full list of between and books each category should have The other attempt I made was to place two checkbox controls directly onto the subform and bind them to the BookOwn and BookRead fields The problem I had with this approach is that I couldn t actually affect see or add data to the BookCollection table At least with the first approach I could see the test data I had already entered into the BookCollection Table I know that I am probably missing something rather simple - Please help if you can nbsp

Relevancy 65.79%

I want to change the width of a cell in a row in a table in Word 2003 without changing the entire column width. At times, when I change the column width, it will only do the cell in that column, without the rest above or below randomly. But it won't do it when I want it to. I have tried using the shift, control and alt keys while clicking on the vertical left margin of the cell but to no avail. Does anyone know how to do this directly without a work around of splitting cells or merging them them or making the lines invisible?
Dr. Joe

A:Solved: Changing cell width in row in table

Read this ---> Resize all or part of a table
Relevancy 65.36%

tried looking for this in many different places but can t find an exact match and i tried to edit some macros but still would not work i need a macro that will allow an entire row from one sheet be copied into a new sheet so basically here is what needs to be done from my active entire sheet Solved: new column matched is to if copy data row sheet look in Column D select all rows in in column D if it has the word quot Confirmed quot in it create a new sheet and paste all those selected rows to this sheet delete this transfered data from sheet in the new sheet i need to add in titles as follows in columns A - O Ticket OrgCode Event Event Status Project Type Project Status Start Date End Date Resource Client Project Name Project Fund Solved: copy entire row to new sheet if column data is matched Project WBS Project Cost Center Amount So column A Row Ticket B Row OrgCode etc I already have a macro which changes the current sheet column names so i need to add the above into this code Code Sub recordd ' ' recordd Macro ' ' Range quot A quot Select ActiveCell FormulaR C quot Ticket quot Range quot B quot Select ActiveCell FormulaR C quot OrgCode quot Range quot C quot Select ActiveCell FormulaR C quot Event Type quot Range quot D quot Select ActiveCell FormulaR C quot Event Status quot Range quot E quot Select ActiveCell FormulaR C quot Project Type quot Range quot F quot Select ActiveCell FormulaR C quot Project Status quot Range quot G quot Select ActiveCell FormulaR C quot Start Date quot Range quot H quot Select ActiveCell FormulaR C quot End Date quot Range quot I quot Select ActiveCell FormulaR C quot Resource quot Range quot J quot Select ActiveCell FormulaR C quot Client Name quot Range quot K quot Select ActiveCell FormulaR C quot Project Name quot Range quot L quot Select ActiveCell FormulaR C quot Project Fund quot Range quot M quot Select ActiveCell FormulaR C quot Project WBS quot Range quot N quot Select ActiveCell FormulaR C quot Project Cost Center quot Range quot O quot Select ActiveCell FormulaR C quot Amount quot Cells Select ActiveWorkbook Worksheets quot Sheet quot Sort SortFields Clear ActiveWorkbook Worksheets quot Sheet quot Sort SortFields Add Key Range quot D D quot SortOn xlSortOnValues Order xlAscending DataOption xlSortNormal With ActiveWorkbook Worksheets quot Sheet quot Sort SetRange Range quot A O quot Header xlYes MatchCase False Orientation xlTopToBottom SortMethod xlPinYin Apply End With ActiveWindow ScrollRow End Sub nbsp

Relevancy 65.36%

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?

A:Solved: Excel macro to select data in a column with a header row

Dim lastRow as long
LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select
Or if you need more columns then Range("A2:H" & lastRow).select
Relevancy 65.36%

Hi, I am new here. I need help to write macro/code which can submit query in the application status feedbox at
and click on SUBMIT. From the results, i want to copy STATUS data to excel sheet.

For example query numbers are 1274032, 1460986, 1522002 in the excel column.

For 1274032 the result page shows STATUS as REGISTERED. I want REGISTERED copied to adjacent cell of 1274032 cell

Please help me.

Relevancy 65.36%

Hello Excel Pros I need help with the table below The totals in columns D E and F are correct sums of the cells above each D -D E -E and F -F respectively and those cells are based on percentages in column B Please note Column F is not based on percentages and should only have in F Initially the issue was that the sum of the rows as seen in Column H dit not equal the percentages from column C based on the total in H but now Table Formula for Totals Row and Column I got that corrected by applying the formula H SUM D G C and then copying H over to H thru H The rows and columns must add up to Formula for Table Row and Column Totals based on the percentages in column B However my issue now is that rows D -G D -G etc do not add up to the row totals H H respectively Can you help A B C D E F G H Table Bucket Percentage Total Bucket Bucket Bucket Bucket Bucket Total nbsp

Relevancy 64.93%

I have different mailing lists that I want to add to a mailing list in my database. There may be duplicate addresses in these lists. I want to only add an Address, FirstName, LastName record once. So how do I combine these lists with my original list without adding a duplicate Address, FirstName, LastName record?

A:importing data from a table to another table without adding duplicates?
Relevancy 64.5%

I have inherited a long document which consists of a table with multiple rows and two columns. The first column consists of numbers (1 - 207) which just serve to number the items in the second column. Problem is that a few of the items (whole rows) have been deleted which now means the numbers running down the first column are no longer consecutive. (Each item has its own row). I thought I could highlight the first column right throughout the document, press delete, then press the automatic number icon on the Formatting toolbar. When I did this only the column for the first page was highlighted.

Is there a way to highlight the first column right throughout the 10 pages of the document and perform the above actions to get it all consecutive again?

Thanks for any help - Julie

A:consecutive numbering in Word 97 table column

It sounds to me like it isn't one continuous table.

One way to check this is to click anywhere in the first cell in the relevant column, then select Table -> Select -> Column on the menu bar.

Scroll down the page and see if the whole column is highlighted. If not, then the table is probably split.

I tried to recreate this with two tables and got it to work by selecting the first column in the first table, then the first column in the second table by holding Ctrl. Clicking autonumber worked for me in this instance....

If the tables aren't split, all i can suggest is trying to highlight the relevant column first by clicking in the first cell and following the steps above (Table -> Select -> Column) and then hit autonumber.
Relevancy 64.07%

Hi there,

I have excel 2007 and I am trying to delete a table I put in. (Insert > Table) The problem is when i do this it deletes the data within the table. I dont want to have to delete the table and put in all the information again. Is there an easy way to do this? I looked at a similar post and it said to press the filter button on the data tab. This does nothing.

Any help or suggestions would be greatly aprreciated.


Relevancy 63.64%

Hey everyone,
I am only familiar with the basics in Word (I am a bit better with Excel) but I have encountered a problem that I am hoping for some help with at work: I am handed over a Word 2003 document every monday morning that is roughly 5-9 pages long that consists of roughly 5-7 tables on it. All 6 tables have 6 columns with identical headings for each. Every Monday, I spend a lot of time making these into the exact same format, adjusting column widths to the exact same specs. I created a macro to make all of my adjustments but it fails when it comes time to adjust the column widths for all the tables. I would like to be able to run a macro with the columns widths to finish as follows:

column 1: .73
column 2: .35
column 3: .66
column 4: .51
column 5: .38
column 6: 7.56

Please help me get to the bottom of this to save my Mondays!
Relevancy 63.64%

I created a SQl table name Skill as:
Skill( SkillCode, Description)
SkillCode Description
POM Project management
IT Internet Technology
DS Design Skills

I tried to convert the text-box to Drop down list box while importing the
datafields into infopath form. But, neither i was able to see SkillCode
values in the drop down list box nor i was able to record the value of
SkillCode selected (When SkillCode was entered manually from the drop down
list box properties)into EmployeeSkill table( with EmployeeID and SkillCode
as its attribute).
I will appreciate any knid of help.
Relevancy 63.64%

Confused of 2007 value table Excel pivot last Vlookup column in Ok I have a list of values on one worksheet called Summary I have a pivot table on another worksheet called Ox Lab Fixed It is based on an Excel Table of raw data Excel 2007 Vlookup value in last column of pivot table called Raw Data The pivot table can expand as new data is added and the pivot tables refreshed New rows might be added and more or less columns could be selected in the filter I need Excel 2007 Vlookup value in last column of pivot table to enter a formula on the Summary sheet that performs a VLOOKUP of the Rate Code values in the list This should look at the pivot table and return the vehicle count for that particular Rate Code from the right-most Grand Total column of the pivot table Having searched for an answer and tried lots of things I m finally admitting defeat The pivot table name is OxfordFixed if that matters Trying to follow online advice I ve selected the table and applied a named range of OXFORD VEHICLES with the following reference OFFSET Ox Lab Fixed A COUNTA Ox Excel 2007 Vlookup value in last column of pivot table Lab Fixed A A COUNTA Ox Lab Fixed The Rate Code field is a row label in column of the pivot table column the row label Town filtered to only display Oxford The problem I have is a bit of a -parter Part is getting the vlookup to work in the first place with the pivot table having a named range I ve tried just doing a basic VLOOKUP VLOOKUP C OXFORD VEHICLES FALSE But this just returns N A even though the value in cell C of the Summary sheet can be seen in the pivot table named range OXFORD VEHICLES in column If I refer to the range in the VLOOKUP without using the named range VLOOKUP C Ox Lab Fixed A F FALSE I still get N A As a test if I set the vlookup as VLOOKUP C Ox Lab Fixed B F FALSE i e if I omit the pivot table s first row label column and refer to the range in the VLOOKUP without using the named range then it works it returns the value OS as expected Fine However if I then try to retrieve the value from the Grand Total column that I actually want by correcting the lookup column VLOOKUP C Ox Lab Fixed B F FALSE I get a REF error So it looks like there s some problem caused by the row label in column of the pivot Town and some problem with the retrieving the Grand Total value The former could probably be solved by changing the named range to omit the Town Perhaps I should be using the GETPIVOTDATA function instead of a VLOOKUP at all though again I can t seem to get that to work Part of the problem is then ensuring that the formula picks up the value from the last right-most column when the number of columns could change depending on what raw data is pasted in or which Responsibilities column label are filtered I think this might be possible using the OFFSET function again but I m lost Hope someone can help Cheers Gram nbsp

A:Excel 2007 Vlookup value in last column of pivot table

Gram123 said:

... Perhaps I should be using the GETPIVOTDATA function instead of a VLOOKUP at all, though again, I can't seem to get that to work ...Click to expand...

Join the club!

Hi Gram.

My gut feeling is MATCH/INDEX combo. But personally it's very hard to visualise the whole thing.

Collaborate via email? If "Yes", PM me.
Relevancy 63.64%


I required vba codings for pivot table but the value should be choose from the data validation i have created.

For example : I have two pivot tables in sheet1 and these two pivot table have a common column filed available so i have created the data validation in cell D6. Now i need to give reference to the cell D6 for both the pivot table on the column labels.

Based on the value selected on the drop down the pivot table need to be changed on the both pivot tables.



Relevancy 63.64%

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
Relevancy 63.21%

Hi guys,
So I've created a pivot table which i want to use as my default template file for any future data sources.
I sorted the fields in the desired column order which i wish to keep.
The problem is, that every time that i choose to hide \ reveal a certain field (checking the "v" sign on the fields list on the right),
that field gets thrown back to the end of the table as the last column.
I wish to be able to play with different fields and hide them, as i don't always need to see all of them, but that they will always appear where they originally were.

I tried creating a custom list, but did not manage to make that happen unfortunately.
Please help!
Thank you
Relevancy 63.21%

Hi everyone,

I have a macro that retrieves some data to excel and populates an excel table (listobject) using vba. The table is large (8000+ rows and 6 columns). There are another table that does some calculations based on the first table. Although the calculations is set to manual, when I send data to the table , I have an impression that the second table is recalculated too, because it takes a lot to add the data (but if I remove the second table it takes just a few seconds compared to 1,5 minutes).
The same is with the table resize.

Can somebody to advice on how to set excel so it calculates everything at the end of the code and not when the data is added to the table. Currently this settings are in place:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Using Excel 2010 and 2007.

Relevancy 63.21%

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.

Relevancy 63.21%

I'm looking to return the source range (will be in another worksheet within workbook) for a pivot table in visual basic. Preferably as string.

I have 1 worksheet with 4 pivots. Each tables data originates on a separate worksheet within the same workbook as the pivots. I know how to do this ad-hoc...set the range equal to whatever sheet based on the name of the table itself, but I would like to be able to apply it to a number of different workbooks.

Any ideas?

Relevancy 63.21%

I ADO Connection Solved: a Need table to make Data using String have an Access database that is currently connected Solved: Need to make a table using ADO Connection String Data to our company s main database through an ODBC connection In a week or so we ll be moving from using the ODBC to ADO I Solved: Need to make a table using ADO Connection String Data have figured out how to connect the two using the following ADO connection string Private Sub Form Open Cancel As Integer Dim conn As ADODB Connection Set conn New ADODB Connection conn ConnectionString quot Provider sqloledb Data Source SERVERNAME Initial Catalog NAME User Id ll Password LLLLLL trusted connection yes quot conn ConnectionTimeout Dim myCommand As ADODB Command Set myCommand New ADODB Command myCommand CommandText quot select from tblName quot Dim rs As ADODB Recordset Where I get stuck is how to connect this to tables and queries in my database Or better yet how to get the above connection string to build a table that I can use using VBA What VBA or SQL would I use to link the tables that I previously used ODBC to connect to my Access database I ve tried using the below but it hasn t worked to far Dim fieldCount As Integer fieldCount rs Fields Count ListView Cols fieldCount ListView AllowUserResizing flexResizeColumns ListView Rows For i To fieldCount - ListView TextMatrix i rs Fields i Name Next rs MoveFirst Count Do While Not rs EOF ListView TextMatrix Count Count For i To fieldCount - ListView TextMatrix Count i rs Fields i Next Count Count rs MoveNext Loop rs Close conn Close I know that the connection works b c I can do a debug Solved: Need to make a table using ADO Connection String Data print and it prints all of the information in the table into the MS Visual Basic quot Immediate Window quot I just can t seem to get the data into a manageable form I can use So any help is greatly appreciated Look forward to your response Best Zhouma nbsp

Relevancy 63.21%

I created an access db amp populated a table using get external data from excel Now I need to import more records from excel to that same table I tried the same process but it insisted on creating a new table Repeat x get more new tables Even though when using get external data there is an option to import into an existing table the option is greyed out I need to get the new excel data into the already-populated table Speaking in human terms not Microsoftese how to I either merge the data from newly-created newly-populated and unwanted table into the existing table or import from excel into the existing table I realize that I could export from access back into excel merge the data there and import into a new table but surely there s a better way Or not since we re talking about one of the Big Flopper s biggest flops Access Of course the best solution would be to import it into an SQL db but that s not an option in this case office politics note to self CHILL nbsp

Relevancy 63.21%

Workstations XP Pro systems Server Win Currently using MSAccess k database for client listing and associated notes etc one-to-many tbl client to tbl notes We recently purchased a software package and I believe I read in the literature that it is built on sybase platform In talking to the company about migrating data one of their techs stated that they Export access tables to csv comma del format bring the new systems table open in foxpro append the data from the csv files He said that foxpro accepts the tables better and keeps the table in a correct format for the commercial program to read use them So I contacted a friend who had Visual FoxPro on his laptop and he let me use the laptop for a short period of time I INTO table Solved: Access foxpro data tables exported a few of the MSaccess tables to csv format and then opened FoxPro BIG Solved: Access table data INTO foxpro tables SURPRISE Never used or even Solved: Access table data INTO foxpro tables seen screen shots of FoxPro so when it opened up I guess I was expecting something quot similar quot to access or whatever but that was not the case Foxpro uses a command window and well Im lost I opened one of the commercial programs tables in foxpro and it took me a few minutes to look at the data currently in their table figured out it was some quot browse quot command but absolutely - totally different than what I was expecting QUESTION How do I append data to existing tables from csv files in FoxPro QUESTION Is there some down and dirty tutorials on FoxPro that I can read through Im not too proud to ask for some help here I pride myself in being able to figure out new different software packages but this has got me stumped Everything I type in google about foxpro takes me to msdn and a bunch of specific VB problems or something Once I get the raw data into the commercial programs tables then I can link them into the clients listing I just need help getting started Thanks in advance Semper Fi JR nbsp

A:Solved: Access table data INTO foxpro tables

is this in the right forum, or should it be moved?
Relevancy 63.21%

Okay I ve got an Access database For this particular task I have a form with comboboxes and a textbox all four unbound The data on the comboboxes are populated RowSource from three separate queries which data Solved: Add 2003: form Access from table to run fine I have a button which when clicked I want to take the information and add it to a table tData My SQL is very rusty and I can t get it working right Here is the SQL statement Code INSERT INTO tData CowType CowLocation CowMovement User Date CowValue SELECT Forms fData CowType AS Expr Forms fData CowLocation Solved: Access 2003: Add data to table from form AS Expr Forms fData CowMovement AS Expr CurrentUser AS Expr Date Time AS Expr Forms fData CowValue AS Expr FROM tData As you can see the form fData should take the controls and add the value of each to the respective field in the table tData It s not working though and I m getting the retarded quot Invalid SQL statement expected DELETE INSERT PROCEDURE SELECT or UPDATE quot Can any Access SQL guru tell me what I m doing wrong Driving me crazy here nbsp

A:Solved: Access 2003: Add data to table from form

Nevermind, I got it. Went away from the Append query I was trying and did it via VBA...
Dim rsData As ADODB.Recordset
'Create data set for entry into table
Set rsData = New ADODB.Recordset
rsData.Open "SELECT * FROM [tData];", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rsData.Supports(adAddNew) Then
With rsData
.Fields("CowType") = Me.CowType.Value
.Fields("CowLocation") = Me.CowLocation.Value
.Fields("CowMovement") = Me.CowMovement.Value
.Fields("User") = CurrentUser()
.Fields("Date") = Date + Time()
.Fields("CowValue") = Me.CowValue.Value
End With
End If
Set rsData = Nothing
Relevancy 62.35%

I have a weird problem I need help with. I am doing some work and have been given data in a word document like so:


its not in a table and there is over 400 lines of it! I need to put this data into excel so I can sort through it. if it was in a table I could do paste special to place each bit of data into a different row.

Can anyone tell me if there is a way to either:
place the data into a table
copy the data correctly into excel


Relevancy 62.35%

Hi all Not a huge tech head so tread lightly with me I ve recently designed and have been using a database for tracking of research participants I have my backend and frontend separate as per my husband data from no reading Access longer Solved: table Form s instructions I have buttons on my switchboard One for enter new participant That form can enter new data and doesn t show existing Solved: Access Form no longer reading data from table data My other button form is quot edit existing participant quot - i had to make some changes to it yesterday - add new fields move stuff around etc Solved: Access Form no longer reading data from table etc was having probs as computer kept shutting down etc However all going well but end of day open it up and the edit participant form no longer shows any existing participants data It s all still there in the backend but wont show up on the form My data entry Solved: Access Form no longer reading data from table is set to No - so that s not the problem The data record source is correct and all tables have been linked correctly between back and front ends Help please - this is work i ve been doing casually for my old organisation and they desperately need this and I ve mucked it up Aaaagh Thank you Carla nbsp

Relevancy 62.35%


I am having trouble making a spreadsheet to record points and prizes awarded for playing different types of games.

Each game awards a set amount of points for playing, plus a prize depending on the finishing position of the player.
I think I need to use Vlookup but I'm confused as to how to set it up.

I have attached an example spreadsheet to demonstrate.

Any help or Advice greatly appreciated.

A:Solved: Excel - Using Lookup table to record game data

vlookup will do the first part and then maybe indexmatch to do the horizontal look up
C6 = =VLOOKUP([GAME CODE],Table16[#All],2,FALSE)
D6 = =VLOOKUP([GAME CODE],Table16[#All],3,FALSE)
F6 = =VLOOKUP([GAME CODE], Table16[#All], MATCH([Finish Position], Table16[#Headers], 0), FALSE)

check it does work correctly
Relevancy 62.35%

Hi everyone,

Let me just start by saying this forum is awesome. Nothing like getting a new perspective to solving a problem!

Here's the scenario:
I have 3 sheets: Input Sheet, Output Sheet, Translation Table

Both the Input Sheet and Output Sheet has columns of data with a header row in row 1. I need to move entire columns of data from the Input Sheet to the Output Sheet based on the header row.

This is where the Translation Table comes into play. I need to move the columns of data based on what is defined in the Translation Table.

For example, all data in column "Column A" of the Input Sheet should be moved to column "Albert Column" of the Output Sheet.

A sample file is attached to better illustrate.

Thanks in advance for the help!


Relevancy 62.35%

I have my Table Ac2007 Saved Form Main Not Solved: Data in form set up so that once the user Solved: Ac2007 Main Form Data Not Saved in Table enters data in the last field of the main form focus Solved: Ac2007 Main Form Data Not Saved in Table moves to the first field of the subform When the user tabs from the last field of the subform focus is set on the first field of the subform instead of moving back to the first record of the Main form This is because data in the fields in the Main form will be the same for multiple records in the subform and this speeds data entry I solved several problems thanks to the the help of quot OBP quot in this Solved: Ac2007 Main Form Data Not Saved in Table forum but discovered that I ve got more The data in my main form is only saving in the underlying table for the st record that is entered Once the user tabs from the last field of the subform to the first field a new record in the subform those fields are saved in the table but not the corresponding fields in the Main form Very confusing but attached visual of my form and table should help I have set the fields in the Main form to copy to the next record but they are not saving to the table Thank you for your help nbsp

Relevancy 62.35%

Hello again,

What I have is a option group on a form. Now, I want to have it to where each individual option pulls from a table, so that the option is checked if the value in the table is true.

I'm not sure how to set this up however. I am assuming that I would need to have a table field set up for each individual option in the option group. Correct?

Now, what type of fields should these be, so that I can link the option group items to those individual fields? And how would I set it up to where on the form, it would show each ones status, as it is in the table.

A:Solved: Group Option Buttons, linking them to table data

Hey, I figured out how to do it. I had to change the ActiveStatus into a number, and set up my checkboxes in the radio group's numbers to correspond.
Relevancy 62.35%

Hello -

I have a data table that is recording inspection dates and inspection purposes for specific records. There are a total of six occurences (i.e. Inspetion Purpose (1), Inspection Date (1), Inspection Purpose (2), Inspection Date (2)).I would like to use a form to input data into the fields. Is it possible to build an expression so that the data input into the form inserts the data into the table with the first available null value for that record.

In other words, if there is a value present in Inspection Purpose (1) & Inspection Date (1) then the data from the form is inserted into the fields for Inspection Purpose (2) & Inspection Date (2).

I would greatly appreciate any help!

Relevancy 61.49%

It s difficult to describe my situation in Excel: based in list table Solved: a data simple MS on create words so I ll just show you what I m trying to do I have a table that looks like Solved: MS Excel: create list based on data in a simple table this A B C Name Hours OT hours Jane Jack Mike Dot Erin Carmen and I want to create a list that will have rows if there are OT hours and if there are not A B Jane Jack Jack Mike Dot Dot Erin Erin Carmen I can t use macros or VBA It has to be formula driven I can use a helper column or two The starting table will change depending on who charged hours where I ve been trying to use IF and OFFSET and ROWS to do it and it works until there s consecutive rows that have OT hours Just to clarify I just need to know how to get column A listed properly elsewhere in the spreadsheet - I can derive Column B using vlookup Thanks so much nbsp

A:Solved: MS Excel: create list based on data in a simple table

2 Formulas - 1 for finding the name, the other for finding the appropriate value.
Note - must start on row 3 or greater.

Names (mine start in cell G3): =IF(G2<>"",IF(G1=G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1),IF(VLOOKUP(G2,$A$2:$C$7,3,FALSE)>0,G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1))),$A$2)

Hours (mine start in cell H3): =IF(G3=G2,VLOOKUP(G3,$A$2:$C$7,3,FALSE),VLOOKUP(G3,$A$2:$C$7,2,FALSE))
Relevancy 61.49%

I created a new database that contains several different tables with relationships auto number fields and all the fun stuff that goes with those things However my workplace had been inputting data into an extremely large table for years This large table contains all the data that I need to be in my new database I was wondering if there is a way to take the contents of the one large table and append it to the multiple tables that I created in the new database To further complicate matters I changed some of the formatting and or names of fields Any thoughts or suggestions for the most effective and efficient method to complete one smaller large into table from Solved: in several Access tables data Appending this process would be greatly appreciated Just a note I have this week and next week to complete the design of the database and get all these people input into the database a daunting task I know So manually adding the data is not really a feasible option nbsp

A:Solved: Appending data from one large table into several smaller tables in Access
Relevancy 61.49%

I have an excel spread sheet that pulls data from multiple seperate sheet to then consolidate them into a single table that then uses a pivot table to pull together a class list of everyone starting a set classs data Table Pivot Excel 2007/2010 Solved: error on the same day and venue with their Solved: Excel 2007/2010 Pivot Table data error contact details on including a phone number For person on Solved: Excel 2007/2010 Pivot Table data error the list the pivot table has added an additional to the end of the phone number I have checked the original spreadsheet the consolidated sheet and neither of thiose has the extra digit in it only shows on the pivot table The pivot table is set up with tabular layout so you get line per person who meets the filters set to class start date and venue and i have checked that the column on the pivot table is formated the same all the way down Does anyone have an idea how the extra digit arrived and how i get rid of it I have over trypwed the original data on the colsolidated list with some thing differnt and refreshed and then put the content back to the refernce cell and the extra re appears - all very confusing nbsp

A:Solved: Excel 2007/2010 Pivot Table data error

Very hard to trouble shoot this on written notes. To find this... I would try moving/adding the person to another class. I would also try pivoting on class members and see how this entry appears. If still an issue I would make this thing with only the offending records and associated records. Excel usually doesn't make this stuff up.

In reality I would send this off and call it done!
Relevancy 61.49%

Hi, i would like to be able to copy the values under the peak list in excel as tab separated or a table from the .rtf file attached?

A:Solved: Formatting data in an .rtf file into a table like in order to export to excel

Like attached ????
Using Word, I replaced all the multiple spaces with one space.
... Replace "Space Space" with "Space" several times ....
then replaced "Space" with a Tab and pasted to Excel
Relevancy 61.49%

Now that I have the rate information pulling from my other table, the Rate Total won't calculate unless I overtype the rate field with a new entry. What I would like to do is just press enter to confirm the field and move past it and have the total automatically recalculate.

I think that this would be an event procedure on Enter but I don't know what VBA code to use to make it happen?

Can you please tell me if this is correct and what code will make this recalculate?

A:Solved: Access 2007, Recalculate field after data is pulled from table. OBP??
Relevancy 61.49%

I have two access tables I would like to merge data into one table. How to do?

I tried blank database importing one by one but result is I get two tables not one.

A:Importing data from one table into another table

You are going to have to write an insert query. If you view it in SQL mode it would look something like:

FROM MyTable2;
Relevancy 61.06%


I have an access database, the database function fine apart from one problem, when I try and open the database on a PC other than the once which it was created on, the font changes size. Also the background behind the font changes to white.


Relevancy 60.63%

Hi Everyone,

I have a date column in my spreadsheet and there are several actions that I would like to apply to it -

1). Auto populate the date column with the current date only when the column to the left has data entered into it (please see below)

2). Make the date column read only so that it cannot be modified by other parties.

Any help will be greatly appreciated.

Kind Regards,


A:Solved: Automatically enter date into a column and make that column read only
Relevancy 60.63%

I have a list of 39525 emails in column A and a list of 19909 emails in column B. If an email exists in column B that also is in column A I want it removed from column B. PLEASE HELP! THANK YOU!!!!!!

A:Solved: Excel 2010 - Remove duplicates from column B that exist in column A
Relevancy 60.63%

Basically I have a situation where users are creating data tables in Microsoft Word using the creating table functionality, and are then resquesting that the data will be imported into a single Microsoft Database field.

Please could someone show me the best way of doing this!!!!!!

Cheers !!!

A:Importing Table Data Into Multiple Access Field Data?

Do you mean they're creating, say, a 3-row by 3-column table and then they want to import everything in that one table into a single field? a single record?

Out of curiosity, what happens if you copy and paste, perhaps with an intermediate step through Excel?

Also, is there a reason why your users cannot work directly in Access?

More details, please.
Relevancy 60.63%

hello all,

in excel 2003, i need to add a whole column of numbers but i need to exclude any rows, and so it's corresponding cell in the column i'm adding, which may contain a particular string of text.

for example,

say there are 3 columns,

"date", "description", "amount"

i want a total for the amount column, "=sum(c:c)" but i don't want to include any cells which have a DESCRIPTION of "gratuity, thank you".

i hope this makes sense and any help is much appreciated.
many thanks.


A:Solved: ignore rows with certain text in one column when adding another whole column

Hi there, welcome to the board!

I would do one of two things.

1) Take a look at pivot tables
2) Take a look at SUMIF()

Both will get you where you want to go, although the first option is more powerful and robust, it has a much higher learning curve, whereas the second one is more succinct and easy yet less flexible.

Edit: This will get you started with the second item...
=SUMIF(B2:B10,"<>"&"gratuity, thank you",C2:C10)

Relevancy 59.77%

Windows - Excel I am trying to write a macro with that will start at the bottom an unknown line number and loop up through the rows then stop at row It will need to loop from column C thru F to see if the value is a If all columns then delete the row and go up one row and Column Solved: loop with bottom looping to to top column start checking C thru F again If at any time there is a number value in one of Solved: Column to column loop with bottom to top looping those columns it needs to move up row and start checking again until all rows except row have been checked I know I have to start from the bottom and move up to delete rows without skipping any I ve been looking and looking but I can seem to find the right code for this I know I m missing something I could use any help I can get Thanks This is what I ve written and the first Lastrow Range quot A quot amp Rows Count End xlUp Row - is getting runtime error Method Range of object Global failed Sub Delete valueRows Open LTC Individual- Workbooks Open FileName quot C Users Diedre N Collins Desktop LOB CY Macro Worksheets LTC LTC Individual- xlsx quot WorkSheets quot LTC Individual-WIP quot Select Dim i Lastrow Lastrow Range quot A quot amp Rows Count End xlUp Row - For i Lastrow To Step - If Cells i quot C quot Value Then Cells i quot C quot Offset Select Else Lastrow Range quot A quot amp Rows Count End xlUp Row - If Cells i quot D quot Value Then Cells i quot D quot Application Goto quot c quot Else Lastrow Range quot A quot amp Rows Count End xlUp Row - If Cells i quot E quot Value Then Application Goto quot c quot Else Lastrow Range quot A quot amp Rows Count End xlUp Row - If Cells i quot F quot Value Then Selection EntireRow Delete Else Lastrow Range quot A quot amp Rows Count End xlUp Row - End If End If End If End If Next End Sub nbsp

Relevancy 59.77%

Hi My question relates to a list I have created that shows our team who our internal stakeholders are We select thier name from SharePoint in Information column - Solved: Replicate Column another a person group column and then add in using Lookup columns which division and department they are from So I don t have to worry about updating the Solved: SharePoint - Replicate Column Information in another column look up lists whenever a team changes it s name or if a person leaves to go to another team I thought I would add more person group columns in place of the lookup columns So now a user has to enter the stakeholders name times in the person group columns and they are set to display the Name Person Column Division Person and Department Person Now the problem I m looking for a way in SharePoint Designer SPD so when the user is on the new item form they only need to enter the stakeholders name Person Column once Then using SPD I would like to adjust the display list so the Stakeholders information is showing in the three Person columns The news columns show the stakeholders division and department The other columns will have automatically copied the source date from the original name column and are now displaying the same source data as division and department Is this possible I appreciate any help you can offer nbsp

A:Solved: SharePoint - Replicate Column Information in another column

Solved in another forum. Used a workflow.
Relevancy 58.91%

SOMEBODY PLEEZE tell me there is a way to change the column headings in Win Explorer whenever the folder you're looking in contains audio files the headings don't follow the normal headings as when you have "normal" data. Instead they're headed by useless headings that really don't offer ANY help at all. What's worse is you have to change EVERY FREAKIN' folder that has such info. It is THE MOST FRUSTRATING when I want to sort files, but before I do I have to change the headings. My ultimate question: Is there ANY way I can make that change ONCE and forget it?


A:Changing Column Headings in Win Explorer

The folders under your user profile act as templates for all folders of that type

Navigate to your 'My' folders (Documents, Music, Pictures, Videos) and make the columns, sort, grouping however you like them. All folders of that type should now display your preference.
Relevancy 58.91%

Hi everyone,
I'm looking to automate deleting duplicates column by column within each row. For example, in the attached example, on row 590, I'd want to delete duplicates of '1225 Whthrs Mrcvl Rd Ste 206,Hamilton,NJ,08619'. In addition, I'd want this automation to process the entire worksheet.
Considering that there is over 8,000 rows, I'm praying that this is possible.
Thank you!

A:Solved: delete duplicates within a row; column by column

Here is a possible solution but without a clearer view of your data it is strongly advised that you try it in a copy of your file.


Sub DelDup()
Dim lRow As Integer
Dim lCol As Integer
Dim i As Integer
Dim c As Integer
lRow = Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row used in column A
For i = 1 To lRow 'loop through each row
lCol = Cells(i, Columns.Count).End(xlToLeft).Column 'Find the last used column in the row
For c = lCol To 2 Step -1 'loop backwards from the nth column to the 2nd column in the row
If Cells(i, c) = Cells(i, c - 1) Then 'compare column c with the previous column
Cells(i, c).ClearContents 'if they are equal then clear column c
End If
Next c 'go to the next column(which is the previous column)
Next i 'go to the next row
End Sub

Let me know if you need help inserting the macro into your workbook.
Relevancy 58.91%


I have a query but I'm not sure if it's even possible to do.

If I enter a name into cell J1 I want to check for this name in column H then for each row that is found on I would like to sum column B and output the result to cell J2. E.g. If I enter Paul into J1 I would like to search for this name in column H. For every row this is found on I would like the B cell for that row to be included in a sum of which the result is output to cell J2.

Is this possible and if so does anybody know how?


A:Solved: Excel - Search for value in one column, sum a different column

yes, SUMIF() should do that

i'll edit - and add an example

enter into J2


see attached example file
Relevancy 58.48%

on our database we have a field for entering someones email, however it is set it up so it will only allow 20 characters, how do I expand this field up to 100?

A:acess 2007: enlarging data cells

Go to the Table and open it in design View.
Click in the email field and in the lower section change the field size from 20 to 100.
Relevancy 58.48%

Hi All,

I want to import some excel data into a access DB.

When I click on import, there is no xls file type so I can't see my file.

This is using office 2002 on a Windows 2000 laptop.

Is there anyway of making access be able to import xls?

Many thanks,


A:Importing Excel data into Acess problem

should see the file, I assume you are changing the file type field to look for .xls
Relevancy 58.05%

Can some registry expert advise the location of the value determining the default column width of the File name in the right pane of explorer.exe with XP SP-2? I would like this value to be about 300 pixels when opening an explorer window. (One can set a value with the RMB on the title bar, but it goes back to the default whenever you close the program.) Thanks


A:Changing Default Filename Column Width

I opened up Explorer to the Root of my C: drive.
Right Clicked on the Title bar.
Selected More.
Made sure Name was highlighted.
Entered 300 into Pixels.
Then went to Tools > Folder Options > Views
and clicked Apply to all Folders.

I even first did it on a Single Folder and it held the Pixel width for that folder after exiting and reopening Explorer.
Relevancy 58.05%

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.

Relevancy 57.62%


I have problem when exporting a data report to html, the alignment of the columns changing in the html file. Please give me a solution to solve this problem...

Thanking you...

A:Datareport Column Alignment changing when export to HTML

i think there will be always this problem associated with exporting data report. Better u can use labels instead textbox in datareport.
Relevancy 57.62%

I am puzzled I have a column CalloutName in one of the tables in an SQL Server database That column CalloutName has either a string or a NULL as in lt NULL gt A If there is no gap space between the entry lt NULL gt and the left boundary of a column the entry is treated as a NULL B If there is a gap space between an in database for Problem "<NULL>" column. Server testing table SQL the entry lt NULL gt and the left boundary of a column the entry is treated as not NULL A and I can use the following to test whether it is really a NULL If IsNull CalloutName Problem testing for "<NULL>" in an SQL Server database table column. Or CalloutName quot quot Then callOutState quot quot path for A Else callOutState CalloutName path for B End If So I tried the following code to see whether the entry in B can be picked up Problem testing for "<NULL>" in an SQL Server database table column. as a NULL Dim iPosNULL As Integer iPosNULL InStr CalloutName quot NULL gt quot If IsNull CalloutName Or CalloutName quot quot Or iPosNULL lt gt Then callOutState quot quot Else callOutState CalloutName End If I got an error and got an error Inavalid use of NULL So I am at a loss how do I test the Nullity of such an entry nbsp

A:Problem testing for "<NULL>" in an SQL Server database table column.

Use the If CalloutName = "" I do not think IsNull will work in all datatypes. You might also want to put a Trim in front of your column name to trim off any white space that is either inadvertently put in there or is there by default.
Relevancy 57.19%

Afternoon. I would like to build a query in Access 2010 to move some data from one field to another.

I only want to move some data though.
My fields are named mailadd1 and mailadd2
The data that needs to be moved is in mailadd1.

These are addresses. The first field should be the street address, the second should contain the city, state, and zip.

This is correct on a lot of the rows, but on about half of the 109K records, the data that should be in mailadd2 is in mailadd1 and mailadd2 is blank.

So I need to move the data from mail1 to mail2 IF mail2 is empty. In the following example the first row and the last row's data need to be moved, the middle two rows are fine.

Again, mailadd2 is always blank if the data needs to be moved, if that helps.

Thanks in advance for any help you can offer.
Relevancy 57.19%

There are several ways to change columns widths in detail view and hundreds of posts about making all folders keep that column width IF you make all folders open in detail view. Does anyone know of a way to save a default column width in detail view WITHOUT setting all folders to open in detail view?

A:Set default column width without changing all folders to detail view

I'm not sure what you are asking.
Surely column width only applies in Details mode.

You can:
Set all of the columns and their widths in Details mode
Change to View to something else (e.g. Large Icons)
Set your Folder Template

The Folder Template will remember the:
Chosen Columns and their widths
This means you can always "Sort by" or "Group by" the chosen columns without having to go into Details view.

I only use Details and Large Icons.
I'm not sure how the Tiles, List and Content views behave.
Relevancy 56.76%

I have two ranges I want to display of a column of data in a graph I want to display rows - and as a second option rows - Same graph same column of data just different portions of the data I also have managed to set up a cell K in the sheet that displays the value K K OR K K I have cell logic in place that if I set another cell to I get K containing K K and if range in Solved: of data changing Referencing Graphic I set that same cell to I get K containing K K Cell K can be referenced within an average or sum calculation and it Solved: Referencing changing range of data in Graphic will do the average or sum properly over the different ranges That much I Solved: Referencing changing range of data in Graphic have accomplished HOW DO I REFERENCE CELL K FROM A GRAPHIC IN THE SELECT DATA SOURCE SO THAT I CAN GET A DYNAMICALLY CHANGING GRAPH LINE FOR THE RANGE I have tried playing around with range names and indirect references but cannot seem to get it to work There has to be a way to set up a Solved: Referencing changing range of data in Graphic graphic with several displayed graphic lines in it and dynamically change the ranges of those lines all to the same different range so that the graph displays different portions of the data Right now I have multiple graphs I copy the graph and change the ranges manually I want to avoid this work in the future Any ideas I suspect its with name ranges For purposes of discussion the sheet is called COMMON and the graphic is call GRAPHIC Thanks Don nbsp

Relevancy 56.76%

Hi - i am trying to write a macro to move data from some rows to columns, but its not a straightforward transpose. Attached is the example

and if there are any blanks not to transpose those.


A:Macro column data into rows

Select your range of data to evaluate (in your attached example you would select C3:N12) and then run the macro below. The macro will create a new sheet with the transposed data.
Sub Transpose()

oldSheet = ActiveSheet.Name

Worksheets.Add().Name = "NewSheet"


For Each vcell In Selection.Cells

If vcell.Value <> "" Then

vRow = Sheets("NewSheet").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Sheets("NewSheet").Range("A" & vRow).Value = Range("A" & vcell.Row).Value
Sheets("NewSheet").Range("B" & vRow).Value = Range("B" & vcell.Row).Value
Sheets("NewSheet").Range("C" & vRow).Value = Cells(2, vcell.Column).Value
Sheets("NewSheet").Range("D" & vRow).Value = vcell

End If

Next vcell

End Sub
Relevancy 56.76%


I need to compute the weighted average of every other column that has data. (sample file attached)

What formula can I use in Excel?

Please help!

A:Weighted Average of Every Other Column With Data

Please explain more.
the attached data is uniform and can be averaged by the normal Average formula.
Relevancy 56.76%

This may have been asked/answered Previously but I didnt see an answer, I know its possible to Hide/unide column's But I need to do it based on if there is a null Value. IE there are 9 columns only 1 has manditory Value, Name. In my query that pulls the data (a search Box) it pull records that dont have data in the Location field. so in my Subform frmSearchdata I want the location column hidden. Ive tried several ways of coding it in VBA and I dont get errors but it doesnt hide the columns either. Any tips?

A:Hiding a Column In a data Sheet

I see this is not your first post and of course we can all make assumptions but you know what the assume stands for.

it makes an a s s out of u and me

Leaving that for what it is, I could assume you're talking about Excel but ... further more no version of the (unknown) application mentioned which is also quite relevant due to added functionality

Maybe Excel but if could also be Access . . .
Relevancy 56.76%

g'day everyone!

this forum has been awesome in helping me do my project. hence im back, seeking answer.

im currently doing in MS Access, a Report about profit and loss. this report obtain its information by Query, matching the specified information gathered from a Table.

the following is a screenshot from the Report
as you can see, the 2 informations i want to use are given in the query (named: items bought & items sold), my question is, how to reference those 2 different data, but within the same column so that i can make an iif expression for the profit and loss.

thanks for looking!



Relevancy 55.9%

I have an Excel workbook with k rows Each set of rows contains a table name with the fields in the table The number of fields is not the same for each table column to of from row Move value based cell? on data For example COLUMN A COLUMNB AV ACTN RSLT VWACTN RSLT CDAV ACTN RSLT VWDESCRAV ACTN RSLT VWDESCRSHORTAV ACTN RSP VWEMPLIDAV ACTN RSP VWEXT ORG IDAV ACTN RSP VWINSTITUTIONAV Move data from column to row based on value of cell? ACTN RSP VWNAMEAV ACTN RSP VWRSPL ID AMT X SAL TYPEWCS PLAN DESCR AMT X SAL TYPEWCS TOT AMOUNT I need to convert the field names so that they are in a row like this AMT X SAL TYPEWCS PLAN DESCR WCS TOT AMOUNT Is there any way for me to do this programatically I m using the transpose feature but it is time consuming and I have k rows So basically I want something like if the field in column A row matches row move that field to the right of row Move data from column to row based on value of cell? and so on until the table name changes Or maybe i should dump the spreadsheet in access and try there Thanks Amy nbsp

Relevancy 55.9%

Hi all,

I am using the below formula (that I found on this forum) to calculate the age based on a DOB. I now want to create a formula for the next column to have either ‘Yes’ or ‘No’ if the age is above a certain value.

If this is possible, is it then possible if I have two ages in separate columns to generate a ‘Yes’ if either of the age’s are above the specified Value?

Formula for Age Calculation;
=DATEDIF($C24,NOW(),"y") & "y,"&DATEDIF($C24,NOW(),"ym") & "m,"& DATEDIF($C24,NOW(),"md") & "d"

Any help would be greatly appreciated,

Many thanks.

A:Data in columns formula to generate a new column

try this

=IF (Cell you do the calculation in > age you want to test , "Yes" , "No" )
if you want to test two columns , then

=IF ( OR(column1 > age, column2 > age ), "Yes", "No")

can you load a dummy spreadsheet and we can check the above works OK
Relevancy 55.9%

I have an excel sheet with 3 columns. What I want to do is, I need to be able to find value in column 3, that corresponds to a value in column 2. The value in column2 should match the value in col 1.


Input Data
Col1 Col2 Col3

The result should be
Col1 Col2 Col3
Please help me.

Relevancy 55.9%

I have a large excel file (3k= rows) of name/addr info. I want to transpose the date so information appears in its own column for sorting, etc. The problem is the data is not consistant. (sometimes there is two address enteries, sometimes three. In another record there may be two phone numbers. So each company may have six or seven rows of info. The only consistancy is that the company name is in bold. I've tried to attach a sample file so you can see what my task is. I'm not real good with Excel, so treat me like a dumy; it's OK.
I really appreciate the help.


A:Transposing column data into multiple columns

Bill, that was an awkward one, but I think this does what you want.
Just click on the "CommandButton1" and watch.
Relevancy 55.9%

I have two spreadsheets:

One has rows with the invoice numbers and date and sales number etc for only one state

the other has rows with invoice numbers and dates and sales data etc for ALL state.

I would to match the info with first spreadsheet with the data of the second spreadsheet
using the invoice numbers, that are the same in both, as a key match .

How would I go about this?

A simple Example to illustrate:

Sheet 1 ------------------ Sheet 2
A --------- B ---------------- A ------ B
X --------- 500 ---------------- Y ------- 540
Y -------- 1000 ---------------- Z ------- 100
Z -------- 200 ----------------- X ------- 800

And I need the result as this :
A -------- B --------.... C
X -------- 500 -------- 800
Y -------- 1000 -------- 540

Relevancy 55.9%

Hey Guys,

I scanned in some phone numbers/names using OCR and need some help in Excel. Here is example I made real quick of what the list looks like:

Column A is fine. However, do you see Column B? It has the grade and then the phone number.

Here's what I mean in column B:
Grade [one space] phone number

So, it looks like this in the excel spreadsheet:
10 555-5555

Here's what I need to do. I need to find a way to automate the process of taking out the phone numbers from column B and moving them all to a new column (C). There are four grade numbers (9, 10, 11, and 12), so I can run the process on each grade to separate them.

How can I do this? Thanks.

A:Moving partial data from one column to another in Excel

Put the formula:


in C2. Adjust the 8 for the phone number if it includes area code. Then drag that formula down the number of rows you'd like.

- Castleheart
Relevancy 55.9%

I m trying to create a piece of macro code that will pick up all populated cells in a column E -E and copy both the value in that cell and the value on the same row in column A I d like the info from column A to be pasted into column A row data non-blanks column in the and from a copy Locate certain in an entirely different workbook and the corresponding data from column E from the original worksheet to go Locate non-blanks in a column and copy certain data from the row into column B of the new worksheet Data from the same row in the first spreadsheet needs to remain on the same row in the second There is also information beyond row E which I do not want Here is what I ve cobbled together so far but it currently doesn t pick up any data which is a fairly big flaw Sub MoveEM Dim wb As Workbook Dim wb As Workbook Dim rng As Range Dim rng As Range Workbooks Open Filename quot Z DPC MI Folder Copy of RPHD - xls quot Set wb Excel ActiveWorkbook Workbooks Open Filename quot Z DPC MI Folder Checking Workbook xls quot Set wb Excel ActiveWorkbook On Error Resume Next Set rng quot wb Range E - E SpecialCells xlConstants quot On Error GoTo If rng Is Nothing Then Exit Sub Application ScreenUpdating False Set rng wb b rng Copy rng PasteSpecial xlPasteValues copy column I to Output C rng Offset Copy rng Offset PasteSpecial xlPasteValues copy column N to Output d rng Offset Copy rng Offset PasteSpecial xlPasteValues Application ScreenUpdating True End Sub The offset lines of code are lifted from another macro and I ve not yet attempted to tailor them to for my needs until I m in a position to test them but you get the idea The workbooks open fine and it does not seem to fail at any particular point but it s not doing what I want either Can anybody confirm if I m on the correct path with this or where I m going wrong Cheers Gary nbsp

A:Locate non-blanks in a column and copy certain data from the row


You have a couple of problems here:

1) The reason it doesn't seem to fail at a particular point is because you have employed "On Error Resume Next," which is a poor practice and should be avoided whenever possible. If that were not there, you would receive an error when the code stopped working. My recommendation would be to remove this line and implement a proper error trap.

2) This line:


Set rng1 = "wb1.Range(E10 - E84).SpecialCells(xlConstants)"

should be:


Set rng1 = wb1.Range(E10:E84).SpecialCells(xlConstants)

When you put quotes around something, it makes it a string and you are trying to assign a range object, not a string. Also, the colon should be used in place of where you have a hyphen. Hopefully, this will help!
Relevancy 55.9%

I have a Make Table query that when I run it and view the table in the query it shows only the data I want. But when I go and open the table that theis query made, there are entries that should not be there.

It is putting in rows with blank fields, I am not sure how to stop it. I have tired to put something in the query for that field but I get back the error "Invalid Use of Null"

If I could put in the criteria that worked I would be all set.

Any ideas?

A:Solved: Access (Make Table) Query Results Returned not Same as Table
Relevancy 55.9%

A previous thread indicated problems encountered when trying to update a Table of Contents and Table of Figures, etc. using Word 2007. I couldn't find any solutions to that message. I am having the same problem. I tell it to update the Table of Contents or Table of Figures, and it does that correctly, but as soon as I click "print" the page numbers all change to 2! Any ideas?

A:Solved: Word 2007 - Page Number Issues Updating Table of Contents and Table of Figure
Relevancy 55.47%

Problem summary: I want to make a table (- 'NEW TABLE' -called 'By Country:% of People Whose Total Training Hours are in 5 different bands') using the data in a pivot table. I want my 'NEW TABLE' to analyse the data by country. How do I refer to a pivot table cell in my NEW TABLE'S formula, when the pivot table data keeps shifting cells depending on the country I filter for?

Problem detail:
My pivot table has each person's training hours for multiple courses totalled, & it allows me to filter for different countries in turn.

Dreamboat has already kindly told me the formulas for my 'NEW TABLE' to get the % no. of people whose total training hours are between 0 - 8 hours, 9-16 hours, 17-24 hours, 25-32 hours, 33-40 hours.

Sorry this sounds a bit involved.

Thanks for consideration of my problem!


A:Use pivot table data in formula outside pivot table

Jen: Didn't I teach you not to use pivot tables yet?

I suck at them.
Relevancy 55.47%

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you

Relevancy 55.47%

I am trying to find a way of creating a drop down list that will drop than of list create to that one data? includes a How column down more include more that one column of information My cell that I wish to select from the drop down is How to create a drop down list that includes more than one column of data? A I have a list of items on a separate worksheet or further down to select from the drop down list is column A includes a description of a product column B is the price and D is the type I would like that when I select the product in the drop down list for example from A that the price How to create a drop down list that includes more than one column of data? from B and that the type from C also will stay with the product and appear in all cells A product B price C type so the price and type would remain with the product regardless if they were not chosen A B C Product Price Type Shoes Sports Sandles casual So If i was to select shoes in the drop down it would appear in cell A and would appear in cell B and Sports would appear in cell C nbsp

A:How to create a drop down list that includes more than one column of data?


Hi, and welcome to the forum. You could use a userform for what you want. If you want to go that route I could or some one else may be able to do it. I'm not sure how what you want can be done with a formula. May be some one with more knowledge may be able to do it that way.

Take a look at the attachment. This is something I put together for another user. It can be modified for your purpose.
Relevancy 55.47%

Dear sir

I want to group by Column A (i.e. Item) and Column N (i.e. length) and also want to add the sum total of each change in lengths of column E,F,G,H,I,J In the column K subtotal of E:J lenghts of column & want to add the average of L,M,N

Pls find the attachment what i looking for

A:need vba code to group set of data by column A:A and sum and average of lenght of col

i have used a pivot table to produce the results
However,this may not work under all conditions and data entered
Relevancy 55.47%

Hi there! I'm creating an access database for our company's mailing list.

I have two fields in my form named ID1 and ID2. When I create a new record, I would like Access to copy the information from ID1 into the ID2 field, and add three letters to the beginning of the data.

In other words, if ID1 is "123" i would like ID2 to automatically be generated as "RTC123".

ID1 is an autonumber field if that makes a difference.

Is this possible to do? How would I go about making it happen?


A:Access 2003: Copying data automatically from one column to another

Hello, if the data is already in column 1 then you can use an update query to transfer the data to column 2.
If the data in column 1 is to be entered in a form then the transfer can be made as the data is entered.
Which way do you need to do it?
Or do you need to do it both ways.
Also why do you need to store the data like that if it can be created and displayed like it at any time?
Relevancy 55.47%

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
Relevancy 55.47%

Utilizing MS Access, is there a way to separate alpha characters from numeric characters in one field/column & create 2 separate fields/columns for each, e.g., HRES4212 to HRES [field 1] and 4212 [field 2]? I am familiar with "text to column" in Excel, but it's very laborious if the number of alpha characters is not consistently 4, as in the example above, e.g., RLC22, EWP1312, etc.

A:Separating data in one column to multiple/separate colummns


Welcome to the forum. Below is some code that I found. It's for Excel though and not sure if it will work with Access.


Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Dim x As Long
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
.Pattern = "\d"
End If
End With
Set Myrange = ActiveSheet.Range("a1:a2") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
C.Offset(0, x) = Outstring
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub
Relevancy 55.47%

I have a database which has about 60000 rows and 3 columns.
Here I have made a picture to show what exactly I want, this picture represent a general view of my data structure and also represent what I want.

I have 3 first columns (from left) and I want to create a new column like the last one which named New Text.
As you see I want to create a column which duplicate the Text of every row for X times. And X is the frequency of repeated number in Number ID 2.

For example: In Number ID 2, 8 repeated four times. So New Text should have four H. (H represent the text)

Please consider this to be done by Microsoft Access software, which I have no experience of working with.

A:How to create a new column using data of other columns in Microsoft Access

will take TWO updates.

1) a transaction to ADD Column "New Text"; commit

2) another to UPDATE "New Text" where "Number ID 2" equals "Number ID 1" value is TEXT;

(or something close to that - - it's be a while since I last used SQL grammar.
Relevancy 55.04%

I am starting this Access database from scratch. I have to import/upload a sales file weekly. I will be adding this file to a main sales data table. The issue I have is that this file does not have any dates on it. I need to be able to add a start and end date to the table.

Example fields that I would need/have

start date
end date

Item #
Store #
Sales Units
Sales Dollars

What is the best way to handle this? I have an idea - just not sure what order I should put my steps for importing the file, asking for the date range, adding the date to the table, etc....

Relevancy 55.04%

I am having trouble updating data from one table to another. I am not Appending the data.

I've linked the two tables on an ID field.
I created a select query to see the data I would update.
I changed the query to an Update query.
In the Update To field I placed the following: [table].[field name]

After running the query I noticed the update did not work. The data from the first Table did not get updated to the second Table it wound up being deleted from the first Table.

Your help is appreciated.


A:Solved: Access 2003 Update Table from another Table

Buttercup1, welcome to the Forum.
I am not sure why you wan to update the second table with data from the first table.
Can you show me a screenshot of your query with the table(s) in and also your Table relationships?
Relevancy 55.04%

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:


From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
To this:

I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help

A:Excel macro - change column data to multiple rows

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub

HTH (welcome to the board)