I got a table, some columns are variable data you have to put in by your own and I got some columns with only formulas. After entered the last variable data I want excel to add a new row with the same formulas and format as the other rows in the table.
View 2 Replies
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
I have a table created in Excel 2010 by a SQL query. the query pulls 3 columns of data - Resource Name, Contract Company, Labor Category. I then add a column called "KEY". KEY is derived using VLOOKUP. VLOOKUP is matching the Contract Company from the table created with SQL to a table called "Rate Key" in another worksheet in the spreadsheet based on the matching the Contract Company. That all works fine. If the table changes due to changes in data coming from the SQL db, the column I've added using VLOOKUP recalculates correctly. Then I add 2 more columns. One is called RATE - which is a number that I manually type in. The last column is called RCode and is calculated by the RATE times the KEY. Whenever I refresh the SQL query, all of the the calculated rows work fine; however, the data in the RATE column that I manually enter does not move - it stays with the row regardless of whether or not the name changes when the new SQL data comes over. How do I get the values manually entered in the RATE column to move with the correct row when the rows change?
View 2 Replies View RelatedIm trying to find the best way to insert data into table.
source of data is workbook range and/or array the obvious one is
Code:
With Sheets(1).ListObjects(1).ListRows.Add.Range
Cells(1, 1).Value = xxx
Cells(1, 2).Value = yyy
Cells(1, 3).Value = zzzz
end with
but if its lots of data it could get very slow.
Q1. is there a way how to insert multiple records at once, something like
Code:
'does not work
With Sheets(1).ListObjects(1).ListRows.Add
.Range(Cells(1, 1), Cells(1, 3)).Value = arrXXX
End With
I found it faster to store data into array and then paste them
Code:
rw = ListObjects(1).HeaderRowRange.Row + ListObjects(1).ListRows.Count + 1
range(cells(rw,1),cells(rw,3).value = arrXXX
but I had issues with it especially if the destination table has some filters applied. I'd also prefer not to clear the filters during the sub
Q2. are there any other options that would be fast and reliable?
An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies View RelatedHow to make excel automatically insert a comment into a cell when a / is entered into that cell?
The comment needs to simply include the date the / was entered and nothing else.
I have two sheets one = where I am entering day to day data two = For getting result
Once I entered data in first sheet then I want date wise record in another sheet in which I will enter date. After entering date all the record will be inserted in another sheet from first sheet And main sheet will remain with all records
Is there a way to auto insert a cell Comment when a particular value is entered and then have the Comment copied to a cell in another worksheet in the same workbook?
The value entered into the cells i want this function to work with is from a Validation list. If the cell's value was to change, for instance the user going back to a cell to change the value for whatever reason, I would like the previous comments to remain and then give the user the choice to edit comment or not.
I've had a look in the Forums using search but nothing close seems to be forthcoming.
We have a table that tracks customer payment dates with corresponding payment details.
Our current Excel program works fine when the Customer payment dates progress (non-duplicate date entries) ... BUT .... sometimes a Customer will send multiple checks on the same date (duplicated date entries), and then our program gives incorrect results.
You will see that this Customer sent in three checks all dated 11/22/2013 ($100, $50, $50).
What we need is a formula that will look at the dates in Column-A, choose the last entry (before any zero or blank), then provide the corresponding value in Column-D.
So in this case, the result we need is (100.00) ... the value in cell D16. Our program is erroneously providing 100.00 ... the value in D14.
I have a protected sheet. When someone enters a value in column A, I want a value to be inserted into the corresponding row in column G.
E.g. When I enter date in cell A45, I want the time this date was entered into cell G45
I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :
[Code] .....
Attached File : Data Entry Macro.xlsx
How to insert a column to table depending upon name of the other column using VBA.
Like i need to insert the column in a table besides the column name x.
So how to search for column name x and insert column besides it.
RowCount = Worksheets("T_Stunden").Range("A1").CurrentRegion.Rows.Count
With Worksheets("T_Stunden").Range("A1")
[code]
I need to change it so it goes into the table as at the moment it goes one line below when I finish with the entry via Userform.
I'm getting extremely frustrated with trying to find some code that allows me to paste an ADO recordset into a database table!
What I am doing is on a weekly basis is creating a plan of which containers my company needs to devan. We have a system that stores shipping information about these containers and I get shipping information delivered to me via e-mail from one of our major shipping companies. Their data is more accurate so what I do is paste the data I get from them into my container planning sheet and import it into out Teradata Warehouse to query against using ADO and I import this one record at a time. This is extremely slow!
In order to get around this I want to create a recordset from this data and then paste the recordset into a table in the database. So far this is my ....
Have attached a small 2007 excel spreadsheet to help explain my needs. I would like to know if there is a function that when data is entered into column A it is then transfered to column B, B data transfers to C, C to D, D to E etc. with the K data falling off (10 Columns). I need this process to happen even when the new A input data is of the same value as the last A input data.
View 4 Replies View RelatedI have a main "ControlSheet" with a list of client and info in Columns A,B,C and E
In Column D of this "ControlSheet" I enter the Staff member who the client has been allocated to (e.g "Staff1")
A B C D E
1 Ref Data1 Data2 Staff Date Client Allocated
2 102 1000 10001 Staff1 01.06.2013
Each staff member (there are 7) has their own tab set up (e.g Staff1, Staff2, Staff3 etc)What I would like to achieve is when I choose the relevant staff member in column D of the "ControlSheet" (Lets say D2) I would like the Data from A2,B2,C2 and E2 of the "ControlSheet" to be copied to the next available/empty row of the Staff members own tab.
So in this example (above) A2-E2 would be copied to the "Staff1" tab as that is the staff member chosen If I was subsequently to change the Staff member chosen on the control sheet from say "Staff1" to "Staff2" I would then like the Data or Row to be removed from the "Staff1" Tab and added to the next row of "Staff2" Tab.
I am working on an excel table in my worksheet contains formulas and locked cells. lets say table range is from column "a" to "f" and there is formula on cell in columns "c" and "f". I just locked cells in column "c" and "f" to prevent people changing the formula accidentally. but when the sheet is protected i am not able to insert or delete rows as the rows has locked cells in columns "c" and "f".
I tried the below macro to unlock the sheet, insert row and lock the sheet again:
[Code] .....
So by running the macro it asks the password and do the job but the issue is it needs password and i do not want people to know my password. I don't want them able to unlock the workbook and make any undesired changes. I want them to be allowed insert or delete rows and input data in unlocked cell. I want the sheet to be locked all the time and only when people want to insert or delete a row it be unlocked and be locked again after inserting or deleting whit no need to insert password in this case only.
I currently have a 200 row table spanning 10 columns. There is one numeric column and the rest are free text. The table charts the different contents on one of the shared drives here at work. The drive is frequently in use with things being added and removed from it.
I have an excel table already which can sort the contents in order of size (numeric row) or name of the folder (the first text column). I have two buttons at the top.
The first inserts a row, which I did by recording a macro and then copying the code into the button.
The second I would like to do the following: When a user selects a cell in a row, the button, then clicked, deletes the entire row. Not just the data in it, but excises the row itself and stitches the surrounding materials together. The code to delete a row I could obtain easily, but I can't work out to say "find the selected cell, and take out the row it belongs to".
I'm trying to create a macro that will allow me to insert two rows to extend two separate tables. The problem is that one table needs to be directly below the other. So if the number of additional rows exceeds the margin between the two tables, the macro will just be inserting two rows into the top table.
I guess I'm looking for a way to get the macro to insert a row, not at a specific row number, but at the first row of the bottom table, which will change as rows are added to the top table.
I want to insert a frequency column in my pivot table. See frequency.jpg for an example.
The column has to count the number of times "artikel" is represented in the pivot. Is it possible to do this in a pivot table, and if so, how?
I need a Module that allowed me when I run it to add Rows down according upper cells Value for Example;
Total
Names
2
Sami
5
Rami
Then after apply the Module I need like this;
Total
Names
2
Sami
[Code] ............
I currently have a spreadsheet with 4 columns that represent Surface corridinates and Bottom Hole Corridnates.
Surface X, Surface Y, BH X, BH Y
We need to get the Bottom Hole XY to be directly under the Surface XY.
Attached is some sample Data and What I mocked up for a temp solution is in VBA Module2
RunAll it will complete 399 rows of data.
In Module1 I have the starting Code that I modeled off of a macro that I started to get the base code.
I just need this to loop till it has no more data to copy. and not use module2 at all.
I commented everything for my personal benifit and to help me understand the steps. also know its a monster right now but it does most of what I need it to do.
Sub CopyPasteBHXYs()
'Wrote Based on recorded macro
'Selects second data row and inserts a row above
Rows("4:4").Select
Selection.Insert Shift:=xlDown
'How to make it loop till the last data is reached?
End Sub
Side Note I'm finally taking a Excel VBA course Apr 8th and 9th.
I'm using Excel 2003. I have about 190 rows that I use on any given day to enter start times & end times. I calculate the difference in Column E. Is there a formula that will calculate the average time as I enter them in the rows? Some days may have only 100 entries, other days may have as many as 190. I don't want to keep adjusting the average formula for column E.
View 2 Replies View RelatedI'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.
So to be sure I am describing correctly, I am after a formula that will enable me to
1. Enter a value into a cell (for the hours worked - lets call this B1)
2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1)
3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.
I am trying to get two text boxes on a userform to populate a third box on the form when the user inputs the data in both of the first two boxes. I am pretty sure i need to use a change event, but I am not sure how or what triggers them. so I have:
[Code] ....
but they don't work when I put data in VSShortPrem and VSLongPrem textboxes. Do I need to call them to the userform module?
I have a file in which records are saved in chronological order. What i want is when i click on a button it should prompt me for a date and when i enter the date it should retrieve the data from the database sorted in chronological order from the date i have entered.
View 10 Replies View Relatedi currently have an email button setup, so when the person has filled in the sheet and clicks the button it sends the active worksheet to the specified email address in the code
i need an extra step in this code to check that data has been filled in, in certain cells before the email is sent
for example i have numberous cells but in cell i17 if something is entered then there must be something entered in cell i19. If there is nothing filled in cell i17 then the data validation can ignore this. Basically it needs to check that corresponding cell(s) are filled in "if" the first cell is filled in (make sense?)
i would also like it to check certain cells are filled in (mandatory cells)
here is the email button code, is there anything i can implement in that to produce the above results? or if this cannot be achieved maybe a warning box to pop up before it sends to say "Please make sure everything is filled in corrently before submitting" (something like that)
I have a command button [Start] that when clicked, will enter the time the process started. I then wish to have the cell locked so the time cannot be changed after it has been entered. I tried this code:
Private Sub CommandButton7_Click()
Range("I5").Select
ActiveCell.FormulaR1C1 = Now()
Cell.Locked = True
End Sub
But am getting an error for the "cell.locked = true"
Any proper code to lock the cell after the data has been entered?
I currently have a monitoring spreadsheet which my staff enter data into each month. There have been some problems with staff entering data wrong so I have decided to build a userform to prevent errors. I have 2 combo boxes one for selecting a month of the year and another for a particular call the user must enter data for. I want the userform to enter the data from the userform into specific cells in a specific area. e.g. If a member of staff entered data into the userform for February, Call 2 then it would appear in the relevant boxes.
View 5 Replies View RelatedI have an Excel spreadsheet that I use to schedule employees. I use this to assign sections to various employees. I was wondering if there is a formula that will restrict me entering the same "section" (or data) on the spreadsheet twice. The page is rather large and sometimes I can enter the same section without knowing it until I manually check it.
View 5 Replies View Related
