Conditional Concatenate Function: References ConcatenateIf Displays "#NAME?"

May 17, 2008

I have two tabs that reference the function ConcatenateIf(), defined by me, see below:

Function ConcatenateIf(iRange As Range, iLook As String, iNum As Integer)
For Each cell In iRange
If cell.Value <> iLook Then
ConcatenateIf = ConcatenateIf & Chr$(10) & cell.Offset(0, iNum).Value
End If
Next cell
End Function

Works great. But then, I copy one of the tabs to make a third tab, and suddenly every cell in all three spreadsheets that references ConcatenateIf displays "#NAME?".

View 3 Replies


ADVERTISEMENT

Conditional Formatting: Calculates & Displays The Number Of Minutes Between Two Cells

Feb 11, 2010

I have a column that calculates & displays the number of minutes between two cells, i would like to conditonal format the column to either display a 2-color scale by number, min 0 and max 40 or i would like to apply a fill to all cells > 40. I can do this with normal numbers typed into the cells but i cannot get it to work with the result of the formula. I have tried formating the cells to number but still i cannot get it to work.

View 3 Replies View Related

Function That Displays Color Based On Outcome?

Apr 10, 2012

If the percent in a cell is more than 10% less than another cell I would like it to turn red. I would like the cell to turn yellow if the percent in that cell is within 10% below the percent in another cell, or if the percent is greater than that of another cell I would like the cell to turn green. How do I do this?

To clarify; If cell a1 is 20% and Cell a2 is 21%, I would like the color in cell a1 to turn yellow since it is less than 10% below cell a2. OR if cell a1 is 25% I would like it to turn green since it is greater than cell a2. OR if cell a1 is 10% I would like it to turn red since it is more than 10% below cell a2.

I've looked at conditional formatting and it does not seem to apply, this seems like it would need an if function combined with conditional formatting.

View 3 Replies View Related

Conditional Formatting With References

Feb 8, 2014

I have 2 sheets. Sheet1 looks like this:

B
D
A

[Code]....

Note: The "X"s are actually not in the cells, they are for illustration purposes only. Instead of the "X"s, I want to fill the cells marked with X with the color red. The cells in sheet2 have no value except row 1 and column 1.

I am looking for the formula to fill into the conditonal formatting field for sheet2.

The formula should detect how many letters there are in row 1 of sheet2 (for example here we have 4, namely A, B, C and D). If I add a new column E later, I just want to re-apply the formula to those new cells, not edit the formula itself. If there is a new column E, it will also be added on sheet1, but not necessaryly to the right side of the column with the header "A", maybe it will be added between "B" and "D".

The number of rows will not change, they stay static at 1-5

Here is the underlying task.

Sheet2: A column that has all days of the year, from Jan 01 to Dec 31, from top to bottom, starting in A2 going down to a366. In column B, C, D and so on, in row 1, I write the names of different countries. Sheet1: In row 1 I write the same country names as in sheet2, but not in the same order. Below the country names I list the official pulic holidays of the whole year.

I need the formula for conditional formatting that will color the intersecting cells of Country names and days of the year. And I need the formuly to be dynamic so that I can add more countries later without have to adapt the formula for new columns manually.

So far I have the formula to count the filled cells in row 1 on sheet1. I also have the formula to find the country name in sheet2 and return the column number of the same country in sheet1. Last but not least, I can make this work with a static formula, but replacing the static pieces with the dynamic pieces mentioned above just won't format the cells as desired.

View 1 Replies View Related

Conditional Formatting With Multiple References

Jul 18, 2014

I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.

What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.

My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.

View 1 Replies View Related

External Worksheet References Conditional Sum

Apr 17, 2007

I am trying to use a conditional sum formula where the data resides in and external workbook. This is fine until I close the external workbook and the formula no longer shows the external reference. Is there a way of getting excel to use conditional sums in this way (or what is the best way of achieving this?)

View 4 Replies View Related

Conditional Formatting With R1C1 References

Aug 19, 2007

Does anyone know if it's possible to use R1C1 style references in conditional formatting formulas?

Eg., =R[-1]C > 1

I tried the above and I keep getting an error.

View 9 Replies View Related

Conditional Formatting That References Two Separate Conditions

Aug 15, 2008

