Concatenate Worksheet Names For Formula

Oct 19, 2007

I want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.

Conditional Formula To Concatenate Names

Sep 21, 2009

I'm looking for a formula that utilizes an "if/then" condition. I'm attaching a sample workbook of what I'm dealing with. I need it to end up so that column "E" holds: if a equals c, then e = b, "and", d, c Does that make sense? I manually entered my desired end result into column E in the sample workbook.

Formula To Return Worksheet Names

Sep 19, 2006

Without resorting to macros, I need a formula that returns a worksheet name.

Ideally, I want to have a list that contains each sheet name in the workbook.

Concatenate Column Names From Pivot Table

Apr 11, 2013

Pivot Table. I have a large data set and have used a pivot table to summarise the COUNT of each row (Objects) for each Column Name (that is, I did what Pivot Tables do).

What I'd like to do in a new column is show a string of all the column names per row name.

I have a data set that looks like this:

Sheet3

A
B
C

1
Object
Description
Class

[Code]...

So I can pivot table to work with other analysis, but what I'd really like to get is a list of unique "Objects" (easy) with all the related "Class"es listed in column (or straight to concatenated). I can concatenate using Chip Pearson's StringConcat.

Here is an example:

Sheet3

F
G
H
I
J
K

1
Object
Countif

[Code]..

You can see Chip's Function in col K. What I'd like to happen is in Cols H to J be automatically populated. (For this example, I have typed in what I'd like the result to look like)

I considered using Vlookups (with helper column for 2nd & successive instances), INDEX/MATCH with SMALL to get 2nd+ instances and even lengthy IF statements. The main problem with IF is that in the real data set, I can have over 30 Classes.

I looked at Pivot table to see what I could do but haven't much experience in manipulating past regular feature.

I also took a cut&paste of the pivot table (as shown in part below) to see what I could do.

Here is a clip straight from the Pivot Table:

Pivot

A
B
C
D
E
F
G
H
I
J

[Code]..

And one from the cut&paste of the pivot table, which also shows the desired outcome in the right hand column:

Sheet2

A
B
C
D
E
F
G

[Code]...

I would also be fine using VBA, but its been a while since I did any basic coding so I wouldn't really stand a chance of starting from scratch.

Excel 2007 :: Automatically Change The Worksheet Tab Names With Cell Value In Each Worksheet?

Feb 14, 2012

I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?

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‎

Renaming Worksheet Names From Data In Worksheet

Mar 26, 2007

I use a web query to import into an excel workbook. As data is changed on the web that the query runs against, I would like the name of the sheet that is being imported into to change to one of the cells that is being imported.

Search Column Concatenate Row And Place In New Worksheet?

May 30, 2014

In Column A, anytime a specific "text" appears, I want to concatenate the information in the row, and place it into worksheet Data-list under a specific heading.

[URL]

The formula I would use (not vba) is =IF(A3="FN",B3,""), then I just copy it all the way down. However there are 27K cells, which is why I want a VBA Code

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

Concatenate The Number Into A Formula

Oct 14, 2008

I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?

E2 is 134

=CONCATENATE("=VLOOKUP(\$B2,\$C\$", E2+1, ":\$F\$2571,3,FALSE)")

returns: =VLOOKUP(\$B2,!\$C\$135:\$F\$2571,3,FALSE) but not as a formula, as text and I need the formula...

Short Formula Instead Of Using Of CONCATENATE?

Apr 19, 2014

I am using CONCATENATE formula to make summary of my data, but i m using long CONCATENATE formula. I want to use short formula instead of CONCATENATE

formula is here :

[Code]....

How to short this formula and accuracy is first ...

How To Use CONCATENATE And SUBSTITUTE Formula Together

Nov 17, 2011

Is this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet

PHONE

WIRE
CANDY
INDIA

When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this

PHONE WIRE CANDY INDIA

I want it to look like this, removing any blank lines within cell A1

PHONE
WIRE
CANDY
INDIA

Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")

Concatenate Formula Amendment

Oct 14, 2009

I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.
Is this possible? I've tried to use the formula below with no luck,

=Concatenate(A1:GR1)

Make Concatenate A Formula

Feb 24, 2010

I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:

=NeoTicker|q!'@ES#,last'

I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:

G10: '=NeoTicker|q!'
H10: '@ES#

Concatenate Formula For Numbes (% And \$)

Aug 7, 2006

I have been able to create concatenate formulas for numbes (% and \$), but have been unable to create one that will display a date, I. E. for the number formula, I used, =(A8&" "&TEXT(B8,"\$0")). What would be the formual so the second cell show the value as a date?

Concatenate Strings In A Formula

Aug 10, 2007

I am writing a macroI have three variables named "name", " book", and "count". In previous statements these variables have all been defined as String, String, Integer respectively. I am trying to uses all of these variables in the same formula and am encountering issues. The formula I wish to use it in is:

ActiveCell.FormulaR1C1 = "=COUNT('[" & book & "]" & name & "!'""B6:B" & count & ")"

Where if book = Book1 ; name = "Sheet1" ; and Count = 100 I would want the following result: "=COUNT('[Book1]Sheet1!B6:B100)"

Concatenate Formula With If And Text Function?

May 20, 2014

I have a time in cell C10 in hh:mm format, in cell D10 I want to add the text "DFL" in front of the time, remove the ":" and add "HRS." at the end of it. For example "DFL0715HRS." I have been able to get the following formula to work:

=CONCATENATE("DFL",(TEXT(C10,"hhmm")),"HRS.")

However sometimes it may not be a time, it could also be "Rest Day" in cell C10, if it says "Rest Day" I need it to add the text "DFL" in front as before and then change "Rest Day" to "RDFLEXI." For example "DFLRDFLEXI." I have been able to get the following formula to work but only if it is "Rest Day", if it is a time such as 07:15 it doesn't work.

It shows "DFL07157R0.": =CONCATENATE("DFL",IF(C10="Rest Day","RDFLEXI.",(TEXT(C10,"hhmm"&"HRS."))))

I think it is trying to continue doing something with the text function

Concatenate Formula (merge Cells)

Oct 14, 2009

I am trying to merge cells A1:GR1 in to one cell by using the concatenate formula and the copying and pasting the values in to the same cell.

Concatenate To Create A Linking Formula

Jan 24, 2006

I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)

