Reference Cell & Only Return X Characters
Oct 20, 2006
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.
View 2 Replies
ADVERTISEMENT
Nov 3, 2009
I've got a worksheet here that i need to update every week. The problem i'm having is that i have a table of values that's drawing values in from another larger table. For example in B9 the cell is referenceing the I2 cell in another sheet. When i pull down the formula in B9 i want the cell below to reference J2 and so on. In other words, I only want the alpha characters to increment. Tried messing around with dollar signs but it doesn't seem to be doing what i want.
View 2 Replies
View Related
Jun 18, 2013
I am currently using cell references to create a depedendent list with data validation. How to make cell references show up with some invalid characters?
For example, one of my cell references is Youngs_Market_Company. However, when pulling up the cell, I want it to be displayed as Young's Market Company.
View 2 Replies
View Related
Dec 12, 2012
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 Related
Jun 15, 2013
In sheet2 I've many numeric value.
In sheet1, A1: =Sheet2!B12 which returns 0 (zero) though B12 of sheet2 is the result of =sum(B1:B11) i.e 660. But if in sheet1 A1=Sheet2!D12 or any other cell in sheet2 instead of B12, A1 gives correct result. That means cell A1 itself is not a problem. I checked format of B12 which is number format.
View 5 Replies
View Related
Nov 11, 2008
i am trying to write some code to analyse a weightlifting movement that occurs 3 times. In particular there is a part of the macro where the user will input the start and end time of the movement using input boxes (to only select relevant data).
I want to then use a vlookup function to search for the start and end times in a range (1 column) in the time range and return the cell reference of these so i can select only these values and either create a graph or do more analysis. My code for this particular part so far looks like:
View 3 Replies
View Related
Jun 16, 2006
I have a column of data in column and a column of data in column F. The data in column F is exactly the same as column C but in a different order. Is there a way to match column C to Column F and return the matching cell references for column C & F in column G & H? i.e. If the word Help appears in C27 and F40, match the two words and return C27 in G27 AND F40 IN H27. All of the values are unique (I hope).
View 2 Replies
View Related
Jul 23, 2008
I am creating a summary financial sheet in Excel 2003, Win XP. It needs to show the top 10 sales variances from a large data list, along with the associated department.
I can bring in the top 10 largest variances using Large(array,k), but because variances are either positive or negative, I'm not really showing the true top 10. To fix this I can use absolute values via Large(abs(array),k) but then I don't know how to convert the value back to its original sign (positive or negative).
If I could somehow get the cell reference that the Large(abs(array),k) formula points to, I could do something like this:
=Large(abs(array),k) * (Large Cell Reference/Large Cell Reference)
I'm afraid to use the address function because of duplicate sales variances. I often have several variances of with same value, so I might not truly be pulling the address that the large(abs(array),k) is pointing to.
View 9 Replies
View Related
Nov 2, 2012
What is the best way to reference a cell in another workbook and return the cell contents and the comment on that cell. I would like the comment to come across as a comment in the new workbook becuase the comment is actually a picture.
Hope this makes sense. I did find a macro through googling but I couldn't get it to work? I don't really want to copy and paste because eventually I have hundreds of sheets & thousands of cells to refer to.?
View 2 Replies
View Related
Dec 13, 2006
i just want to use vlookup and hlookup to give me the row and column headings for 30 to 40 sesor at a time.... but i keep getting #n/a whenever the functions meet a table of more than one column! (reduced workbook attached)
it should surely be simple to get this data - but i've struggled to no avail. I thought that having the four separate worksheets was the problem - but i haven't had any look even when i dump the data into a single worksheet!
i basically just want excel to return the cell reference of a sensor number which exists in a table. sometimes a sensor can appear more than once, but its not very common and i could happily work around that by doing the manual search (ctrl f, find all).
any advice would be very much appreciated, i'm struggling and the number of sensors i need to test will increase in the coming months.. please help!
should i even be using vlookup and hlookup? ive tried all the other excel functions, but they don't seem to be useful?
View 8 Replies
View Related
Apr 1, 2014
I want to find the cells 30 past the reference cell and the corresponding value:
i.e. =Sheet1!D312 to =Sheet1!D342 (=Sheet1!D(312+30))
or
=Sheet1!D312 to =Sheet1!E312
is there a way to automate this without having to manually edit each formula?
View 8 Replies
View Related
Apr 5, 2008
I have a formula that searches through a couple columns, and based on some criteria on those columns, it returns a value. The criteria looks at the dates in a given week, finds the earliest date, and then the largest value for all entries of the same date. So, I do not know what this vale is going to be in advance. In any case, Let's call this value1. This is all working fine!
Now, what I need to do is grab the value in an adjacent column to the left(same row) and add that to value1. Let's call this adjacent column value, value2. I was thinking that I could use the OFFSET function in this way, OFFSET(cell reference of value1, 0, -1) to return value2.
But this requires that I can get a cell reference for Value1. I cannot find out how to get a cell reference. I saw some VBA code to search and return cell references, but it assumed that you know what value you are looking for, and I do not know that in advance.
View 9 Replies
View Related
Jun 30, 2007
the formula which will return the cell reference of the cell that contains a specified value
eg
if column A has values 1-10 in order, b1 has the value 5, then what formula can i input in c1 that will give me the cell reference in the range in column A which contains the value in column B? (the result of the formula in c1 would be A5 in this example)
View 4 Replies
View Related
Oct 1, 2011
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
View 2 Replies
View Related
Nov 9, 2006
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.
View 4 Replies
View Related
Jan 26, 2010
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 Related
Jun 25, 2009
Excel 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
View 9 Replies
View Related
Nov 1, 2009
If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says. So, on Sheet1, I might have this:
View 2 Replies
View Related
Sep 14, 2007
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.
View 8 Replies
View Related
Apr 8, 2008
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 Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
Jul 21, 2014
NumberABC D Value to be returned
1 -10050 0 C
2 156.6700-31.34A
3 104.5800-20.92A
4 -6.2000 #NA
5 0 -3500 #NA
6 132.800-33.2 A
7 0 1000-50 B
What formula i need to apply so that i get values which are in column "Value to be returned"
View 6 Replies
View Related
Mar 3, 2014
I'm trying to lookup two dates in a column in order to provide the start and end point of an array for use in the PERCENTILE.INC function.
I can't quite get it working, my base idea is something like;
PERCENTILE.INC(INDEX(A1:A10,MATCH(C1,B1:B10,0))&":"&INDEX(A1:A10,MATCH(D1,B1:B10,0)),0.95)
So this looks for the dates found in C1 and D1, finds them in B1:B10, and returns the corresponding cells in A1:A10 for the array part of the PERCENTILE syntax.
I think the issue is that the lookup will return the value in A1:A10 rather than using the cell reference for the PERCENTILE.INC function...
View 4 Replies
View Related
Feb 16, 2008
A common task for me is having to manually update the series formulas on charts to conform to some predefined conditions as the data changes. To reduce the strain of this, I've developed a VBA module that will read the various series A1-style formulas of the chart and update them according the the specific conditions. This works wonders on most charts, but I've suddenly run into a situation where Series.Formula property returns ONLY the R1C1 style. That is, where my series formula looks like this in the chart sheet formula bar: ...
View 6 Replies
View Related
Jan 24, 2008
I need to build a formular in a cell that uses various columns in it's row such as: =A2+A4+A17+A24. In the past I've used simple number references to the columns (a is 1, b is 2,.....) I can't do tht though if I'm using a macro to automate the creation of a formula, right?
Is there a property I'm missing that will return the letter reference to the column instead of just the number as Activecell.column does?
My alternative is to write my own code to do this, but you know the bit about not reinventing the wheel....
View 9 Replies
View Related
Aug 1, 2014
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So:
Charles Johnson -> Johnscha
John Smith -> Smithjoh
Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
View 4 Replies
View Related
Mar 11, 2009
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Jul 20, 2014
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
View 2 Replies
View Related
Apr 25, 2014
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies
View Related