I have built a formula using CONCATENATE. (Built in G2) When I copy and paste it into a empty cell (E2), one that is formatted as General and is not locked, as text or value or a formula I get the same results. It always shows up as text. I can't get the formula to function and show the results in the cell (E2). Yet, when I singularly build the formula in the cell it works fine. But the moving (cut and paste) from one cell to the other doesn't work.
The concatenated formula is: =CONCATENATE("=LEFT(",G2,",",H2,")") The result is: =LEFT(B2,3) . These results look fine, yet only when built in the cell using Excel's Insert Function builder or doing it by hand it works fine. It returns the first 3 numbers/text from the left side of cell B2. But the data that should result from the concatenated formula and pasted in to the cell (E2) is not showing up as needed. Any help would be appreciated.
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC ______Loc__PN 1,2____1___A _______2___A _______3___B 4,5____4___C _______5___C
So, I'm trying to come up with a dynamic graph title. I highlight the graph title, go to the formula bar, and type:
Code: =CONCATENATE(Sheet1!$C$1," All Servers")
"That function is not valid". Yet, the same function, placed in a cell, works perfectly. I suppose I could use an intermediate cell to hold the concatenated text and point the graph title to that cell, but I dislike having contextless cells like that floating around. Is there a way to apply that formula, or some other with the same effect, directly to the graph title?
My data is pulled in as a table from QuickBooks, so its many rows consisting of many column entries per row. The columns are laid out like:
DUEDATE, VENDOR, ISPAID, PONUMBER, AMOUNT
I currently have a SUMIFS formula that works well for searching the table and finding PO's that are not paid, for a specific vendor, on a day today or before, and it ads the amounts together and returns the sum opf the amounts for me.
Now, what I would also like to do is to have it return the PO numbers as a concatenated text string all into one cell. So, it shoudl find all the rows where the due date is today or before, the PO has not been paid, and the vendor is a specific vendor, and then concatenate all of the PO numbers into one cell of my choosing.
This sounds just like the SUMIFS function, however, the SUMIFS function adds the values and places the sum in the cell of my choosing. For this, I would like the values found (which are text values) to be concatenated into one cell.
I have a work sheet with about 35,000 lines of data. Every day I have to sort the list by product category (I use auto filter to separate) and then copy the results into a new worksheet, there are about 300 product catagories. I'm new to macros but I'm wondering if there is a macro that can automate this process.
I am trying to determine the best way to graphically show the results of a time/work study. Basically i have a list of tasks and each list has 2 sets of numbers assigned to it. One for how much time it takes on a scale of 1-3 and a second for how much value is added for the task, again on a scale of 1-3. I want to show some sort of graph/visual that shows the number of tasks as clusters (not name of task) in maybe quadrants. With the time on X axis and value on Y axis. I am envisioning something like a 9 box that would have low/low to high/high. I can only think to create a 9 box in excel and physically put circles(clusters) in each quadrant.
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four one , two , three one , two one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ") [ returna 2 commad ] one, two, three, four one, two, three, one, two one ,
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
I have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.
I'm having a rather difficult time getting this one.
I am concatenating (is that a verb?) a bunch of cells to create an item code. The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.
I am trying to get 2 rows in one drop down without having to create another combined row. im not sure what formula should i use. I have attached an example.
I want to write formula in cells(many cells!) using concatenate. The desired output:
In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6 in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7 in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F
Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.
I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:
1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8
I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008
Easy enough! Now adding the following is the problem:
1. Cell A2= 12345 Cell B2= 67 Cell C2= 89
I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089
Now D2 has one too many characters from C2
I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.
Is there a way to use a variable with the concatenate or am I using the wrong formula period ??
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".
Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.
If I enter =CONCATENATE(A2,B2) instead of showing the strings located in A2 and B2 it displays =CONCATENATE(A2,B2) in the cell.
I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.
I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).
So here was my attempt at a formula: =IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")
Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".
What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com
