I'm currently creating an inventory tracking spreadsheet for my restaurant.
I'm using ="*"&A(x)&"*" to create the necessary barcodes. Some of my inventory numbers have leadings zeros that need to be included. The formula above is eliminating the 0's and how to get them to show. I've tried a custom number formatting but that isn't working for me.
I have a spreadsheet gathering information on vehicles. One of the pieces of information is a 'Date Off Strength'. For the vehicles still on strength I have the symbol '#N/A' showing,, this is linked to a sheet using a VLOOKUP formula, is there a way to stop this symbol showing
View 4 Replies View Relatedi want it to stay on working sheet asit prints out the selected sheets below.
is there anyway to stop this from showing the sheets? ie stay on working sheet
I have data that has values showing 0 and I don't want them showing up on the chart. I have excel 2003 and 2007 that I need it to work for.
This is what I am using for excel 2007 and it works fine.
=IF(COUNTIF('Closed Transaction'!I6:I38,"Friends")=0,NA(),COUNTIF('Closed Transaction'!I6:I38,"Friends"))
I need a forumla for excel 2003. The one above still shows the 0's on the chart.
when I apply an = 'Worksheet1'!A1. formula to a cell, the respective cell will contain a zero. Is there any way that the cell can be shown as completely blank and still hold this formula?
View 3 Replies View RelatedI am building a small accounts package and need certain work books to open with no toolbars or row and colum headings showing.
View 7 Replies View RelatedI have a cell that has a formula in it and is also Excel Dates & Times coded. Hence when the repsective cells are empty and there is no values to be calculated the date "00-jan-00" shows. how to prevent this from showing up allowing for a blank cell to only show?
View 4 Replies View RelatedI've forgotten how to prevent the macros from showing in the macro dialogue box.
I'm sure it's got to be something with the VBA editor, but I can't figure it out.
I've got some files where the macro list is hidden and I'd like to do the same again, but for the life of me....I'm stumped. I'm sure it's something very simple.
I've protected the Worksheet & Workbook.
I've done some searching...."macro hide", "hide dialogue" etc. but can't seem to find what I'm looking for.
It's been a while since I had to amend code, as everythings been running very smoothly.
I have a program that requires the last 8 days to be total, unless there are 2 days off in a row. So my cells need to sum the previous 8, but then stop when two zeros show in the column and then restart following. One zero should continue to total the previous 8 days. As shown below, for 1/10/14 I should see 57.5. The sum needs to restart on 1/13/14 to show a total of 10.5. I have tried multiple formulas with no luck.
Col A Col B
1/5/140
1/6/1412
1/7/1412
1/8/1412
1/9/1411
1/10/1410.5
1/11/140
1/12/140
1/13/1410.5
1/14/1411
1/15/1411
I've seen a lot of timestamp solutions but none of them fit my needs. Also my VBE skills are not good enough to create this. So here's the problem:
I've got 3 columns E,F en H. H contains a validation list with 3 options OK, NOK and OPEN.
If H is NOK I would like E and F to be blank. If H is OPEN I would like E to contain a start date wich doesn't update. If H is OK I would like F to contain an end date wich doesn't update.
I have a link set up in an Excel document that plays a .wav audio file. The problem is it just plays the file. No media player comes up and I need to be able to manipulate the file when it is playing (i.e. stop, pause, play, Ffwd, Rew). Can someone tell me how to link the audio file up with a player? I am using a Windows based system. Unfortunately, Mac not available to me.
View 4 Replies View RelatedI have a spreadsheet that has
worksheet_calculate and also worksheet_changes, its a combination of the 2...
and it's purpose is to make a record of the number every time it changes by populating it into the next row, so I end up having a very long data list
and what happens is that my spreadsheet has real-time data coming into one cell which makes it constantly changing....
needless to say, when my spreadsheet is opened, it starts to do it immediately...and it wont stop as long as the number keeps on changing.
I would like to make 2 buttons - one says START, one says STOP
and when I click on START, then it will start making record of this number and when I click STOP, then it stops recording the changes of this number.
I'm using code to delete rows in a column when the cell is blank. However it is not working and the cell is not blank, but appears to be.
View 9 Replies View RelatedI am attempting to create a datasheet to track the actual time an agent takes a break or lunch. Here is an example of the data that I am using.
name Date Lunch Sch Brk1 Sch Brk 2 Actual Time
BrandonHolt8/19/201411:10 8:30 1:20 8:24:20 Break
BrandonHolt8/19/201411:10 8:30 1:20 11:42:34 Break
BrandonHolt8/19/201411:10 8:30 1:20 12:57:46 Lunch
Because I have two break times, possibility of more depending on the agent, how do i create a formula that will look at this data, take the schedule time for say break one and only apply that against the time that is more in line with the closest actual time?
How can I make XL (2000) display the macro start/stop recording and relative/absolute box that used to appear whenever I started recording a macro?
Such an apparently simple issue, but I can't resolve it!
Somehow I lost this small box, and I know how to force it to display via customize, but it doesn’t seem to work the same (relative does not work as such) and the buttons or box disappear.
The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?
View 9 Replies View RelatedI have a small project at work where I am being asked to put a simple spread sheet that will calculate a start/stop time - and also include the date. For example:
Start time 5pm, date: 3/13. The spread sheet to auto calculate what the stop time and date will be if a specific amount of hours is to be calculated. For example in this case, 12 hours. From calculating in my head that would be 5am the following day. However, how can I get this in excel to work and therefore all i would have to do is enter the start time and date, + 12 hrs, and excel would calculate the time/date after the additional 12 hrs.
I have this code :
[Code] .....
Great Timer. Have this assigned to a button to start, how can I work in a button to stop or pause this code from running?
I have a piece of code that put a check in all checkbox in column B from row 5 to row 50 but in column C, I have data from row 5 to 38. I want the macro to stop at row 38 in column B. When the cell in column C is empty stop putting checks in column B. How can I make this macro Check all checkbox down column B and stop when column C is empty. How do I add a loop to stop when the cell in column C is empty?
Private Sub CommandButton1_Click()
Dim CB As Variant
For Each CB In ActiveSheet.CheckBoxes
CB.Value = False
Next
End Sub.......
As you can see below, I've written code that writes random numbers into three columns of a spreadsheet (10 numbers in each column).
What I want to do is create code that will run the random number generator for a period of 1 minute and then stop. I know that I will need to write a timer subroutine to do this but I'm how unsure how to do this.
I would like to know the easiest way to temporarily keep a worksheet code from running while I am editing, then turn it back on when I am done. I was thinking a button with these commands(?)>
Application.ScreenUpdating = False
Application.EnableEvents = True
but I don't know which button to use, or if I would need a button for each.
I have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty
2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.
but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.
Series [b]Start Range End Range Qty
1 1 1 1
3 3 3 1
5 5 7 3
6
7
I have a timesheet where user updates start and end time for various tasks.
I have placed a time capture button in the excel sheet (which is simply a macro saying =now() function)
The user clicks it before starting and after finishing the task. The start and end times are captured in adjacent cells.
If the user starts the work, and goes on a lunch break say for 20 min, comes back finishes the task and captures end time, the time difference will not consider break time which is non productive.
How can I incorporate something like 'pause' option so that before he goes for lunch he can temporarily pause the time.
Is there a way to force Excel to look at only the first characters in a field when searching?
If I use:
MySearch = Range("C3").Value
Cells.Find(What:=MySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
It will find any instance of the characters entered in C3; however, I want it to find only fields that START with those characters. E.g. if I enter TRA in the search box it will come up with CITRATE when I want TRACLEER, etc.
Can I adapt this code so that users receive the alert if they try and go to the next field without filling in the required ones, or - better - to combine it with a code that does not run a macro, but instead gives the alert, if the required fields have been missed. (I don't require anything to do with printing, this was the closest code I could find)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheet1. Range("A1").Value = "" Then
MsgBox "Cannot print until required cells have been completed!"
Cancel = True
End If
End Sub
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.
View 4 Replies View RelatedIs there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
I'm trying to use conditional formatting to highlight cells in a column that have less than 8 characters.
I know the LAN function, but I don't know how to make it work for the conditional formatting.
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So:
Charles Johnson -> Johnscha
John Smith -> Smithjoh
Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
I have a tab within a workbook where i want the user to copy a different spreadsheet into and then press a macro button to run some code, however after this button has been pressed i want this one tab to show any changes that have been made to it, i.e make the cell red or something just so i can keep track of manual adjustemnts.