Identifying Cell By The NAME Of Cell Next To It?
Jan 5, 2012
Basically, let's say in Rows(1), I have:
A1 = Apple
B1 = Peach
C1 = (empty)
D1 = Orange
...
Now, assuming that I don't know C1 (the cell location), but I want to insert something next to it, or for the entire column, will I be able to identify the cell by the "name" of the cell next to it???
I know where "Peach" is, and I have already inserted a blank column next to "Peach" 's column, and now I want to name the first cell of the inserted column
View 5 Replies
ADVERTISEMENT
May 14, 2014
I have some data that has names but is not consistant i.e
Smith, John
Smith,John
I want a formula that says, if there is a space after the comma do nothing, otherwise insert a space between.
I have figured out the second part so my formula will look like
=if(some logic to identify a space after the comma ,"",already figured out this bit)
View 5 Replies
View Related
Jun 10, 2013
I want to sort a spreadsheet based on whether any of the numbers '1', '2' or '3' appear in a particular column.
There are 2 particular rows which I care if they have a '1', '2' or '3' in them, let's say row 17 and 18.
I want to create a formula say in row 20 which would tell me if there is a '1', '2' or '3' in the corresponding cells in EITHER (!) row 17 or 18.
An example to clarify:
Cell A17 = 1
Cell A18 = 2
Then Cell A20 would be = 1 because both A17 and A18 have a '1', '2' or '3' .
2nd example:
Cell C17 = 1
Cell C18 = 0
Then Cell C20 would be = 1 because C17 has a '1', '2' or '3' .
3rd example
Cell D17 = 6
Cell D18 = 12
Then Cell D20 would be = "" (Null - no value) because neither D17 nor D18 has a '1', '2' or '3' .
Note: I am looking just for the numbers '1', '2' or '3' not (!) numbers like 11, 12, 13, 21, 22, 23 etc...
View 4 Replies
View Related
May 17, 2007
Sheet 1, column A, has invoice numbers from 1 to whatever is needed for the month.
How can I get Sheeet 2 (the following month’s) to automatically start numbering from wherever Sheet 1 leaves off?
View 3 Replies
View Related
Jul 15, 2008
I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.
--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.
--Is there a function/filter that would be able to report the highest number recorded?
View 10 Replies
View Related
Mar 26, 2013
I am working with some fairly long formulas and I am looking for a way to highlight those cells AND work in the document. I know you can click in the cell and it will highlight them, but if you click off the cell the highlights go away. Is there a way to keep them highlighted?
View 1 Replies
View Related
Dec 1, 2009
Is there a way to reference a cell value instead of using a static value for col_index_num? For example - Instead of telling it to look in the 5th column of Sheet2, I would like it to look in the column were the heading (row 2) in Sheet2 is equal to the value of O2 in Sheet1.
Here's another example -
Normal formula: VLOOKUP(a2,Sheet2,5,FALSE)
What I'd like: VLOOKUP(a2,Sheet2,column where row 2 equals the value in 'Sheet1'!O2, FALSE)
View 7 Replies
View Related
Jul 9, 2014
As i am working on consolidating the Missing numbers manually, whether will it possible for making this automatic.
In Column "A" I have Multiple Duplicate Invoice Numbers and followed by the Different Transcation numbers in Column "B".
In Column "D" I have Each Invoice Numbers and in Column "E" Total Transaction Numbers i.e., no. of times each Invoice have Transactions.
In Column "F" No. of times we received the Transactions.
Here i need to manually need to update each Invoice's Missing Transaction number in Column "H" in single cell with comma separated to each Missing Transaction numbers.
Example:
Before : (Column "A" & "B")
Invoice NumbersTransaction NumbersInvoice NumbersTotal Transaction NumbersReceived Transaction Numbers
A2014000351501 A201400035150 3 2
A2014000351502 A201400035494 13 12
A2014000354941 A201400035881 12 11
A2014000354942 A201400035884 19 17
A2014000354943 A201400035888 14 13
A2014000354944 A201400037613 5 1
A2014000354945
[Code]...
Instead of doing this manually can we have the VBA to auto Update these missing numbers.
Here by i am attaching sample file.
View 1 Replies
View Related
Jan 26, 2006
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies
View Related
Jun 7, 2013
So I have a list of integers in col A that is a truncated list from col B. I want to select the units in col B that do not appear in col A. I tried this statement
=IF(A2=B2,A2,0)
Since both lists (A and B) are ordered from smallest to largest this only worked until a value that is not in ColA appears in ColB and then all the cells following = O.
View 4 Replies
View Related
Jun 29, 2007
I have a column of cells, of which certain values have the prefix "EmployeeID:". I'm trying to identify the cells containing the prefix by evaluating each cell using activecell.offset and value to no avail. I've also tried to use the selection. find, however the first instance of the prefix is continually selected, and does not move on. I'm assuming that I must use the findnext feature, however I'm not having much success.
View 2 Replies
View Related
Aug 1, 2014
I am trying to develop an IF formula based on 2 variables;
1. The value in column Q = 4
2. The value in column AA is between -10% and 10%
When both are true, it should return a value of "YES" (or "NO" when untrue). I am having trouble with the range part. It could be that the range spans negative and positive integers, but I'm not sure. Here is what I've got at the present time:
=IF(AND(OR(Q2=4),OR(AA2<0.10)),"YES",IF(AND(OR(Q2=4),OR(AA2>-0.10)),"YES","NO"))
View 3 Replies
View Related
Apr 17, 2014
I have two tables,
one table (TABLE1) contains the name and a mile point a
the other table (TABLE2) contains name, mile point 1, mile point 2, and other data
I want to be able to pull data from TABLE2, based on TABLE1's name and MP. I want to be able to select data from TABLE2 whose name and mile point's fit with the data from
TABLE1
Line
MP A
SYS
100
8
?
TABLE2
SYS
Line
MP1
MP2
DATA
A
100
0
5
BOB
A
100
5
10
KON
B
100
10
15
B
I want to be able to replace the ? from table1 with the correct SYS from table2 based on the MP given
I started with something like: =if(and(name=name,mpa=>mp1,mpa=<mp2)),vlookup...
The problem is this doesn't work because im comparing one to many.
View 3 Replies
View Related
May 5, 2009
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
View 11 Replies
View Related
Sep 30, 2009
I'm trying to identify duplicates with a return of "True" or "False". The attached workbook has column F for results (to read PI2 A if in column L, PI2 B if in column R or Both), but I want to query if everyone in column E is in column L or is in Column R and if so which one.
View 3 Replies
View Related
Oct 10, 2009
i have a spreadsheet that contains dates spread across different rows and columns. How can I identifying dates for this week and next week by colouring the cells?
example dates that fall within this week will be red, next week will be blue.
View 7 Replies
View Related
Feb 5, 2010
In Sheet1, If I have Group IDs in Column A and values in Column B as below, is it possible to create a summary in Sheet2 where the MIN value for each group ID is returned?
Sheet 1
A B
1 1
1 4
1 5
2 9
2 3
2 7
3 5
3 2
3 6
Sheet 2
A B
1 1
2 3
3 2
View 12 Replies
View Related
Nov 24, 2003
I would like to write a macro to examine a range of cells, and over-write some of those cells with a zero.
I want to over write those cells with either a numerical constant, or a formula with only constants, e.g.
"12345" or "= 123+567-(2*5)"
I do NOT want to over write cells that contain links or any functions:
"=page3!a5" or "=sum(a1:b6"
View 4 Replies
View Related
Mar 15, 2013
Is there a way in Excel to identifying certain combinations of numbers? If tried every IF statement I know.
I have an Excel with two columns of numbers and I need to identify their combinations.
18
18
18
19
18
19
18
20
18
18
18
20
The combinations 18 and 18 would return a "LOW" message in the third column, the combination 18 and 19 would return a message "HIGH" etc.
View 2 Replies
View Related
Sep 3, 2008
I have a list of variables created by a user like this:
BAND ID START END
Band1 06:00 08:59
Band2 09:00 15:59
Band3 16:00 17:59
Band4 18:00 22:30
Band5 22:31 24:29
Band6 24:30 05:59
Seperately I have data from a third party like this:
DATE TIME VALUE
18/05/08 17:22 10
19/05/08 02:15 1
20/05/08 07:14 5
20/05/08 21:36 15
20/05/08 25:43 1
21/05/08 19:15 15
22/05/08 18:26 16
22/05/08 24:59 2
etc.
I need to be able to identify which BAND ID belongs to each TIME in the list so that I can sum the VALUE figures for each BAND ID (e.g. the first time given,17:22, would belong to Band 3). have tried using LOOKUP but I can't seem to get it to work. Additionally, I have a problem with the 30 hour clock. Would I have to stick to a 24 hour clock? This would mean that I would have to find each time from the list that was between 24:00 and 05:59 and allocate it to the previous day because it is important to keep the values that I ultimately derive in the right day of the week.
View 9 Replies
View Related
Nov 13, 2008
Our unique identifiers are from SF.com... they have created unique Account ID's.
Comp Name Unique ID
Company A - 5102AA
Company B - 5102Aa
The problem:
Excel does not recognize the capital A and the lowercase a as being different. I need to pull this data out and bounce data across tables for analytics.
The Question:
Is there any way I can get Excel to recognize 5102AA and 5102Aa as being DIFFERENT ID's?
View 9 Replies
View Related
Nov 25, 2008
I'm using a cells.find command to locate a value in a file. How do I return the current row number that I'm on following the command?
I'm guessing it is something along the lines of:
MyCurrentRow = ActiveCell.RowNumber
but I know that that is an invalid statement.
View 9 Replies
View Related
Feb 10, 2009
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1Container ID26 11 135 8 11 245 7 11 355 7 565 6 9 11 675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Mar 24, 2009
I have a worksheet, where in C3:C100 (or even higher) i will have text strings. A lot of these will be repeated, and i want to pick out just one instance of each text string and display it in E3:E10 (or higher).
So, for example, if i had the following values in column C:One
Two
Two
One
One
Three
Seven
Two
Four
Seven
One
Three
I would want the following values in column E:One
Two
Three
Seven
Four
View 9 Replies
View Related
Aug 6, 2009
Here is the deal I have 4 columns. Each line gives you the following information:
The Id is the record number, Code_Name is a code for each Fox in the study, date and area is a sub area in a bigger grid. Basically I have an area divided by squares and every time a marked fox enters in one of my squares a new line in the data is created. What I won’t to know is if a fox when in my area of study will return to the same squares or not.
ID
CODE_NAME
Date
Area
Logical
116
SSS1
02-Jan-09
1A1
273
RRR1
02-Jan-09
2A2
2959
BBB1
02-Jan-09
1B1
2959
What I’m trying to achieve is a 5th column were ill get a logical value of TRUE or FALSE if, for each fox in the next available record a fox went back to the same square or not.
So if you check for the fox RRR1 I have 2 records one in 2 of January in area 1A1 and a second in 7 of January in the same area. For the Fox BBB1 you will see that she was always in different areas and for CCC1 she only came back to one square.
The problem is I have over 400 fox’s and 12000 records and I’m trying to get a way of doing it automatically.
View 9 Replies
View Related
Jun 20, 2006
When using the formula '= CELL("format",G3)' the result is ',2' for Euros, US and Canadian Dollars. As you can see in my spreadsheet this does not enable me to calculate sums (ie. Total Canadian Dollars) using the currency format. Is there something other than "format" that would get a more precise result that would differentiate between currencies?
View 7 Replies
View Related
Mar 11, 2007
I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this
Dim MyCount As Integer
Dim r1 As Range
'No of rows in Col D
MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count
Set r1 = Range(Cells(MyCount, 4))
The problem is with my line
Set r1 = Range(Cells(MyCount, 4))
View 2 Replies
View Related
Aug 17, 2010
I am about to re write my vba project as I can see that I need to make it more robust. One of the main issues I have is that the source XML file is not controlled by me. The quality and content is consistent but sometimes the order of the columns is different. At the moment that totally throws out my macro as it relies on certain columns for certain functions.
Trying to nut out the best way to import an XML file, keep only the specific columns I want - by content not location so then all follow on formula's will work and reference the correct data.
I have one theory that I can't get going. I thought if using defined names based on column headers this would allow me to keep and sort the columns I want. I can't find any examples to support this. I have been looking at the advanced filter but most examples reference exact cells which isn't flexible and leaves open to wrong data being pulled. Since I can't control the source doc what can I do?
View 1 Replies
View Related
Apr 3, 2014
I realise there are a few threads on similar topics but I can't seem to get any of them to work for my scenario. I have two columns with a list of numbers in each. I'm after a function that will scan column A and identify any numbers that also appear in column B. If possible I would like it to then generate a list of these in say, column C.
View 2 Replies
View Related
Dec 22, 2013
I have a number of workers in one column and how many dvds they have sold for the week in another column. how would I automatically identify the worker from the worker list who has sold the most dvds in that week? is there a formula for this?
View 11 Replies
View Related