In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.

CONCATENATE Formula To Put All The Text On Different Lines?

Jul 21, 2006

Is there a way to make the CONCATENATE formula put all the text on different lines? It keeps combining some on the same lines and I want them all on separate - like a list.

Format TODAY() Within A CONCATENATE Formula

Nov 28, 2008

Looking for a little help with the following formula =CONCATENATE("August 07 -",TODAY()," Evening Instructor") After entering this formula today is displayed as a serial number. I'd like it to display as current month/current day/current year (11/28/08)

Formula To Concatenate Cells With Data

Feb 6, 2009

Is there a way to nest IF & And statements. I'm looking to concatenate a number of cells and seperate them with a space and/or comma but only if they contain data so need something along the lines of

Concatenate Cells To Create Formula

Jan 17, 2007

way of joinng the contents of cells to create a formula?

path month subdept
x:path oct [dept.xls]sheet1!a1
x:path dec [dept.xls]sheet1!a1

is there any way of joining these to create a cell reference such as x:path oct[dept.xls]sheet1!a1

i've tried the concatenate and "& &" method to no avail.

Concatenate- Combine All The P.O# With A Formula Automatically

Mar 13, 2009

Is there a faster way to combine all the P.O# with a formula automatically, no matter how many P.O# there are? Its unpredictable on how many invoices will get pay per wire.

Log # Amount P.O. Numbers 1-a 45,678 1234 1-b 89,173 5678 1-c 66,526 9123 2 22,113 2345 3-a 66,654 6789 3-b 88,650 2468

1 201,377 1234/5678/9123 2 22,113 2345 3 155,304 6789/2468

Refering From Another Workbook By Using The Concatenate Formula

Aug 2, 2006

I would like to reference information from another workbook. Instead of writing the Name of the other workbook I would like to reference through a Concatenate formula (e.g. CONCATENATE("Test"&G2&".xls")). I always get an error message. Is there any way I can reference from another workbook by using the Concatenate or any other formula?

Worksheet Tab Names

Dec 23, 2008

Is there a way of linking the worksheet tab names to a cell in the spreadsheet

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

Concatenate Formula To Combine Two Cells Of Data

Sep 8, 2012

I am attempting to use concatenate to combine two cells of data, but to only display the information if both cells contain information.

Currently using the following simple formula:
=concatenate(a1,a2)

However, I only want it to work if both a1 and a2 cells contain data. If only a1 contains information, but a2 is blank, I want it to calculate a blank cell.

Concatenate Multiple Cells With Array Formula

Jan 21, 2009

If I have the following data set in cells A1:C9:

IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE

In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):

=COUNTIF(A\$2:A2,A2)=1

In cell E2, I have this formula to count unique occurrences:

=SUMPRODUCT(C2:C9*1)

In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):

=IF(ROWS(G\$2:G2)

Concatenate Formula Referring To Cell In Another Workbook

Feb 9, 2007

"Smith B" is text in C1.

My current formula in C3 is a simple reference to a cell in another workbook:

=+'Z:Time Records2007[Smith B.xls]Daily Time'!\$C1352

I would the formula to refer to C1 to get the "Smith B" part.

This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.

To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?

=+'Z:Time Records2007["C1".xls]Daily Time'!\$C1352