Is there a way to flag data that hasn't been used in another worksheet. For example, a second worksheet consolidates the data into two different groups, but someone spelled the name of one of the groups wrong in the first worksheet and the data was not added via a sumif function.
View 2 RepliesI am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.
I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
Still I have to check and manually insert extra missing for every half hour missing but that's bearable.
This formula inserts a "missing" every time it finds a gap.
My question is: How can I insert a row above every cell with "missing"?
How do I do that? Here is also my excel sheet.
CR1000_Meteo_20131113_2_CLEANED.xls
I have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
I have stock data values being updated on my sheet every 10 seconds in one Cell (E5).
I am looking to indicate a change in that cell value by changing cell color (or could be any easily programmable indicator). I want to change the cell color on first change of value but not subsequent refreshes. Stock data is numeric in string form. colors used not important as long as there is a visible change.
I Have tried playing with sheet change events but no progress as data is not manually entered.
I have some data stored in a database and when it is extracted into excel each entry's data is presented in both horizontal and vertical formats. e.g.
Entry1 value1 value2 value3
value4
value5
value6
Entry2 value1 value2 value3
value4
value5
value6
I have a macro that will transpose the vertical data into horizontal format but if there are not the expected number of 'value' items below the entry it will not work correctly. If in the above example Entry2 only had value4 and value5 below it the macro would fail.
What I need is a way to validate that each entry has the correct number of values below it before the macro is run and some way of highlighting or drawing attention to these problems.
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies View RelatedI have a worksheet (mileage) which has a client column (column B) which is validated so that the user can only choose clients from a dropdown list. The dropdown gets its values from another sheet (data) in the same workbook. In the data sheet each client also has a recharge flag (1 or 0) in a column beside it.
In the mileage worksheet column E is ‘mileage’ and column F is ‘recharge mileage’. What I need to do is create a validation rule or macro that only allows a user to input a value into the recharge mileage column if the client whom they selected from column B has a recharge flag of 1 (i.e. they are a recharge client). If they try to input a value without the client being a recharge client (or without selecting a client) it should give an error message.
I have a list of items in column A. And a "flag" in column B. I have attached a small sample. What I need to be able to do is "Hide" all the items in column A where there are no flags for that item in column B. This I can do. The problem is that if a flag shows for an item, I want to show ALL of the rows containing that item, even if there are no flags for some of them, and hide the rest.
View 3 Replies View RelatedI have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View RelatedI have a table with 21 numbers two rows with x and y e.g.
nr.xy
205,5
195,4
185,3
so on ........
Now I wish to make a table with 100 numbers in wish y is interpolated based on y in the given table. Using many IF statements (in Dutch "ALS") isn't useful since the number of statements is becoming too large and besides is a number is missing e.g. nr7 x=0,7 the y is 0.
This is not working properly:
=
ALS(P12<$H$28;"Bodem";
ALS(P12<$H$27;$i$28;
ALS(P12<$H$26;$i$27;
ALS(P12<$H$25;$i$26;
ALS(P12<$H$24;$i$25;
ALS(P12<$H$23;$i$24;
[Code] .....
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies View RelatedI need to take in third sheet all data which are missing in second sheet from first sheet, for example:
IDName LastName
1John Doe
2Jany Tine
3Paull Marphy
4East Doe
5West Marlyn
6ThirstanWist
7SecondEast
8ExampleMiddle
9Doe Tryer
10MarlynEarly
Sheet 1
IDName LastName
1John Doe
4East Doe
9Doe Tryer
10MarlynEarly
Sheet 2
And in Sheet 3 to get result
2Jany Tine
3Paull Marphy
5West Marlyn
6ThirstanWist
7SecondEast
8ExampleMiddle
Sheet 3
I am trying to import data from web to excel. I posted the link below to the website I am attempting to import from. The only part I need is the statistics. However, I cannot get it come thru by getting external data from web.
The goal is to great an auto updating spreadsheet with multiple sheets so copy and paste is not feasible.
[URL]
I have ben given a excel file from another department at work as they had been told i know a bit about excel. Excel... Yes, Visual Basic... Nil, zilch, nada! It really might as well be written in chinese!!
What has happened is the guy who set this up has since been made redundant from the company and they now want the file to do one more thing.
Basically what they want the file to do is, when the user clicks the 'generate file' button, if E2 has no date entered then a message box pops up stopping the user from going any further until a date is entered.
I need to be able to create an average total that skips any missing information. For example, if I have four values I need the average for all four (e.g. 5+6+7+3/4), but if one is missing I need the average of three (e.g. 5+7+3/3).
I found the array formula: =AVERAGE(IF($A$1:$A$50,$A$1:$A$5)) [Note: I pressed Ctrl+Shift+Enter to make it an array formula]. Which works perfectly for cells that are next to each other (e.g. A4:W4). The trouble I am having though is that the cells I need to pull the data from are not next to each other e.g. (K4, N4, S4, W4).
I did try to ammend the array formula with the following: =AVERAGE(IF($K$4,$N$4,$S$4,$W$40,$K$4,$N$4,$S$4,$W$4)). But of course that came back with a "too many arguments" error.
How can I ammend this information to make this formula work.
Extra information if needed: The cells that have missing data are not blank or have a 0 value, they show a "#N/A" (which doesn't need fixing).
I am importing information from 20 stores by item. If a store doesn't carry an item it is not creating any data for that store but in turn it throws my whole template out of whack. The graph would be a repetitive 1-20. Can I create this pattern and import the information to match while creating blank cells when the data isn't available? I manually entered the left column and the items columns "B" on are imported.
excel store column test.xls
StoreSKUDescriptionMfg.#UPC
115170016GUN,BB BUCK 00000000210539256221051
225170016GUN,BB BUCK 00000000210539256221051
335170016GUN,BB BUCK 00000000210539256221051
445170016GUN,BB BUCK 00000000210539256221051
5
6
775170016GUN,BB BUCK 00000000210539256221051
885170016GUN,BB BUCK 00000000210539256221051
995170016GUN,BB BUCK 00000000210539256221051
10105170016GUN,BB BUCK 00000000210539256221051
11115170016GUN,BB BUCK 00000000210539256221051
12125170016GUN,BB BUCK 00000000210539256221051
14145170016GUN,BB BUCK 00000000210539256221051
15
16165170016GUN,BB BUCK 00000000210539256221051
17175170016GUN,BB BUCK 00000000210539256221051
18185170016GUN,BB BUCK 00000000210539256221051
19195170016GUN,BB BUCK 00000000210539256221051
20205170016GUN,BB BUCK 00000000210539256221051
21215170016GUN,BB BUCK 00000000210539256221051
5170016
I have created a user form for some data submission. I am looking for a macro to check the text boxes, that I have named appropriately i.e. Name, Address, City, State etc. , to make sure there is something entered.
If nothing is entered it needs to return a msgbox that says what field is blank.
I have a pivot table based on a sales sheet called "Datasheet". I have created the pivot table using a dynamic name as below =OFFSET(DataSheet!$A$1,0,0,COUNTA(DataSheet!$A:$A),12). My problem is that I have 11100 lines of sales data but the when i refresh my pivot table it only seems to be going down to row 10979. Therefore missing out some of the data. Is there a max number of lines for a dynamic named range or is my formula wrong in any way
View 3 Replies View RelatedI have been searching the forum for weeks now for an solution to my need, but was unsuccessful. I have two Workbooks. One is for tracking new enrollments in the school for whom I work named WVT.xls and the other is for tracking the school materials assigned to each student named 2007.xls (for inventory accounting). I need to streamline adding new enrollments names to both Workbooks by adding to only one manually and then using VBA code to update the names in the other workbook. The sequence would be to type the names into WVT.XLS and run a macro in 2007. XLS to automatically update the names. Below is the locations of the same named columns in each workbook.
First Name Last Name
WVT.XLS Column B Column A
2007.XLS Column B Column C
In 2007.XLS, there is a sheet for each school location that is represented by a unique school code. In WVT.XLS, there is one sheet for all enrollments from the start of distributing materials. Each row is a student. There is a column that contains the unique school code to identify in which school the student has enrolled.
Any easy way to retrieve data from another table using an array formula.
I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table.
I have the spreadsheet attached. I attempted to use an if/and statement :
=IF(AND(D2=C8I:I,E2=J:J),H2,0)
but found out that it does not work with arrays and only found the first address by default.
How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name?
I'm using a worksheet that has a time value in column A.
The time is manually updated every five minutes.
Is there a formula that I can use to catch any missing times?
I have 2 sheets in the same workbook (sheet 1 & 2) with one matching column (A) of info and need them to combine and fill the missing data. Each sheet has identical column heading and the amount of data is not the same; sheet 1 has ~2000 rows and the other ~5000. Sheet 1 is consolidated so I would like to fill in the missing cells from sheet 2.
Ex. Sheet 1.......
Ex. Sheet 2.....
As you can see, the missing cells in sheet 1 could be filled from the data in sheet 2; also note that the above info is on different rows (and will be random for each case). I only want the columns to fill in that have a matching email, the other 3000 rows in sheet 2 do not match with sheet 1 and I don't want the rest of it to transfer over.
This code repeats for each of my 18 UserForm TextBoxes. Is there a better way?
Code:
If Trim(Me.TextBox4.Value) = "" Then
Me.TextBox4.SetFocus
MsgBox "Enter a Score for #1"
Exit Sub
End If
I'm trying to setup a macro to run on a database extract that is sent to me. Some of the data is not filled in for each row and I want to automate the auto fill down for each entry based on the Reference # above. I searched the forum for terms that I thought might be related,
Here is the starting data: ...
I have two lists of data. The Master data in sheet one and the monthly download in sheet two. I want to create a macro that will identify the new entries in sheet two and add it to the bottom of the list in sheet one.
View 2 Replies View RelatedI have 2 worksheets that need to be merged. More specifically, I have one 'master' XLS file with some columns included in it that are missing from a second XLS file. I need to merge the new columns (with column titles in row 5) into the second 'auxilary' sheet without touching data above row 5.
View 4 Replies View RelatedOn a weekly basis I update a suite of charts in one workbook that are created from several other linked workbooks. Once compiled I send this via email to a colleague who incorporates it into another report by printing the charts as a pdf. For some reason he often ends up with erroneous data (zero's where there shouldn't be on the charts), or often his system doesn't display certain elements like titles and axes etc
We think his system is spending resource looking for the original data and getting itself knotted up, but surely there should be a way of delinking the charts but maintaining their integrity as they are saved?