Is there any way that Tab should automatically jump to next column, after the entry has been made.
I have only one number (one digit) per column. I wish that when I enter the digit the tab should go automatically to next column. And also if it is possible that tab may miss the blank column and go to the next where the entry is to be made.
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER()
Select Case Range("F4").Value
Case "Medewerker"
Range("S2") = "M"
Case "Interview"
Range("S2") = "I"
Case "Data"
Range("S2") = "D"
Case "Observatie"
Range("S2") = "O"
End Select
End Sub
How to Create a Work Breakdown Structure (WBS) in Excel automatically with given data through macros.
In the attached excel sheet
work_breakdown_structure_.xls
Is there any way to do it for data automatically?
I am trying to create a spreadsheet that will automatically increase the work order number by 1. The cell will always be in the same place. The idea is that when the file is opened it populates the number. After being closed and reopened the number will be 1 higher than the previous.
The page will reused by various people to create and print work requests for my mechanics, I want it to assign the W/O automatically.
I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.
The working code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False
End If
End Sub
N5 is the linked cell for the option button.
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedIn a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedI have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
Following code has 2 IF statements , the code runs the way it is and the first IF works perfectly, but the second IF does not work.
View 3 Replies View Relatedscreenshot.jpg
Ok, so from the attached picture (screenshot.jpg), you will see a data export of staff losses.
On the table to the right, i need cell G2 to take the ID from cell F2 and then search column A and add up every time there is negative number in column B.
So if staff ID has -15 , -15, -10 and -10 in the first table, their summary on the right table will show -50
(PS, i know it says AVERAGE LOSS) but i just need it to be the total. So basically, from the loss log on the left, it just counts the total losses occurred by each individual staff member on the summary table to the right.
I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.
In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.
I normally use the WORKDAY function in order to create an involved scheduling
program. However, I have no idea how to create a formula that would count
Saturdays as a workday. I usually type in one date and then the dates kick
out to an end date several months later. How do I create a formula to
include Saturdays on a daily projection (e.g. A1=8/12/06, A2=8/14/06,
A3=8/15/06,etc...)?
IF statement does not work?I have a chart on my sheet like so: (it gives me rates of pay)
View 3 Replies View Relatedi want to link one cell to another sheet if that cell is not blank. in other words if A2 says Decor, then I want to have the data on Sheet1 in A2 link to Sheet2 and say Decor. i thought i was clever and in the cell i wanted the link to go i wrote
=IF(Sheet1!A2>"",Sheet1!A2)
but it didn't work....
I have some user defined function in an .xlam file that works.
Right now since that .xlam file doesn't auto open. I have a button on specific workbooks that will open that xlam so all the udfs work.
Is there a way to embed the udfs into a specific workbook so that workbook is a standalone without the need for opening a separate xlam file?
I get the following sent to me on a daily basis and I would like to format it so that I can utilize it.
I get this:
GROFKCTotalCalls:3
TotalOrders:0
808-5600Calls:3
UNI1Orders:0
GROHJBTotalCalls:436
TotalOrders:75
3-Nov644-6200Calls:225
UNI1Orders:42
3-Nov646-7979Calls:76
UNI2Orders:15
3-Nov700-2900Calls:89
UNI4Orders:12
What I need is this:
Phone Calls Orders
808-5600 3 0
644-6200 225 42
646-7979 76 15
700-2900 89 12
How would you work with the data I get to produce what I need??? I know a macro can format the data, but maybe there is something else I can utilize that I'm not aware of.
I have the following:
Private Sub TextBox1_AfterUpdate()
'//Check for valid date
If Not IsDate(TextBox1) Then
Me.TextBox1 = ""
'Show SplashForm2 if not date
SplashForm2.Show
Me.TextBox1.SetFocus
End If
End Sub
I thought this was working but I put 22/07 in TextBox1 and stepping through it it goes from the "If Not statement" to the "End If"
Don't know why it is not recognizing that it is not a date. It is missing the back slash.
I wrote a little macro and put in into presonal workbook. I cant seem to get the short cut to work. When I got into " Tools > Macro > Macros > Options" and put in a letter that I want to use for short cut it does not work.
I am trying to run this macro in another workbook.
i need to work out employee turnover in excel has anyone got any useful tips?
i have a spread sheet with start and end dates so i need to work out who was in the company etc
To run a macro from the first tab of the workbook but have it do it work on another tab.
In the workbook in question I have a tab called "input" were certain information in updated and is linked or used in formulas on other tabs. Some of the tabs have macros that do various functions. I would like to set up one macro button on the "input" tab that would run the macros on the other tabs instead of having to run each one individually from its own tab.
Below is on of the macros that I would like to run from the "input" tab and have it update the "AP Accrual" tab
Sub CopyPriorMonthAccruals()
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,-1)"
Columns("L:L").Select
Selection.Copy
Columns("Y:Y").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B3").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,0)"................................
I recently took a macros my friend did and adjusted it so it would work for my problem.
It works fine but there is one thing i don't understand about it.
The initial problem was that i needed a macros that could copy a range and paste it 1 cell space to the left and then delete the last cell of that range, so it would look something like this:
FROM THIS:
HIJKLM2NIVEL
3
21
TO THIS:
HIJKLM2NIVEL3
21
so this is the macros i am using which works perfectly:
Range("H1").Select
Selection.End(xlDown).Select
Selection.Offset(0, 2).Select
RC = Cells(Columns.Count, ActiveCell.Row).End(xlUp).Column
Range(ActiveCell, Cells(RC, ActiveCell.Row + 11)).Select
Selection.Copy
ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 4).Select
Selection.ClearContents
What i don't understand is why i have to use the +11 in order for it to move 4 spaces to the right.
I have created an Add-In and hard-coded the file name assuming that it will be used with a specific file called, let's say, Data_Approval_Project.xls (where Project would change for a praticular Project name, e.g. Data_Approval_Alpha1.xls,
Data_Approval_Beta1.xls). Then I would just need to manually change the path and the file name in the Add-In for a specific user group.
Now I am presented with the case of a " Multiple Project - One User", where the same user will have to work on Alpha1 and Beta1 projects using the same Add-In (which actually accounted only for one of the projects). Is there any easy way to get this fixed?
I was thinking about adding an Input box, asking which project user wants to work on, then write the answer somewhere in the workbook, and find and open the file using something like the "Data_Approval_" & the reply of the input box string command.
when i enter data it dows not enter into the required cells. I have attached a SS that shows the initial laout and the new one and on sheet 2 the initial code. I thought it mught be a offset issue and have tried various permitations but being a total novice in VBA probably was trying to do something totally alien. On advice from moderators have added a new attachment, but had to zip it due to size
View 3 Replies View RelatedThe spreadsheet has been around for a long time. Even in the electronic form, it has been around for over a quarter century. How has the electronic spreadsheet improved or changed your job?
I'm taking a course in computer applications and will be using any information I get in replies to this thread in my term paper.
I have some problem with my excel formula here. It works in some rows but doesn't in others.
I have attached the excel file herewith : Book1.xlsx
i checked and checked and my IF function just gives me the wrong answer... attached is just a sample data..i have over 230 lines to check actually..
View 4 Replies View RelatedSee the attached workbook.
Please edit the formula in column B so that it only gives a BINGO if the adjacent cell in column G is less than 51.
So cell M16 should not be a BINGO because cell G16 is not less than 51.
Attached Files : Book1.xlsx