I've tried a search on this, but have not had any luck. On my spreadsheet, Column B contains text split by a forward slash:
I.e.
123/4567
I am trying to write a formula in column L to display the information on the left of the "/" as well as a formula in column M to display the information on the right of the "/". Normally I would just use the Right/Left worksheet functions, however the number could vary from 2-5 digits on each side of the slash.
I am using the below formula to find the latest date (column N) based on fund (column G) and Vendor (column O) reference. How can I find (column I) where the balance of that payment is? I know there are formulas like INDIRECT RC[-1] but how to add them to my formula.
I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.
For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae
A B C 1='Base Case'!A15='Base Case'!D15='Base Case'!G15
I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.
However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.
how to plot Multiple left Y axis display in Multiple draw line graph. Already I have created Right Y axis. I want to draw 4 Line graph in one graph 3 are Left Y axis and one is Right y axis.
I have this formula =TEXT(inventory!I2,"mmm-yyyy") which takes the date from another spreadsheet, but anytime that the date is missing, it susbstitutes for Jan-1900. Is there any way to revise the formula to leave the missing dates blank in the cell? For example, if the date is missing on the other worksheet, in this case, the inventory worksheet, is there any way for a blank to appear, instead of the Jan-1900? Also, anyway for this formula to spell out the month, and not just the abbreviation?
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I have data arranged in cells A1 to Z1 as Name1, Amount1, Name2, Amount2, Name3, Amount3, etc. Is there a formula that can determine the name that corresponds to the maximum amount in the row?
I am using the find or search function but I need to read my text string from right to left (from the end of the string, instead of from the beggining). Is there a way to do this?
In a large database with a large portion of cells that contains a flag to the left of a number "<LD 2.0" what would be the quickest way to move the flag to the left of the number so that it reads "2.0 <LD". This seems like it should be simple to do. I would prefer not to have to use a second cell and formula to do the modification if that makes sense. I'd rather do a "Find and Replace" if possible.
I am attempting to utilize the Mid, Left, and Right functions to parse out data returned in a single cell. There are twelve months of data returned with the "title" listed after. I would like to be able to FIND the title and return a specific number of characters of data to the LEFT not the right (as Mid seems to do).
In a1 i have 100.888 and a2 122.222 and a3 122.555UK,(and so on) in column b i want just want the number and not the uk, i have tried =left(a1,7) which works until a3 then it give me characters 122.555 and not the number/value 122.55. I need it as a value to use the vlookup, how do i do it?
I'm working with a datafeed and basically I have a column with the prices of each product in the same row. What I need to do is take the value in the price column and insert it in a specific spot of a different cell (but still on the row).
A1 = <b></b> B1 = 29.99
How would I get that price information between those two bold tags, and do this for all the rows I have that contain specific price info to that row?
I have a lot of HTML in the line I want to bring the price over to and I have tried the following formula at the beginning cell of A1 ="<b>"&B1&"</b>" but I get an error.
i know the forumla, using left and find to get the value of a cell to the left of the first space. (i.e if the cell said [url] click here then im looking for just [url]
however, when I try and use this in vba I cant get it to "find".
im trying to get the value of a textbox, before the first space
and totally working! but the problem is if the word "ink" in the left of the value --> 253.00 ink and the result is #NA, is there any way that i can get the value whether the word Ink is in the left side or right side of the value?
also bothered why is it if the word is not "ink" in the cell and return -> #value since i put ("") in the last part of If function(value if false)?
HTML Code: If Left(Range("H18"), 7) = A23C567 Or Left(Range("H18"), 7) = A65C321 Then ActiveSheet.Cells(6, 7).Value = "Business and Private Banking" End If
Im trying top use the above bit of code and it does not work when the criteria is Text , so if I simply change the above to =1234567 it works fine -- But I need it to be alpha numeric
I need assistance in shifting cells to left based on three (3) specific text values in column B. There are tons of data and extra lines and stuff but consistently these three (3) text values are consistently not aligned and I simply need to shift entire row 1 space to the left. I can do the long and tedious process of going through them all but is there a macro which I can play that does it instantly...
I am trying to write a bit of code to go into a macro and as I am very much a leaner, I thought I would create the formula I need and record it, then copy in the relevant bit. However, I cannot even get the formula to work - never mind the macro bit!! The formula I am trying to do is something like this...
In column S:S IF the first digit in R2 (and eventually R2 to R lastrow) = 1 to 3, concatenate Q2 (and eventually Q2 to Q lastrow) + "/" + "Q1" IF the first digit in R2 (and eventually R2 to R lastrow) = 4 to 6, concatenate Q2 + "/" + "Q2" IF the first digit in R2 (and eventually R2 to R lastrow) = 7 to 9, concatenate Q2 + "/" + "Q3" IF the first digit in R2 (and eventually R2 to R lastrow) = 10 to 12, concatenate Q2 + "/" + "Q4"
Column R:R has the year and the first digit of Q:Q hold the month so I want to end up with 2007/Q1 etc.
When placing a control on a worksheet, the text in the label,option box etc controls is vertical and I can't find a way of changing the direction to left to right. It doesn't happen on all workbooks. I can put the control on a "normal" workbook and then copy it to the "abnormal" workbook but I would like to know why/how I can change the direction of the text.
Under Tools/Options/International the text direction states left to right
I have to put different ticket numbers for a company with some code. At present, I am manually copying and pasting data (which is too difficult as sometimes, tickets may be more than 300). So, suggest some easy way out, keeping in mind that I am new to Excel. I want this as output.
Company Name= New Horizons Code=CS/02 Number of Tickets=1/300
Here, I want to increment number of tickets automatically like 1/300, 2/300, 3/300 till 300/300 and all the other entities like Company name and code should remain same. My rest of data remains same except number of tickets through out. I also have to print same data.
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like Variable 1 Variable 2 Variable 3
I have columns A and B, and want to add values in column B based on corresponding criteria in column A. The criteria is if the first two characters in the string in column A = 15.
The only way I can think to do this is with LEFT, but LEFT asks for a specific string, and I want to evaluate each cell in a column. So I could write
