I have a worksheet that I will be allowing other to access so that I can get them to input certain data. I will be protecting the sheet to avoid having them inadvertantly overwrite any of my formulae. So, I have a matrix on the left for them to input data into with calculations to the right that they cannot change. Can I prevent them from moving cells within the input matrix
View 1 RepliesI am trying to design a spreadsheet for planning a project schedule. Down the left are the dates, across the top are the resource names and in the content are the activities being carried out.
Each activity I have is given a two letter prefix, e.g. TD = Training Delivery. I then have conditional formatting applied to each cell to change the cell colour based on the two letter prefix. In all there are six conditional formatting rules.
Now, my problem is that I need this sheet to be really fluid. I want to be able to drag and drop blocks of activities to a buffer area at the side and then drag them into another part of the schedule. However, when you drag and drop (effectively cut and paste) the activities around the conditional formatting moves with the cut content, whereas I want it to remain where it was. I guess what I'm after is a way of dragging and dropping values only.
The best I've come up with so far is an onChange macro that reapplies the conditional formatting to the relevant part of my spreadsheet, but I can't believe this is the most elegant solution. Is there perhaps a way of triggering a macro onPaste?
I new for a forum I have a small problem I just stock and can´t see what to do. So i need to move data from 4 columns B,C,D,E to F,G,H,I depending on input in column J, like if in J2 i enter letted M (or any from column A) i need to move data from B12,C13,D13,E13 to F2,G2,H2,I2 and so on. I dont have experiens in programming in exel or using formulas.
View 14 Replies View RelatedI have a matrix A with 12 rows and 10 columns. My problem is if in the cell(i,j) there is data then the same data should appear in a similar matrix B, but in a cell which is 15 cells behind the cell(i,j).
That is it should start counting upwards from cell (i,j) in B and once it reaches the top of the matrix it should continue counting from the bottom of the immediate left column and go up. When it reaches the 15 cell from cell(i,j) in b, it should print there the value that was in cell(i,j) of A.
I have a spreadsheet with a User From that transfers data from the user form to a sheet each time OK is pressed. On my workstation, the form stays in the middle of the screen. On the PC where it is in production, it moves to the left of screen a little each time OK is pressed until it is hard up against the edge. We recently moved to Excel 2003, and I'm wondering if there is a setting in Excel that contols this.
View 5 Replies View RelatedI'm an intern working at an aerospace manufacturing house and I need to automate a lot of the excel files I created last summer.
Anyway, here is my problem:
I've been making macros but I am worried about people moving the worksheets around or changing their names which will have the potential to mess up the macros. What preventive measures can be taken/ changes in the code? I feel like there is something very simple I am missing.
I have multiple dropdown lists (forms combobox, NOT activex) on a worksheet -- once the user selects them, a keypress on the downarrow will move to the next dropdown list.
is there a way of preventing this? instead, i would like to move focus to the next cell (same column, 1 row beneath the combobox)
I have an IF formula on one worksheet that refers to another worksheet (ex: =IF(Page1!$A13="";"";Page1!$A13). The problem is when I move data from one cell to another the formula becomes #REF. (ex: =IF(Page1!#REF="";"";Page1!#REF)).
View 6 Replies View RelatedI have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
In VBA I'm trying to grab numbers from a cell range (ie: A1:A10), and save them to a matrix or variable. I will later need to grab additional numbers and add it to that matrix. I have experimented several different ways, but belows demonstrates what I'm trying to do:
VB:
my_data = Range("A1:A10").Value
my_data = my_data&Range("B1:B10").Value
i have a spreadsheet model that i have inherited that needs some rigour. currently, users are inputting the bare minimum and then running away with calculation errors all over the page. I want to be able to "force" them to fill in certain fields before allowing them to move on. Eventually, I will do this via a User Form but until then, I need something to help my lovely users along in the right direction.
So, if a user inputs a project name (say G15), they will have to also enter Project Type (I15) and Start Date (K15).
while I am here, I want to be able to have the Project Status show as Potential if the Start Date is in the future. I already have validation set to limit the user choices to "Current,Completed,Suspended,Potential,Cancelled". I am not sure how to get the validation list to be contingent on the start date.
and lastly, does anybody have a little piece of code that will make the Start Date show as the day that the Project Status was changed to Current? So, even if the Start Date was initially a few weeks away, if Status is changed to Current tomorrow, the Start Date should be overwritten with tomorrows date.
I am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.
First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.
I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.
Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.
what I want. It relies on an input box asking the user to enter a part code, which when found creates a duplicate copy. However, if the user enters a part that doesn't exist I get a run-time error. Can anybody point me in the right direction so when a user enters an incorrect part they get a message telling them it doesn't exist.
Private Sub SortTest_Click()
Dim s As String
Dim r As Excel. Range
Range("A2").Activate
s = InputBox("Enter the number you wish to find")
If StrPtr(s) = 0 Then
MsgBox "You must enter an existing part number!"
Else
Set r = Cells.Find(What:=s, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
Range(Cells(r.Row, "A"), Cells(r.Row, "AH")).Copy
Sheets("APL").Cells(r.Row, "A").Insert Shift:=xlDown
Application.CutCopyMode = False
Application.Goto Sheets("APL").Cells(r.Row, "H")
Selection.Offset(-1, -5).ClearContents
Selection.Offset(-1, 0).Select
End If
End Sub
I have a Rental Form that i'm working on, and to tidy things up when I print it out, I would like to move a cells data down 1 row if that cell is empty. Here's what I have:
cell b8 - First and Last Name
cell b9 - Address1
cell B10 - Address2
cell b11 - City, State, Zip
cell 12 - Phone #
etc.
Not too many people have an address2, so when that field is empty, I'd like to move Address1's data down to it's position (address2). If address2 has data in it, leave address1 where it is. Simple right? This moving would happen when the command button is hit and the form data goes to sheet 1 which works fine.
A spreadsheet that can be used to enter the hours worked by staff each week and then calculate their Gross pay, Tax and Net Pay. THe basic spreadsheet has been created, however it will require aq macro to copy the Week ending, Hours worked, gross pay, tax and net pay columns to the first empty cell in row 1, clear the entries in the week ending and hours worked columns that have just been created and then hide the give columns to the left. which show the data for the previous week.
I know this sounds confusing but ive supplied the file to help you understand. The problem is that we can get the macro to hide the previous five entries and copy 5 new ones in their place but it only does this once. Just about our whole class is having trouble with this as we're not very advanced in macros.
How do I input an IF Formula to prevent Zeros appearing in the TOTAL cell ....
Example: TOTAL Cell (A10) =sumA1:A9
ALSO difference between two sums
Example TOTAL Cell (A20) =SumA10-A19
I'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.
Here's the history...
I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.
Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?
I have one excel file sheet in which we enter client data and another page in the same file that formats the data. Each new row is another client.
It is a simple formula in the data formatting sheet ='data sheet'A23 for example.
The problem is when we add client data above A23, say 2 rows, the format data page will follow the original cell and now say ='data sheet'A25.
How do I keep the cell as ='data sheet'A23 no matter what I do the original page?
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
how to do some complex stuff in Excel but sometimes I don't know how to do some really easy stuff. After I have named a data set of say a certain ten cells in one column, is there a function or a way to input the name and have that data populate a different ten cells? For instance if I have a huge amount of data all coded and I want to take equal but different portions and run them through a template, how can I make it so I just have to enter the a code for any of the data I have coded and have it populate momentarily where ever I need it to go? I'm sure this is very easy to do unless I have explained it inadequately.
View 7 Replies View RelatedI have a spreadsheet that has a couple of columns that ask for "Move In Date" and the other "Move Out Date". These dates are used in other calculations so there can be only one or the other in each row. I have users that mistakenly either leave both blank or both popluated. Is there a way to stop them with a message telling them that a date needs to be entered or deleted, maybe with data validation?
View 1 Replies View RelatedI have a spreadsheet whee teammates can inut the date in one cell and the exact time in another cell - how do I lock the cell with the time so it can't be changed?
View 1 Replies View Related I'm trying to create financial statements on a sheet with quarterly and annual data. Is it possible to create like a button to change inputs on a cell
Income Statement for Abercrombie Fitch Company Class A ANF from Morningstar.com (2).jpgIncome Statement for Abercrombie Fitch Company Class A ANF from Morningstar.com2 (2).jpg
whats the best way to insert a button like in the jpg example
for example i have this data in rows B and D:
austriaaustria
icelandaustria
austriaiceland
austriagermany
icelandaustria
germanyiceland
germanygermany
germanyiceland
germanyiceland
germanyiceland
germanyiceland
germanyiceland
austriaiceland
austriaiceland
and need to input in rows A (date in this example) and C(number in this example) which are adequate for each country so to look like this in rows A,B,C,D:
19 20 austria1,25austria
20 30 iceland1,25austria
19 20 austria2,2iceland
19 20 austria6,6germany
20 30 iceland1,25austria
22 00 germany2,2iceland.......
I would like to create an spreadsheet that prompts me to input data into a cell (eg; 'C1' - 'Quantity Ordered') as soon as another cell on the same row becomes populated (eg; 'A1' - 'Item Code'). I need this function to apply for several rows (25 rows in total), so that if data is then entered into cell 'A2', I get a prompt to enter data into 'C2', all the way up to a prompt for data in cell 'C25' when cell 'A25' is populated.
As it is imperative that I never forget to populate the relevant cells; I would also like to 'enforce' the prompt - perhaps by preventing the worksheet from being saved until the relevant cells have had data entered into them, or perhaps by using some kind of form, rather than a messagebox.
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I basically need to switch between ranges of cells to data input alot. so i thought of making a fixed field to enter the data which transfers the data over to designated cells .
Attached is an example.
I am not too sure which one will work, the If statement captures the data but when i switch out , the entry is gone .
I'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.
I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.
Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.