is there a way to validate a cell (that is already validated btw) to prevent it from being able to key in data to it a second time??
I have very low expectation of this but I guess I can always ask...
the cell has validation to assure unique records. I think I would have to add to this validation...
the formula is
=COUNTIF($C$9:$C$2008,$C$9)=1
I have a thread in here called "Inputing data values automatically based on data value in another column". I have determined that I need to use the VLookup function.
View 3 Replies View RelatedI am trying to create a form and I want the form to put values on certain spaces when box is checked. For example:
apples unchecked box
pears unchecked box
and so on.
I an going to create a list like this
apples
pears
and so on
total
If I check any of th boxes, I want a certain price to be posted besidesmy listand at the bottom to give me a total.
This is just an example of what Im going to do. No I am not creating a shoping list I just want to learnesd how to create the boxes and run macros.
The merged Cell B6:G6 will receive a ten-digit number followed by a dash and then one or more numbers. (For example: 1234567890-123)
Cell B15 will then receive data shortly afterwards. I already have a validation macro for this cell which allows either 'I' or 'I I I'.
Upon exiting Cell B15, merged Cell B16:H16 needs a macro which will check Cell B15 and if it contains 'I', Cell B16:H16 will display the data from the ten-digit number entered in Cell B6:G6 minus the first five digits. (For example: 67890-123)
Now the data in Cell B16:H16 can only be somewhat editable hereafter. It can be erased or replaced with numbers in smaller or greater digit combinations than five before the dash (i.e. 67890-123 can be replaced with 123456-7), and digits can be added after the whole group (i.e. 67890-123 & SEE DWG) without any error messages. But if any five-digit number with a dash and some numbers exist in Cell B16:H16, they must correspond with the number in Cell B6:G6 minus the first five digits.
However, if Cell B15 ever receives a 'I I I' afterwards, all data in Cell B16:H16 must be erased. Cell B16:H16 can never contain data if Cell B15 contains 'I I I'.
Also, if the data in Cell B6:G6 changes later on, the corresponding digits in Cell B16:H16 must change as well, even if there are digits after the whole group.
So here is an example of what a good macro would do for me: ...
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 want to do (seemingly easy task) of getting a price from a code, from a code sheet.
EXAMPLE:
Sheet1 (Log sheet)
A1 = "Code456"
A2 = $? <- This needs to be found.
Sheet2 (Price Sheet)
A1=Code123 B1=$1
A2=Code456 B1=$2
A3=Code789 B1=$0.50
Etc, and so on...
What I want to do is basically (without using macros):
"If A1 = sheet2:A1, then
A2 = sheet2:B1, halt. , ELSE,
If A1 = sheet2:A2, then
A2 = sheet2:B2, halt. ELSE,
So on and so forth..."
I thought there might be an easy way to do it with some sort of 'double sided' data validation drop downs or OFFSET function but I cant figure it out.
I wondering if it is possible for a macro to recognise whether or not there is text in an adjacent cell, and if there is, then to copy data from another adjacent cell.
For example, because there is data in C2, data from A2 has been inputted into B2. And any blank cells are left alone.
a
b
c
d
e
ref1
(cell A2 data here)
DATA
DATA
DATA
ref1
I have been wracking my brain around how to put this formula together.
Is it possible or it can't do more then 1 cell at a time?
Here is an example:
If F1= button request and E1= Scoring then OK (take no action) also i want to add that Folder in F and Corrupt in E would = OK
If it doesn't match to highlight the cell in red
i want to match a cell data with a range of cells and if matches return the cell reference in another cell
View 3 Replies View RelatedI am looking for a way to do what i would call a dual vlookup.
i have some data in a number of sheets and need to do some consolidation into a summary sheet. problem is, not all the sheets have data in the same columns and rows but all the data is the same.
what i am trying to do is something like
vlookup("a",sheet2!A:M,***need to put the column of the data here***,0)
i cant just put in the column number because it changes on each sheet, need some way to do another lookup there, like an hlookup or something, so that i could lookup which column contained 'sales' and return that number so it can be put into the vlookup.
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.
Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:
Tab 1 (Never changes)
AB
Bob1
Jon2
Fred3
Tab 2 (Dynamic, changes each week)
AB
Jon
Fred
Bob
So it would work as follows.
Tab2 column B will take Tab2 column A’s data check Tab1 column A and display Tab1 column B’s result.
Problem 2
Weekly league rank table that shows position movements week by week
Example.
Week1
1Jon
2Bob
3Fred
Week2
1FredUp 2
2BobNot Moved
3JonDown 2
Can Excel calculate/show the actual movements of league positions?
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies View RelatedI have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies View RelatedI am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I have 2 sheets in 1 document 'April Events' & 'Event Staffing' and I need to do the following
Basically, If 'April Events C5' contains any data, 'April Events D5' gets filled with the data value from 'Event Staffing G5'. If 'April Events C5' is blank, 'April Events D5' would be left blank. If 'April Events C6' contains data, 'April Events D6' gets filled with the value from 'Event Staffing G5' or left blank. If 'April Events C7' contains data, 'April Events D7' gets filled with the value from 'Event Staffing G5' or left blank e.t.c
e.g 'Event Staffing G5' = £12.57. On 'April Events' (C5=12, D5=£12.57) (C6=191, D6=£12.57) (C7= , D7= )(C8=1, D8=£12.57)
Is there a function/formula to do this?
I would like to be able to sort the data in this one cell alphabetically without retyping the data .ie use a sort function but only for a cell.
For Example cell A1 contains Chris Brown Andy
No commas there. Names are seperated only by spaces.
What function should I use in order to get Andy Brown Chris ?
I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx‎
see sample file, i need a formula to do like B column,
I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..
I am not sure how to go about creating a spreadsheet that does this:
I have a number much like a stock market index which is in cell A1 for instance, and this data changes every second,
And I wish to create my spreadsheet to automatically make a record of this change value and populate it at the last available blank row, so ultimately, I have a full history of how this number changes during the course of time.
5450 in cell A1
a second later, it changes to 5430
and so I want to populate 5430 to A2
and then another second, it changes from 5430 to 5470 and so i want to populate 5470 to A3 and so forth....
does anyone know how to do it?
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
Got an issue that I can only seem to do with things like the program Hotkey and making physical macros. I'm sure it is easy but I'm lost.
I have data in one book that looks like this (Call it Book1)
Date: Service
ProviderReason for callCRM
SR #DM
SR #WO #Device #Serial #Issue15/02/2010DATE: 03/02/10
TIME: 11:29
BUS: 1234AO
ISSUE DETAILS: Issue with Console
and data in another book that looks like this (Call it Book2)
DeviceSerial NumberNameTerminal PositionTerminal Group Location DescriptionTerminal IDDescription Service Provider 104012343030123456ConsoleDriver Console1234AO90401234Service Provider Name found Here
I need to find the BUS: 1234AO from the cell in Book1 then look that Bus up in Book2 and then from Book2 copy the Device, Serial Number, and Service Provider into the cells into Book1 and repeat this infintely often. The problem I have is the the bus can have 1 Console and 1-5 Readers and I need to find that data also. So for example I need to find BUS 1234AO and get the Console details and past that into one issue and the next issue will have the same bus number 1234AO but I need the details for Reader2 (that detail is in the Name field as shown above.) The issue details will always have the Console text and Reader# text in it.
The problem I have is that the Reason for call cell has ALT-ENTER characters in it and the details are not always in the same spot but the details are almost always in the format ####AO (four numbers and the letters AO)
how to split data into columns.
I have a set of (imported) data consisting of 1000+ rows. The data set (bank statement) contain six informations (date1, transaction, vendor, date2, sum1, sum2) in one cell which has to be split into six columns.
Using text-to-columns will generate more than six columns since the set dosn't have any delimiters and both the transaction and vendor name-fields contain blank spaces.
Is there a way to either split the data into the correct number of columns or if text-to-columns is used - a way to manipulate the data into the correct columns, for instance with help of filtering ?
small data sample included
The 1st set is in a column. Nice & tidy all twelve characters in length & alpanumeric
zx0123123123
zz0123123124
zy0123123129
The second set is a mess, taken from a lotus notes inbox from the subject line off an email, eg. there Blah blah zz0123123124 etc etc. What I want to do is do a reconcilliation between the two with a lookup, but have no idea which route to go down to get round the messy data. I have been using =IF(ISNA(MATCH(A1,'[spreadsheet to lookup to.xls]sheet1'!$A:$A,0))," No match"," Match")
but here the data is clean. I have searched teh forum ;o) DaveH & found something about > Instr() but this can't be used in formulas..
When Cell F3 is not equal to "CURRENT" then where (G3:G10) contains X
change X to A.
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
Excel 2010
A
B
C
1
Helper Column
Desired Result
[Code].....
I am trying to do is to make it so that users can enter data into my form (I have managed to create the form and am working on trying to figure out how to get the info entered into the form to appear in my worksheet) and then the data will cause the information to, basically, compile sentences that I want to appear in one of three cells at the bottom of the worksheet.
Essentially, at this point in time, the end goal is to make 3 cells that contain text. Eventually, this text will be indexed elsewhere, but that is for the next person to deal with. I am creating a prototype that I want to present as a process improvement.
So, when the user selects 3 in cell D7 I want the text "Customer purchased 3 Widgets at $20 each." to print in cell 82B.
I also want the user to be able to copy and paste some customer records from another program directly into a cell in the form that will just print (unchanged) into one of these boxes, too.
Lastly, I want the user to be able to select checkboxes that will, when selected, print a comment that will print in two of these boxes. For example, if the user selects yes for cell D26 then I want the text "Customer not eligible for free shipping" to appear in both cells C82 and D82.
Basically I have three columns in a work Sheet F, G, & H. F is empty, G contains text and column H has both text and numbers.
I want to be able to automatically copy the value from Cell H to Cell F if cell G contains the word cost.
I would also like to delete all rows where Column G & H contain two dashes -