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.
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.
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.
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.
I 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.
I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
I have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.
column G - I have a group size recorded that should be in a room. column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked column I - I have the size of the actual group in the room.
Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.
It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked
OR
No, there was a booking supposed to be in the room but there was no one in it when checked
OR
There was no booking but there was someone in the room when checked.
With a set of data in colums by quarter and department names in by row I need to answer build a formula that answers simple questions like "what department had the fewest sick days per quarter?" and which department had the most sick days per quarter?"
I have a database with 65536 rows and A-BZ columns
What I am wanting to do is, on another worksheet be able to select identifying data from one or more columns from the database but have it display the entire row.
For example
WCP28 SX
WCP28 TX
WCT36 SX
WCT36 TX
In this example I would like to, on another worksheet, be able to create a drop down where I could select WCP28 and it would show me both the TX and SX row. Or I would like to be able to select WCP28 SX and WCT36 SX and see the rows next to each other. If it were for me I would just use the auto filter and manually compare but this is for somebody even less inclined than I am.
I would like to insert a function in spreadsheet A that looks up data in spreadsheet B based on _two_ comparison values.
The two comparison values in spreadsheet A would always be in the same row and the corresponding match in spreadsheet B would also have to be in the same row.
For instance, if the comparison values in cells A2 and B2 of spreasheet A match with the comparison values in cells A30:B30 of spreadsheet B, then show the value that is in column C30 of spreasheet B.
Excel macros. I'm trying to figure out a script that will look at alphanumeric numbers in column A (A2 --> for example) and compare it to the number directly below it and above it. If it matches either, do nothing, but if it is different, delete the entire row that the different number resides on.
An example of the data would be:
A
12A34A5 12A34A5 67F86F3 - when it encounters these rows I need to delete. 23G45G6 23G45G6
The sheet is a couple thousand lines in length, so I can't deal with it manually or even with sorts, due to the other data in the other columns.
I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?
I have a spreadshhet which has data by month, year, week and bi-weekly. I would like everything to be monthly. How can I put a formula which will look up the cell and see if it monthly it will the value of the cell beside it, if it is weekly it will take the cell value and multiply by 2 and so on.
I want to be able to do, however. I want to make a spreadsheet that has all the equipment items preloaded into it, along with the quantity that we're supposed to have of said equipment. Then, I would like to import the data from the text file, having it match items in the spreadsheet to their equivalent in the imported text file, and then bring in the quantity scanned. That way, I can use some conditional formatting to show when there is a miss match between "Required" and "On-Hand" quantities.
What I don't know how to do, is get Excel to only import the name of the item, and the quantity scanned, instead of all of the information that the scanner spits out. Also, I don't know how to make it match the name, with the one preloaded into the spreadsheet, so that the correct quantities are matched up. Here's an example of what the scanner spits out: FIELD WIRE,6145-01-155-4256,[Scanned Quantity Goes Here],N/A,N/A,RESPONSE TRAILER,,
The scanner creates records like this the first time you scan an item. So if you scan Field Wire first, it's the first record. However, if you scan it fourth, it's the fourth record. How do I make that match up with a predesigned spreadsheet?
What's odd is that if I create the formulas above independent of the specific formula data (O5, P5, and Q5) and just type in numbers, those formulas do exactly what they should. Can I not compare data returned though formula calculations?
I'm interested in comparing the 2 columns of data as attached in the SampleData file, for eg. First customer bought 5 items and customer 2 bought 3 items. I'd like to show the difference which customer 1 bought and customer 2 did not buy. Will need to use that via VBA And also for loop if possible!
I need to make sure people enter City State and Zip correctly,
I need somehow to validate: City, NC 21312
Validate City with a comma right next to it with a space and then 2 letters and then space or 2 space and 5 numbers.
Is there any way to do this using Validation.
I guess I could separate the cells, but I would rather not do that. Or I could have a message box pop up and ask individually to enter each one, but I would rather not do that.
I'm making a data entry form for a user, and one of the fields is in a time format (mm:ss). Is there any way that I can validate that field in "cmdOK_Click()" so that when the user clicks "ok" on the data entry form, if they haven't entered a date a msgbox comes up.
I tried IsNumeric with an If function but to no prevail.
I have a Userform that has 4 Textboxes (named TextBox1, TextBox2, TextBox3 and Textbox4) and 7 Option Buttons (named OptionButton1, OptionButton2, OptionButton3, OptionButton4, OptionButton5, OptionButton6 and OptionButton7). I need to validate that the following criteria is met:
TextBox1.Value Does not = "" TextBox2.Value Does not = "" TextBox3.Value Does not = "" TextBox4.Value Does not = ""
One Option Button is selected. (It can be any one of them 1-7, Just one needs to be selected).
Is there an easy way to check this data before executing a sub procedure to move the data?? Here is my try at it:
Code: Sub CheckData()
'Is this the correct way to check data ? If TextBox1.Value = "" Then TextBox1.SetFocus MsgBox "Please enter a Batch Date", vbCritical
I have a question that is bugging for quite some time, see if you know. Is it possible to insert a data validation drop-down menu to a pivot tables drill down data automatically?
I really do believe that pivot tables should have this option, it would make data validation much more easier. I going to suggest that to the guys developing Office 14, according to them the new Office will more Business roled based. Lets wait and see a !
I have 2 Data tables in 2 sheets of the same workbook. I want to compare my column A of table 2 with column A of table 1 and delete any rows of table 2 where (column A of table 2 has a value which is not in the column A of table 1)
In Excel I used the Vlookup function and deleted any rows which had Error in result of the formula. May I know how to execute this in VB
I am creating a spreadsheet where the user enters food they have eaten that day. The way this is done at the moment is with validated list boxes, one depending on the other using the indirect function. I want to be able to make this into a user input form. Is there a way of adding these validations from the spreadsheet, to an input form. I want the input form to have an input for the day, food group, food item, and quantity of the food. The food item needs to depend on the food group.
I have an Excel workbook which contains data entry fields, which have different types of data validation rules - like Lists, Date, Whole Number.
I do not want end users to remove these data validations as well as the formatting of these cells by doing copy/paste. So, I have implemented techniques mentioned in the following post, and elsewhere - to override the paste functionality and implement PasteSpecial values automatically.
[url] To keep it simple, I'm only supporting pasting a single cell at a time.
Now my problem is this: Doing the PasteSpecial values programmatically doesn't prevent the user from pasting values in the cell that violate the data validation rules. So, I can paste a string into a cell having data validation as Whole Number, or a invalid string into a cell having data validation as List.
The following post just suggests disabling paste whenever data validation is present: [url]
But I would like to allow the paste operation if the value being pasted is a valid value for the cell's data validation.
I need to search through a worksheet for a value located in the first A column eg. and then put down a flag ( or bettar the searched text ) to che previous row in B column
Es:
I search the text 'xxx' in A column
if I found the searched text 'xxx' in A5 i need to have a flag or better 'xxx' in B4
Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
I am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
Our school system is trying to clean up student records. They have a demographic worksheet of hundreds of records. Each student has a student ID number. We are trying to flag students whose ID numbers in 9th grade do not match an ID number for 10th grade. so we are left with only students who have matching ID numbers for both 9th and 10th grade. We are trying to find the right function(s) to make this work without VBA. I am attaching a small sample file
