I have a barcode scanner I have setup with Excel to scan PC barcodes at the office.
Previously what used to happen is I would get a list of all PC's on level 6, under data validation I had an error alert setup. The source code was =$A:$A (A:A being the list of PC's). So basically If I scanned a PC that wasn't on the list a error alert would popup saying STOP - Take down PC details. This worked Fine.
I now need to use it for the opposite.
I have a list of PC's that are missing in column A, If I scan a PC on level 6 or wherever and its in that list I want the Error Alert to popup to advise me to take down the details. So the =$A:$A won't work anymore.
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?
I need the formula for statistical data analysis for a research project i'm helping out with, and this is too complicated for me to know where to even begin searching for the information. The problem is this:
I have a sheet (called Sheet1) containing 9009 rows of information on columns A to AL. I am interested in searching for the starting sequences of codes contained in column AI and returning values on the same row but in a different column. The formula that works so far is this (note: I use Excel 2003 and a swedish version, translated the formulas though)
I havent come up with this formula entirely on my own so dont think my expertise is this high In any case, this formula gives me the value of the A-column in the same row as the first N06 found in the AI column. If N06 isn't found anywhere in AI, it gives me "error". The problem is this: I want it to "continue" searching column AI for strings starting with N06 and returning the values in the A-column for the following N06s found. I basically want to wash out all rows of information that dont contain the string N06 in AI, and get the new information on another sheet.
To make it even clearer: I want to have all values on column A on a new sheet, but only where the AI value on the same row starts with N06. Eventually I want other columns as well, and also search for other codes, but that should be easy to figure out once I get the initial formula.
if a macro or private macro is necessary, I would need basic instruction how to use those as well (I mentioned private macro as I understand they automatically run whenever worksheet info is changed, which I would like in this example.)
I'm simply trying to change fonts in cells based on the inputs of other cells. Obviously, using conditional formatting doesn't allow for this.
For my example, assume the word "MrExcel" is in cells D2, F2, H2, J2, L2, N2, P2 and R2. If I type different font names (Times New Roman, Arial, Bookman, etc.) in the cells to the immediate right of each word "MrExcel," -- cells E2, G2, I2, K2, M2, O2, Q2 and S2 -- I would like the "MrExcel" word in each corresponding cell changed to the corresponding font to the right. I would like the ability to do this for up to 15 different fonts. There will be many rows to this worksheet I need to use this process for.
Is this possible? I'm in a bit of a jam and have exhausted my knowledge of formulas in that they won't work in this case ... I'm assuming I need to delve into the unknown land (to me) of macros, private macros, or VBA.
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
##################### 149 entries Column A - Date - OK Column B - Customer_Phone - Errors (Should be 11 digits) Row 21 - Customer_Phone - Error (Not 11 digits) Row 108 - Customer_Phone - Error (Contains letters) Column C - Outcome - OK Please correct and re-check. #####################
I have a table with 3 columns of dates and then a column with Set # that I feel in the box #.
I need to see how many items processed for each set per day.
Example: [url]
The problem is that it counts the correct amount but not with the correct dates. The formula that I use is: =SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code: Option Explicit
'Form level variables - used in more than one event Dim intAdult As Integer Dim intStudentSenior As Integer Dim intBalcony As Integer Dim intChild As Integer Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables Dim intCount As Integer Dim i As Integer Dim intAdult As Integer Dim intStudentSenior As Integer
Imagine I have 2 columns of information that look like this:
Column A Column B
AS Dog
AS Cat
AS Hamster
FT Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D Column E Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
I want to add some to a macro have which asks before performing the rest of the macro... "Are all details correct?" with a yes and no button. yes, carries on with the macro and no ends the macro.
I use a lot of VLOOKUP formulae in the sheets i put together and, despite both the lookup value and table array being in the same format (usually text format), i often get an #N/A being returned.
Here's the fun part... if i then do something simple like access the lookup value cell (either by double clicking or pressing F2) and then hit return, then the vlookup calculation suddenly returns the correct value.
I'm convinced it's something to do with the way the cells are formatted but can't work out what.
In this case the largest groups are: 50:01 - 75:00 = 15 75:01 - 100:00 = 25 100:01 - 125:00 = 15
55% fall in to this bracket.
What we are targeting as a business is that the majority of worker fall in to a similar range each month. So the challenge for my team is to increase the % value month on month. the close to 100% the better they are doing.
Equally what in Excel could I use to automate this calculation.
Looking for some help to figure out what is causing our problem. My friend has created the attached excel sheet in Excel 2003. The issue is that the a vlookup does not seem to be finding the correct line for the value from the lookup table when it is returned twice.
Let me explain - The details are contained in the attached workbook. There are three sheets in the workbook - NH3Curve, Samples and Qvalue table.
On the Samples sheet, in cell C12 and C13 you can enter varying values. Go across to F12 and F13 and you will see that they both have the value 22.1. This is where the issue occurs - in cells G12 and G13 the lookup value should be the same - but it is not. G12 is actually returning the value for 22.0 not 22.1. (lines 173 and 174 on the Qvalue sheet). We cannot ferret out why this is happening.
Any help would be greatly appreciated. Sometimes when you look at something you can't see the obvious, so I hope it is that simple.
What I am doing is selecting the field in A which equates to the lowest value in C (C= hourly sales), then I am subtracting 60 mins from the value selected in A. This in effect will give me my closing Times i.e. find the cell with Zero Sales then subtract 1 hour to find what must be the closing time (assuming of course that there is at least ?1 of sales per hour while open).
My cells in A are formatted as h:mm AM/PM, as is the format in the formula cell
I have an attached spreadsheet, where I have an IF statement within "O8". Bascially, the result in that cell should equal "50", however displays as "0".
I think this might be an excel glitch - as the same formula worked for "M8".
I have been working on a Spreadsheet for GVWR (Gross Vehicle Weight Ratings). I'm having a problem with the Formulas in Excel, I thought you may know what's going on and why it's not giving me the right data. I'm pretty good with Excel, I've been using it for about 17 years for calculating Tank formulas, etc, but have never run in to this before.
I have three columns to the right of the sheet. One is Stock Vehicle weight of an item____(E) The next column is the Aftermarket weight of the item______ (F) Then the third column (G) is the total weight minus the stock weight using a very simple formula =SUM(E6-F6) gives the correct weight in the third column, but at the end of each area (Topic) i have a Total Weight area in the third column (G), using the formula of =SUM(G6:G26) which should just add up everything in column G, but it doesn’t. I have re-made the spreadsheet three times,
it returns an error when I try to enter it. Is there a brave soul which a good eye for errors that might be able to shine some light on the error. Currently it appears the error centers around the MONTH( ) portions of the function. But each embedded function is able to stand alone and I can't imagine why they won't work together.
I have put together the below macro to take data from an Excel spreadsheet and place in a word documents (using bookmarks.
What should happen is it should open up the correct word template depending on what data is in column E (Servicecode) and fill in the bookmarks with the data from excel.
It all works fine and goes through the loop and saves, inputting the correct data into the bookmarks but after looking at the saved templates and scrolling through using F8 the IF ELSE does not seem to work. It CALLS theBearSSR macro rather than the relevant macro e.g. word in E2 is Cele, the macro should call the CeleSSR macro (which opens the correct template).
I don't think i have declared my DIM Servicecode correctly... I can't seem to figure out how to make the IF ELSE condition look at the Servicecode and then go to the correct sub routine (the difference between them is the template that is opened).
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2 Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2 Event Assistant@City of Saint Paul
Sample Data 3 Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3 Marketing Coordinator@Town & Country Caterers
I have a userform with several fields located in it. For the most part, it looks like a user information form, Name, Date,, stuff like that. There is a field that the user enters some information, and using the afterUpdate function for the field, it searches to see if the value is unique. The problem is that when I tab from that field, if the value is not unique, I want the cursor placed back in that field (textbox). SetFocus doesn't seem to do what I want, unless I am using it incorrectly.
I have a question regarding Do While loops. I'm trying to write a do while loop to search through an array for a particular value and return the row number. This value is in the first column of the array and there are 211 of each value chronologically sorted. So, the first column from top to bottom reads 1,1,1,1..211 times, then 2,2,22,..211 times and so on. For this case, I want to return the first row where a particular value is found.
i've been having for quite some time now. I need to input quantity of items into the existing database that holds all the items names which is sorted by category. Below is a sample of the problem:
Table 1 - Items with quantity that is waiting for input:
Quantity Product 5 Button A White 7 Ribbon B Blue 8 Thread A Black 10 Cloth A White 3 Button B Blue 4Button C White 9Ribbon A Pink15Button A White Above is just a part of it, the list goes on to over 200 rows long.
