IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E
3 4 / /
5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
I have two columns that have similar information. I want to copy the unique information from one cell to a third, blank cell. In my case cells d2 and cell e2 are almost identical except cell d2 has addition information. I want to copy that information into cell f2. I have a feeling I am making this harder than it needs to be.
View 2 Replies View RelatedI have a range of cells (D10:D20) with numbers in them. I need cell A10 to look at D10 and return a specific number based on a set of criteria.
The same thing would go for A11:A20 & D11:D20.
If the number in D10 is 1-2 then A10 needs to display 0
If the number in D10 is 3-8 then A10 needs to display 0.1
If the number in D10 is 9-14 then A10 needs to display 0.2
If the number in D10 is 15-20 then A10 needs to display 0.3
Once I have that formula I know that i will just highlight A10 and drag the formula down through A20.
I'm am trying to restrict the length of data that can be entered into a cell based on a drop down in that row. I have achieved this, but what I need to be able to do is use data validation to restrict the cell length to one of two values - so for example the length can only be 6 OR 9 characters long.
I cant see how to do this. At present the best I can do is rectrict to between 6 and 9 - but this would allow 7 and 8 which is not allowed.
I'm going to be using a spreadsheet to keep track of where different people are at. So if Person 1 is in Room 3, I will stick a 3 in the box next to their name and then can look at the spreadsheet whenever I need and see what room they are in. When I'm deciding what room to put a person in, though, I need to be able to quickly glance at a list of Room #'s and see what one's are still available. So I have a bank of Room #'s in the spreadsheet....1,2,3, etc.
What I'd like, is some way to set this up so that when I put, for example, "3" in the cell next to "Person 1" the spreadsheet automatically removes "3" from the bank of available Room #'s and when I delete the "3" because the person has left, it adds "3" back to the bank of available Rooms.
I have a table with 4 columns where in column 3 I have part numbers and in column 4 I have the number sold. In column 3, the part sold can be listed several times with a different number sold in the column 3. What I need is code to go through the part numbers and return a total number sold for that part in a new column. I have sorted the table by part number, then by Column 1 which is for the date. The new total should go in the last column with the oldest date for that part.
View 2 Replies View RelatedI have a worksheet that I want to populate with specific data from another worksheet.
If all of the following are met:
1) Column A equals "763" and
2) Column K is formatted Red (255, 0, 0) from conditional formatting
3) Column P is blank
Then in my blank worksheet I need to display the data from Columns A, B, D, K, O
I am setting up a spreadsheet for a Stock Broker. I need to have a cell produce a value dependent on the value of another cell. Specifically, I need the calculation cell to change depending on whether another cell has the letter A, S, Q, or M. I am calculating the value of investment income based on the Payment terms (A;annual, S;semiannual, Q;quarterly, M;monthly) So if for example Cell A7=A, then Cell C7 =”specified formula for Annual income”
View 5 Replies View RelatedThe data attached shows the length of individual fixations (A4:A8) and whether the fixation was ON (=1) or OFF (=0) a specific target (C4:C8). I want to know how much time was spent ON target for each 250ms time bin (i.e. between 0-250ms, then 250-500ms etc).
My problem is that each fixation is a different length and may cross over the time bin boundaries. IF(AND(THEN ) works for the first few time bins but I get stuck once the first fixation is accounted for. I have filled the rest in manually so you can get an idea of what I want it to do.
So I've been trying to set up a table.
The table is made of 4 columns, with several times the same values. Each column has a specific range of value. No possible combination can exist twice. Meaning I won't have two rows that are identical. It looks like :
A1@X
A1@Y
A1&X
A2&X
B3#K
B3#O
B3£K
B4£K
C5$P
C5$M
C5*P
C6*P
The user tells me the value he wants in the 3 first columns, and I want to tell him what's in the 4th one.
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).
What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.
Is there such a formula or do I have to enter manually the amounts into an If function?
I have 5 options that I am designating as "Allocation Methods" for a number of line items. They are numbered 1-5, but i have custom formatted them so that they have text in the formatting. They are as follows;
Allocation Method
1 - Allocation per Hard Cost
2 - Allocation PSF of Proj. or Hotel
3 - Allocation per Loan
4 - Allocation per Parking Spaces
5 - Allocation for Park
I will allocate one of the above allocation methods to every line item in an allocation column by hardcoding 1 - 5 in a cell for each line item. What i cannot figure out (or figure out if it is even possible) is how, for instance, can I type in a 1 and it have it automatically formatted per number 1 above & then if I change my mind to method 2 and type 2 it will automatically format per number 2 above. I know I can do this by creating a reference column right beside by allocation column, but I would prefer not to use that method.
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying:
Sub usstates()
Dim R As Range, s As String
Set R = Sheets(1).Range("J1:J50000")
For i = 5 To R.Rows.Count
X = UCase(R.Cells(10, 1))
If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then
R.Cells(i, 2) = "SC"
End If
Next i
End Sub.................
I am trying to extract some data based on two criteria. The first is that one row cell value is greater in than an agreed date/time, in my case this is 7:00am yesterday. The second is that the first 5 characters of a second cell, in the same row but several columns along, are not equal to 'Monit'. If both of these are met then the code will strip out several cells of data from that row.
I can do the first and last parts of this but the 5 characters check is proving a little more difficult in my limited VBA knowledge. The second cell is simple text format and I can't do a LEFT equation in a different cell to get those 5 characters (restricted workbook range so no room). how to go about running a check? I'm thinking it's probably quite simple but trying to search for an answer to it is tricky.
I have a column in one of my spreadsheets that I would like to split. There's currently not anything that can be used as a delimiter, nor can I use fixed width. the cells in question start with numbers and end with letters.
View 7 Replies View RelatedI have an excel sheet here with the data as
1.0 Develop Vision and Strategy
1.1 Define the business concept and long-term vision
1.1.1 Assess the external environment
1.1.1.1 Analyze and evaluate competition
1.1.1.2 Identify economic trends
1.1.1.3 Identify political and regulatory issues
1.1.1.4 Assess new technology innovations
1.1.1.5 Analyze demographics
.... etc
I need to split this into two columns column A should contain 1.0 and column B should contain Develop Vision and Strategy . Ex: 1.1.1.2 Identify economic trends in column A then column B - 1.1.1.2 column C - Identify economic trends.
Count formula. I have few cells highlihgted in different colours based on the conditional format. Is there a count formulat to count the numbers of cells based on the colours.
For Example - I have 3 cells highlighted in green, 2 in red etc. I need count formula to count all green cells and count red cells.
Is there any way to insert a carriage return after each instance of a character? I have a section of data that requires a carriage return after each instance of -v eg:
View 8 Replies View RelatedExcel 2007 - Windows XP
I have the below in cells A1 and A2. I need to find the last comma then return all characters after it. I need the same formula for both cells because the value is always changing.
HUG,PU-TUB,BW,-,-,64 Need to Return 64 in B1
HUG,PU-RFL,BW,-,-,176 Need to Return 176 in B2
how I would go about referencing a cell on another worksheet to return only the last so many characters from the data in that cell. EG:
Sheet 1 cell A3 contains the number (stored as text) 12345678
Sheet 1 cell A4 contains the number (stored as text) 98765432
On Sheet 2 I want to put into cell B2 5678 (the last four characters from the data in Sheet 1 A3.) Also on Sheet 2 I want to put into cell B3 5432.
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
I would like to have a sheet where there is a cell designated as "start number" (where any number may be entered) and a cell designated as "how many times". I would then like to be able to click a button and excel generates a list of sequential numbers starting from the number designated in the "start number" cell as many times as is designated in the "how many times" cell.
View 2 Replies View RelatedLets say we have several numbers and combination of letters/numbers. What can we use to commonly refer to them correctly,
34566
R3456
88900
D2344
I tried to use
Like “*####”
But that’s not correct
there is a none VBA way to remove everything from a cell but the numbers.
Example: A, DAVID (002081) becomes 002081
The cell the formula will go in is F4 and the cell its looking at is K4.
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedI would like to parse the below :
Say this appears in A column , I want to parse all the characters appearing after the numbers (i.e., 8.625% /5.875%/6.125% ) in B column - Any excel formula ??
Alere Inc. 8.625% October 1, 2018
Delphi Corporation 5.875% May 15, 2019
Delphi Corporation 6.125% May 15, 2021
So B should column like below
8.625% October 1, 2018
5.875% May 15, 2019
6.125% May 15, 2021
I have a list of data in Column A of a spreadsheet that is all at different lengths.
I would like to return the numbers from this (see below) seperately and also everything after the last number so returning "green"
Example:
EAST MIDS, YORKSHIRE0112012green
EAST MIDS, YORKSHIRE0112012red
EAST MIDS, YORKSHIRE0112013black
EAST MIDS, YORKSHIRE0112013brown
Looking to create a Formula (not Code):
IF CELL A1
1. NOT Between 8 and 20 characters OR
2. NOT contain at least 2 numbers OR
3. NOT contain at least 2 letters OR
4. contain characters (e.g. punctuation) which are neither numbers or letters
Then FALSE.
For example the cell contains 1,M2,M7,M1,M8:2,M15,M9,M4,M5:3,M3,M6,M14,M11,M12:4,M10,M13 and I need to create M1, M2 , M3 ..etc columns and attach the coresponding number to each one of them. For instance M2,M7,M1,M8will get 1 and so forth and so on. I was acutally thinking using the Notepad to replace the , with a TAB space and paste them back in to my data set and create some IF statements. On the other hand running VBA scrip would make it even easier. However I would preffer to use a function (that I am not 100% familiar with) and leave the VBA scrip out of the question for the moment.
View 12 Replies View Related