I am trying to use conditional formatting in Excel 2003 to indicate when certain tasks are overdue (by highlighting the cell in red). My problem is that I have multiple criteria. If my line item is a "priority" item, it is considered aged if not resolved after 2 days; if the line item is "routine", the item is not considered aged until after two weeks (14 days). My goal is:

1) I would like the "status" cell in column D (which says "open" or "closed") to turn red if the item is aged.

2) Aging depends on the "priority" status in column A .......

View 9 Replies View Related

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.

View 3 Replies View Related

Color Scale Conditional Formatting With Relative References

Oct 7, 2013

I'm having trouble with Color Scales within Conditional Formatting. I have a data set of commodity prices. In column A I have the name of the commodity, in column B I have the standard deviation of the price change of the commodity, and in Columns C-N I have the monthly % change in the commodity price. I want to conditionally format with Color Scales each row of price changes within Columns C-N based on each commodity's standard deviation (column B). If the price change is a one standard deviation or more decrease, I want the cell to be dark red; if the price change is less than a one standard deviation decrease, I want the cell to be a gradient of light red; if the price change is a one standard deviation or more increase, I want the cell to be dark blue; if the standard deviation is less than a one standard deviation decrease, I want the cell to be a gradient of light blue; and if the price change is 0, then I want the cell to be white.

I can achieve this perfectly by manually doing 3-Color Scale Conditional Formatting for each row, but it's very time-consuming. And Excel doesn't allow me to enter relative cell references when I'm doing the Color Scale Formatting. Is there a quick way that I can do this so that each row is color formatted differently?

I've attached an example file (there are many more rows within the original file) and formatted the first several rows manually as I want the final product to look.

example.xlsx‎

View 3 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

Set Cell References In GETPIVOTDATA Function

Aug 30, 2007

Is it possible to replace the Pivit table name/reference in a GETPIVOT function with a cell/range reference? I can on other components of the function call, but not that. For example ... the "hardwired" function call might be:

=GETPIVOTDATA(" Sum of 1991",'[EE_financial data.xls]Sheet1'!$A$3,"Company","ERG","Item"," Depreciation & Amortisation")

I can achieve the same result by externalising the 1991 and ERG, so i replace this with other dates or names, thus:

ERG
1991=GETPIVOTDATA("Sum of "&C13,'[EE_financial data.xls]Sheet1'!$A$3,"Company",D12,"Item"," Depreciation & Amortisation")

.. and this works fine.

However, if I try and replace the '[EE_financial data.xls]Sheet1'!$A$3 with a reference to a cell containing that string, it returns #REF!

View 3 Replies View Related

Average Function Using Cells As Row References

Jun 18, 2008

If I have numeric values in a cell examples c5 = 126, and d5 = 135. How do I place these values in c5 &d5 in another cell as a cell reference within the formula ex: =Average(f126:f135)

View 3 Replies View Related

Copy Non Absolute Cell References In Conditional Formatting Formula?

Dec 3, 2012

I have this fairly simple formula which decides whether to shade a cell or not

