Can't Get CONCATENATE To Work. No Results.

Oct 20, 2009

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.

View 11 Replies


ADVERTISEMENT

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

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

View 5 Replies View Related

Why Doesn't CONCATENATE Work In Graph Title

May 24, 2013

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?

View 4 Replies View Related

Find In Table, Then Concatenate Results

Jun 13, 2009

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.

View 9 Replies View Related

Trying To Use VBA To Concatenate And Display Search Results On A Userform

Sep 16, 2009

I'm using a userform to report on set of worksheets. Two of the functions are:

- reporting on the number of contacts with a client
- displaying the details of all contacts with selected client in one textbox.

I can't work out how to concatenate the text of all the contacts relating to one client in a text box (txtContactHist) on the userform.

Using the countIf function I'm trapping zero matches.

The worksheet containing the contacts has the following data columns:

Col A - Customer ID
Col B - Detail of contact
Col C- Date of Contact

Columns B and C contain the data I want to capture

Column A (the customer ID) is the column that is searched for a match ( via the userform text box (txtLic.Value))

To work out the total number of contacts, I use this bit of ...

View 6 Replies View Related

Sorting And Copying Results Into New Work Sheets

Jun 3, 2009

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.

View 6 Replies View Related

Concatenate Multiple Vlookup Results With Dash Exclude Null Values Or 0 Return?

Jun 10, 2014

The 3 vlookup will be in a single cell for concatenation like :

=vlookup1&"-"&vlookup2&"-"&vlookup3

Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.

I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.

View 6 Replies View Related

Best Way To Graphically Show Results Of Time / Work Study

Feb 23, 2012

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.

View 2 Replies View Related

Concatenate Formula Nested Within IF Formula Is Not Producing Expected Results

Aug 24, 2014

I am trying to use a combination of Concatenate and IF formula to produce an email.

My input.

D4: First Name:
D5: Middle Name:
D6: Last Name:

D8: Organization:

Once all these are filled, I want the formula to produce a result like

FirstName.M.LastName@Organization.com

I have used the following formula.

[Code] .....

Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.

FirstName..LastName@Organization.com

How do I remove the additional (.) in cases where there is no middle name.

Attached File : Email Generator.xlsx‎

View 3 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

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 ,

View 9 Replies View Related

Work Out A Formula For My Spreadsheet Which I Use To Work Out Cutting Lists For Timber Frames

Jan 11, 2009

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.

View 4 Replies View Related

Work Accurately With Times To Calculate The Work Progress Of The People In The Workshop

Mar 3, 2009

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.

View 9 Replies View Related

How To Get A Macro To Work With Any Work Book Name

Aug 24, 2009

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

i have attached the code in notepad ...

View 8 Replies View Related

Make To Display (0), Instead Of (-0,02) When I Just Do Other Work Than Cad Work

Feb 13, 2010

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.

View 2 Replies View Related

Results Show Results Of Formula, But Should Be Blank

Jul 27, 2006

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.

View 2 Replies View Related

Copying A Certain Range In A Work Sheet To Another Existing Work Sheet Using VBA

Dec 29, 2009

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 .

View 9 Replies View Related

Copy A Formula Across Several Work Sheet And Have The Formula Always Take Data From Previous Work Sheet

Jan 2, 2009

I am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.

2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")

and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations

View 2 Replies View Related

Change Formula To The Results Of The Formula For Cells That Have Results

May 9, 2006

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.

example attached

View 9 Replies View Related

Opposite Of Concatenate

Sep 29, 2006

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.

View 3 Replies View Related

Concatenate (two Different Cells Into One)

Dec 11, 2008

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.

A picture is worth a thousand..

View 4 Replies View Related

Concatenate The Dropdown

Jun 19, 2009

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.

View 7 Replies View Related

Increment Using Concatenate

Sep 2, 2009

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.

View 2 Replies View Related

Variable With Concatenate

Jan 2, 2013

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 ??

View 3 Replies View Related

Automatic Concatenate

Jul 9, 2009

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".

View 7 Replies View Related

VBA Concatenate Formula

Jul 25, 2009

I'm trying to use a macro to write a formula within a column of data.

Here's the

View 3 Replies View Related

Concatenate 2 Column

Feb 2, 2010

i trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc

with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?

View 4 Replies View Related

CONCATENATE Not Working

Nov 2, 2006

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.

View 9 Replies View Related

If / Concatenate Into One Cell

Nov 14, 2008

I have 6 columns:

A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2

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 "".

View 4 Replies View Related

Concatenate Function..

Jan 23, 2010

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

View 2 Replies View Related

Concatenate Returning Zero

Dec 20, 2011

I have this code

Code:
Selection.FormulaR1C1Local = "=INDIRECT(CONCATENATE(""["",R4C26,""]Sheet1!"",RC))"

Which creates this in the excel cell:
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!",D4))

But for some reason, it returns a 0. However, when I change the code in the excel cell to this
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!D4"))

It works. But I need to be able to change D4 in the macro, so doing it that way won't work for me.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved