Auto Shift Cells Down OR Copy Data To List
Jun 28, 2013
I'm trying to have one cell be the constant place I enter data and then when I hit enter, I'd like the data I entered to be moved down to the cell below it. Then for every new number I enter in that first cell, it would auto shift the previous data down and add the new data to the cell below it.
Basically, I'd like to record data but I dont want to have to scroll to the bottom every time to enter new data since this list will be continuously updated. And I'd like the most recent data entered to be on top.
Then I want to take the top 100 cells in that column, and do a running average of them.
View 8 Replies
ADVERTISEMENT
Apr 9, 2013
I have a column with data but some rows are empty, i want the empty rows to be filled with exact data from the upper cell so that no row remains empty. How can this be achieved?
View 4 Replies
View Related
Oct 31, 2006
I've got a problem with one of my macros. This code should import a text file. The name of the imported file is the same as the value of the cell that is active the moment the macro starts. The way I've written it, the code can't find the needed text file.
Sub addfile()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:aprojects" & ActiveCell.Value & ".txt", Destination:=Range(ActiveCell _
))
.Name = ActiveCell.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0.........................
View 5 Replies
View Related
Nov 27, 2012
I have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I
so bascially i have a list at the moment like this
Q
R
S
[Code]....
There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution
View 2 Replies
View Related
Jan 3, 2013
I have A dropdown list (form control combo box) ...in that the values are jan,feb,mar,apr... property of cell link is given as C4..... now my doubt is suppose if i select mar from drop down, in sheet the cell C4 should Show Mar only.....
View 6 Replies
View Related
Feb 19, 2013
I would like to auto-fill cells based on what I select from a drop down list.
For Example:
Drop Down List is located in A1(already created). Let's say it lists items like bathroom,front of building, dinning room etc.
I have created on sheet 2 a list of responsibilities for each item on the drop list. The list has a header for example bathroom and a below it list the responsibilities. I did this for every item on the drop list.
Now, is it possible to select bathroom from the drop down land when selected the list I created in sheet 2 show up in the cell next to bathroom? Also, is it possible to have this happen every time i select a different item from the drop down list?
View 6 Replies
View Related
Dec 16, 2013
I am looking to auto generate a roster/schedule. I have a spreadsheet with shifts for each weekday that I need to populate every week with names to pick from a drop down list. the drop down list is made using data validation and looks at a dynamic named range to allow me to select only the people who are available to work a certain shift.
Is there a way to do this using vba?
View 3 Replies
View Related
Sep 22, 2008
I am trying to make a calendar spreadsheet to enable me to track allowance payments i am due from my company.
This payment is worked out by three conditions - where i am working (7 regions), how many days of the week (4,5,6 or7) and how many hours (<7,8-9 or 10-12)
I have made drop down lists to select each of the values for the three conditions and want to show the total for them
i can total the entire spreadheet for the month up using fairly large "COUNTIF" statements, but i want to be able to show what the exact amount is in each cell not just a complete total.
i.e. i am away for 6 days i get £15 extra, if i am in Europe i get another £50 and if i work 12 hours i get £30.
I want to be able to calculate this total in each cell for that day.
I can send a copy of the spreadsheet i have got at the moment if that will make things clearer than what i have tried to explain.
View 12 Replies
View Related
Dec 1, 2009
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
View 3 Replies
View Related
Jan 17, 2014
I have data validation in a cell that says only options from a list are allowed. i.e. =indirect(A1)
If this refers to a list where there is only 1 option available is there a way to display that value automatically without the user having to click the dropdown to select it?
View 1 Replies
View Related
Mar 21, 2008
I am using the same VBA that is discussed in several threads on the internet to create a combobox on top of underlying excel validation. The most relevant post I have found on this site is linked below, however I am told that it has expired and I can not reply to it so I started this new thread.
Data Validation Autocomplete Via Combobox Modification
The VBA code discussed in the above linked thread is working pretty well in my excel file which has MANY different fields being validated. However, there is a problem that I can not seem to get past. Specifically, the combobox is currently showing the validated data behind it, but will also allow IN-valid data to be entered. Is there a way to stop this and force only valid data to be used (i.e., is there a way to force a "re-validation" on exit from the combo box)?
View 5 Replies
View Related
Oct 7, 2013
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies
View Related
Apr 11, 2008
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
View 8 Replies
View Related
Oct 18, 2013
I have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)
[Code] .....
View 6 Replies
View Related
Oct 28, 2009
how to code the following?
I need to remove an item from a list, find its match in column "A", remove the matching cell and then shift the remainder of the column up to remove the empty cell.
If this helps, there are 5 different sheet names, and I'm working with range A4:Range("A" & Rows.Count).End(xlUp).Row for the items in the list.
To make it as simple as I can:
COLUMN A
one
two
three
four
user clicked on two in the list, so column A now looks like, with no gap between one and three:
COLUMN A
one
three
four
One sheet actually needs it to do the same removal to identical rows in column B as well (i.e. remove A10 and B10, or A45 and B45, etc.)
View 9 Replies
View Related
Jan 30, 2013
Recently ran into this problem where when I select(highlight) multiple cells, it automatically copies the highlighted cells.
[URL]
This auto-copy also occurs periodically when I select(highlight) a single cell.
Notes:
1. I do not have a copy hotkey on my mouse, as this also happen when I use the trackpad.
2. This only happens in excel. When I select multiple items on my desktop, it does not auto copy.
View 1 Replies
View Related
Apr 5, 2013
Any way to have the date auto increase 7 days when we drag/copy down the cells. For example, I have the following:
03/31/13
04/07/13
04/14/13
So, when I copy down (let say hundred cells down), the day should show 7 days apart. Is there a formula?
View 3 Replies
View Related
Mar 5, 2008
So im setting up a big list of stuff, and basically i have multiple sheets.
One of them is a "master sheet" so to speak, and the rest are sub-sets of data.
Is it possible to have the Master sheet updated automatically every time data is entered on one of the sub sheets?
Basically its an alphabetical list of names, and my sheets are "Master sheet" "sheet A, Sheet B" etc .... so If I enter a name on Sheet B, or format the cells on sheet B, is it possible to have that automatically copied to the master sheet?
its becoming a pain to have to enter the same name on two sheets manually.
View 10 Replies
View Related
Jan 21, 2013
How to populate MTD data linked to the other spreadsheet file (monthly data) to the table on the left, depending on the month selected month from the dropdown list.
Attached files for reference :
Dropdown Month.xlsx‎
MONTHLY DATA.xlsx
View 3 Replies
View Related
Sep 11, 2013
I have two worksheet. One worksheet is a form where in the Field Door No. should be a dropdown list. The list will be retrieve from worksheet Source. Now upon selecting a specific Door No. it should also autopopulate the respective Type, Brand, Serial No., and Type.
First thing i need to know is what should i do on Cell B1 worksheet Form for it to be a drop down list retrieving the Column A on worksheet Source.
Second, upon selecting for example ATC0009 on the drop-down, it should auto-populate like these
Door No. ATC0009
Type AT Crane
Brand A
Serial 43679
Type Crane
View 14 Replies
View Related
Jan 2, 2010
macro which autofilter data & copy to another sheet.
below mention are the steps, i dont knw how to write vb code to autofilter month. Please find sample workbook on
below mention link
[url]
1.Auto Filter Date 2(Column C)
2.Select First Month (eg.May 09)
3.Auto Filter Date 1 (Column B)
4.from, the month, which filter in Date 2 (from May 09 to Dec 09..last month of year)
5.Auto Filter Column A
6.Copy each unique value on output sheet
View 9 Replies
View Related
Jul 4, 2006
I just want to check that if it is possible to copy specific cells from one row to another sheet by using macro or any other script.
Attached is a sheet which explains what exectly I want
The sheet one is having Source data
Sheet three is the result sheet
If I put some code numbers (in number form) it should search the data from source sheet and update the same in result sheet in different rows
View 9 Replies
View Related
Jan 11, 2013
I'm trying to make a spreadsheet that can be used to easily build a collective list of steps, for a user to read and follow line-by-line.
I want a source sheet of "steps" that I can change over time, and the resulting tabs that reference the source sheet get updated/populated automatically.
I've pieced together some VBA code from other sources, which kind of does what I want it to:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 And Target.Column = 1 Then
Dim SourceSheet As Worksheet
Set SourceSheet = ActiveWorkbook.Sheets("Steps")
Dim TargetSheet As Worksheet
Set TargetSheet = Target.Worksheet
Dim c As Integer
Dim Source As Range
[Code]...
But there are some problems:
1) The data auto-populates into a row. It would read better if each step was in the same column, meaning rows would need to be automatically added upon selecting something from the drop-down list. The number of rows that need to be added vary based on the number of steps in the source sheet, for the selection made from the drop-down list.
2) If you make a change to the source sheet, my goal is to have the other sheets referencing the source sheet's lists of steps update automatically, so you only need to update the steps in one spot and everything you've built from them gets updated instantly. Currently, you must select a different choice from the drop-down list, and then change it back, before it populates the "new" steps from the source sheet.
This is my first time using VBA.
What I have so far is attached: testAutoPopulate.xlsm
View 2 Replies
View Related
Aug 23, 2008
I'm currently using a Excel 2007 btw... I'm a newbie when it comes to VBA programming... I have attached a sample file for reference. I need to automatically copy the contents of Sheet1 to Sheet2 and Sheet3 (meaning everything you edited on Sheet1 should be automatically edited on the 2 other sheets.) Here are the conditions:
- the items with zero (0) quantity from Sheet 1 should be hidden on to the 2 other sheets. but when you replaced an item with 0 qty to other values (any value greater than 0), it should appear on Sheets2 and Sheet3. (Not affecting the format of the table)
- items without price should be highlighted.
- items that have values that are linked to other items should be highlighted. (ex. if the value for item1 for qty is linked to the number of qty to item 5)
- for Sheet3, Column D to Column G should be hidden
View 9 Replies
View Related
Aug 18, 2014
Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.
View 1 Replies
View Related
Sep 26, 2007
My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with.
View 14 Replies
View Related
Mar 19, 2009
I have a list of names in column A, don't know how many different names but for sure some of them repeats in different cells of column A.
A1 = NAME_1
A2 = Alan
A3 = Ben
A4 = John
A5 = Alan
A6 = Kevin
A7 = Mark
A8 = Dominic
A9 = John
A10= Tom
A11=Alan
A12=Frank
How can I make aonther list in column B, which contains a list of name in column A without repeating. So that B1 = NAME_2, B2:B4 is exactly like A2:A4, then B5 = Kevin, B6 = Mark, B7 = Dominic, B8 = Tom, B9 = Frank.... and so on. Another question, which is very similar to the previous one. In the same column A, now I add an AutoFilter (Data > Filter > AutoFilter) in A1. A drop down arrow button will appear at the right side within A1. Click the down arrow and another box shows: Sort Ascending, Sort Descending, All, Top 10, Custom, Alan, Ben.... the rest of the data in Column A but each unique data will only shows once in that list. How can I copy the contents of that box?
View 5 Replies
View Related
Nov 7, 2009
I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.
View 5 Replies
View Related
Feb 6, 2007
I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...
View 9 Replies
View Related
Feb 6, 2009
If I put a length in (G13) such as 4.44 & position from (H13) is 1. Then I want that length of (G13) to move its value (4.44) into (C15) the 1st cell to add to is (C15)
Then Move (C15) to (C16) 11.12 becomes C16
and move (C16) to (C17) and so on all the way down the C column.
When it hits the last joint (C374) position 360. I want a messageBox that says you can not insert anymore.
Basically shift the existing values down untill (C374)
I have only column C to shift the calue from (G13)
I do NOT want to delete rows!
Below is how it looks before macro:
GH12LENGTHPOSITION134.441
C14LENGTH1511.121612.221711.561811.861913.112012.872113.062211.992312.03
Below is how it will look after macro: The RED moves to (C15)
C14LENGTH154.441611.121712.221811.561911.862013.112112.872213.062311.992412.03GH12LENGTHPOSITION134.441..........................
View 9 Replies
View Related