=AND($X$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.

View 12 Replies View Related

Array Concatenate Function VBA

Aug 8, 2012

I use a existing code and want to add the Concatenate in the code.

VB:
Sub Subtotal()

Dim myAreas As Areas, myArea As Range, x As String
Dim y As Long

Worksheets("INVOICE_hulp").Activate
Range("Q1").Select

[Code] ....

The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?

View 9 Replies View Related

Using Function To Concatenate Cells

Jan 29, 2009

I have data in cells A1:A50. I want to combine all of them into cell A51. I know that I can achieve that by using =concatenate(A1,A2,A3,A4,........,A50) or =A1&A2&A3&A4&......&A50 but it would be very tiring to click on each cell. Just imagine if the data in cells A1:A1000? function that work like say =combine(A1:A50).

View 2 Replies View Related

CONCATENATE Function (dropping The 0 From B2)

Feb 16, 2010

I am using the function =CONCATENATE(A2,B2,C2)
Why is it dropping the 0 from B2?
Table 1 Table 2 Table 3
C 012 A
C12A

View 2 Replies View Related

Vlookup But Do Not Want To Use Concatenate Function

Mar 28, 2007

Trying to do Vlookup with If but dont know how , I can do the concatenate function and then Vlookup as in Sheet 3 but that is too much of load to deliver in quick time specially i have pull data in dynamic and continuous update.

View 14 Replies View Related

Concatenate TODAY() Function

Feb 23, 2010

I want a report header to be similar to: REPORT AS OF 2/23/10. If I use the TODAY() function by itself, I get the date; 2/23/10. But when I concatenate it with the text "REPORT AS OF " & TODAY()" I get REPORT AS OF 40232". How do I preserve the date format when I concatenate it with text?

View 2 Replies View Related

Function Which References Active Or Selected Cell

Oct 22, 2008

I have a spreadsheet which shows a matrix in pounds. But I would like to have a reference cell somewhere near the top which will automatically show the conversion to kilos of any selected cell. Is anyone aware of a function which will do this?

For example, one pound equals 0.45359237 kilos.
Cell A1 contains the reference function to the active of selected cell. If I click on B1 which contains 12, cell A1 would automatically show 5.45. If I click on cell C1 which contains 20, cell A1 would automatically show 9.09.

So I’m thinking it would be something like
=SelectedCell/0.45359237 or maybe =ActiveCell/0.45359237

View 2 Replies View Related

Trying To Insert Nested Concatenate Function In VBA

Nov 7, 2012

I am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T

he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is

=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).

VB:
Dim iInput_Rows As Integer
iInput_Rows = Worksheets("Workspace").Range("D3").Value
Dim iOutput_Rows As Integer
iOutput_Rows = Worksheets("Workspace").Range("D1").Value
Dim iAnalog_Rows As Integer

[Code] ....

My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?

VB:
Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"

View 2 Replies View Related

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

View 3 Replies View Related

Function Inside Concatenate Funcion

Dec 18, 2007

how i get this to work?

B4 = BMEF3

function:
=CONCATENAR("=TZMS|";B4;"!VAR")

and this its what should do:
=TZMS|BMEF3!VAR

how im supposed to do that function work?

View 14 Replies View Related

Carriage Return In A CONCATENATE Function

Jan 14, 2009

I try to concatenate some 5 text strings and I would like to have a carriage return after each text string (ALT ENTER). How can I achieve this using =CONCATENATE(......;......;......;......;......)?

View 4 Replies View Related

Concatenate Function To Merge Certain Columns

Aug 14, 2009

I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.

View 5 Replies View Related

Creating Unique ID Using Concatenate Function

Mar 26, 2014

I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.

View 3 Replies View Related

Function To Concatenate Cell Values

Jan 10, 2012

I need to concatenate all cells filled out in a column (for example A in sheet1) in a cell in sheet2. More exactly.

column A (Sheet1) (Sheet2)
aaaa cell(2,2)=aaaabbbbcccc
bbbb
cccc

If I use a VBA macro I can write this VBA code

RowsNumber = Application.CountA(Sheet1.Range("A:A"))

Then loop from 1 to RowsNumber and concatenate values in cell(2,2) but I need, if this is possible, to define a FUNCTION (nested functions) in cell(2,2).

View 2 Replies View Related

Excel VBA - Use Concatenate Function To Show A Value

Jul 12, 2013

Possible to use concatenate function to enter a formula to a cell and have the cell show the result?

I enter the following in a cell:

=CONCATENATE("=LEN(""",A2,"""",")") 'A2 contains the string "Exchange rate: 925"

what is shown in the cell is:

=LEN("Exchange rate: 925")

If I manually enter the above len function in the next cell, the cell displays 18.

Is it possible to use the concatenate function, maybe combine with other functions, to display 18 in a cell?

View 5 Replies View Related

Underline Word In Concatenate Function?

Apr 22, 2014

each cell contains one word say hello in a1 and Henry in b1. Is it possible to underline Henry after =concatenate(a1,b1) ?

View 4 Replies View Related

Concatenate Function- Two Texts In Two Cells

Aug 1, 2008

I have two texts in two cells. e.g in A1 I have JOHN, and in B1 I have SMITH.

I need to cocatenate these two texts in a third cell (=CONCATENATE(A1, B1) /or I can use = A1 & " " & B1). It's fine till I concatenate.

But I need the output in the following format:

JOHN SMITH

The second text needs to be in ITALICS.

View 14 Replies View Related







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