Value Of Cell Based On Concatenated Values

Jun 19, 2014

I have charts that I have to update at work, and I want to make it as fast of a process as possible. Here is an example of what I want to do.

I want to put the letter B in cell 1, then the number 23 in cell 2. I then want to concatenate those two strings, which I can do. I end up with cell 3 which says B23. I then want cell 4 to to be the value of cell B23. If the B23 in cell 3 changes to C11, I want cell 4 to be the value of cell C11.

I would use an IF(x,x,x), but I want to to take into account the fact that it could be any cell on the whole sheet which would be one hell of a nested IF statement.

I need to do this without macros if possible.

View 3 Replies


ADVERTISEMENT

Cell Addresses Derived From Concatenated Values In Separate Cells

Apr 2, 2013

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

View 4 Replies View Related

Split Concatenated Delimited Cell Values Into Separate Columns

Jun 20, 2014

Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg

Concatenated comma delimited data

Cust Name Cust Identifier Locations
L Kim 543545 California,Chicago,Florida
S David 31434 Maryland,Vermont,Maine
D Bryant 572575 Texas,Oaklahoma,Nebraska

i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one

Customer name Customer Identifier Location
L Kim 543545 California
L Kim 543545 Chicago
L Kim 543545 Florida
S David 31434 Maryland
S David 31434 Vermont
S David 31434 Maine
D Bryant 572575 Texas
D Bryant 572575 Oaklahoma
D Bryant 572575 Nebraska

View 2 Replies View Related

Excel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value

Jul 2, 2012

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001
q9002
q9003
q9004
q9005
q9006
etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

(Using Excel 2010)

View 7 Replies View Related

Excel 2010 :: Produce Unique List Of Key Values With Second Values Concatenated Together

Sep 15, 2013

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b

My system is Windows 8 Excel 2010.

View 7 Replies View Related

How To VLOOKUP A Value In A Another Sheet And Return Concatenated Values Of Other Cells

Nov 27, 2013

I need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).

The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).

I'm not sure how I can do this using formula.

I've attached a example.

View 6 Replies View Related

Conditional Format Based On Value In Concatenated Formula?

Jul 21, 2012

I am lookin to apply CF to a cell based on the value of the left two numbers in a concatenated formula. If the value of the left two numbers are greater than zero the apply the CF

View 6 Replies View Related

Concatenated Cell Value For If Statement

Jul 6, 2006

I have a cell that containes a concatenate statement for two named formula. The value taht the cell returns is a multiple of 10 (i. e10, 100, 1000, 10000 etc etc.) then in the adjacent cell, i have a nested if statement giviing differing text dependent upon the other cells value, i.e if less than 1000, return text string of "good" , however the formula does not seem to accept the value given in the concatenate cell.

View 9 Replies View Related

Format Cell With Concatenated Contents To 2 Decimal Places

Dec 3, 2013

I have a list of cells that has two values concatenated. The first I would like to round to 2 decimal places, and the second is always a integer

Consider this formula..

Code:
=IF(SUMIFS('Analysis Data'!$I$5:$I$1840,'Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1)=0,0,SUMIFS('Analysis Data'!$I$5:$I$1840,'Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1)/COUNTIFS('Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1,'Analysis Data'!$I$5:$I$1840,">0"))
&"("&COUNTIFS('Analysis Data'!$C$5:$C$1840,3,'Analysis Data'!$E$5:$E$1840,1,'Analysis Data'!$I$5:$I$1840,">0")&")"

In one cell this yields the value;

2.42553191489362(47)

In another it yields 2.975(40)

and so on..

I would like to format it to display as 2.43(47) and 2.98(40)

FWIW this means average of 2.43 games refereed per session in 47 sessions of refereeing.

View 3 Replies View Related

Cell Value Of Closed Workbook With Concatenated File Path In Cells

Sep 24, 2007

I have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.

I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.

View 4 Replies View Related

Generating Values In Number Of Cells Based On Adjacent Cell Values

May 22, 2014

What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username

The following is my code

[Code] ....

Above code runs without errors but does nothing.

View 4 Replies View Related

VBA To Fill Cell Values Based On Adjacent Cell Values

Jun 5, 2014

I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".

I have attached the work book of what I am trying to accomplish.

View 14 Replies View Related

Combining Cell Values In Column Based On Other Cell Values?

Mar 2, 2012

it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.

View 5 Replies View Related

VBA - Copy Range With Multiple Same Values Based On Other Cell Values

Aug 31, 2012

I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.

View 4 Replies View Related

Assign Values To A Cell Based On Values Of Another

Jul 7, 2013

I'm attempting to assign 1 of 4 predetermined values to a cell based on the values of entries in another cell.

View the example attached : Value assignmts.xls

Assign the Value of 1, to (D8) when any value entered in (C9) is equal to or over 380, but less than 410.
Assign the Value of 2, to (D8) when any value entered in (C9) is equal to or over 410, but less than 440.
Assign the Value of 3, to (D8) when any value entered in (C9) is equal to or over 440, but less than 470.
Assign the Value of 0, to (D8) when any value entered in (C9) less than 380.

View 9 Replies View Related

Sum Time Values Based On Other Cell Values

Dec 19, 2013

I have a table of times which shows how long it took someone to process a form. If this value is longer than 10 minutes the cell next to the duration time contains the value 'Break Over Ten'. This is only true when the time is outside of 12:00 to 14:00. If the break is within this time period it shows 'Lunch'.

Is it possible to sum all of the time values that are over flagged as 'Break Over Ten'? This is how it looks

Event Date Start Time End Time Duration Breaks Duration Of Break
19/12/201308:43 08:43 00:00:35 00:00
19/12/201308:43 08:44 00:01:03 00:01
19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13
19/12/201309:05 09:19 00:13:15 BREAK OVER TEN00:13

I was thinking of using SUMIF but can't work out how to total the values in the duration of break column when the cell to the left reads break over ten. Is it possible to do this?

View 1 Replies View Related

Excel 2010 :: Display Cell Ranges From Multiple Locations Based On 2 Cell Values?

Jun 4, 2013

I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...

I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.

P.S. I have excel 2010 and windows 7

View 1 Replies View Related

Change Colour And Values Of Cell Based On The Value In Adjacent Cell?

Nov 10, 2012

I need to write a condition in such a way that cell IF A1= "DBR","BPR","SLR","SKR" then change cell B1 to SPECIFIC COLOUR Each condition different colour. A1 values ("DBR","BPR","SLR","SKR") is a list of selectable values. if the condition is falls then B1 should be normal (white color cell) with 0 in it.

View 1 Replies View Related

Colour Cell Based On Few Conditions / Cell Values Of Other Cells In Same Row?

Jun 28, 2014

In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week

I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.

The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.

The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.

View 9 Replies View Related

Copy Cell Values In One Cell Based On Value Of Adjacent Cells

Feb 20, 2014

What I want to achieve: those cells have number 1 ( in column E) , adjacent cell values ( In Column D) are copied to G12 in the following format:: Mark,Lark ( i.e. cell value , cell value, etc)

is it possible ( with a command button) Sample attached

View 4 Replies View Related

Clear Contents Of Cell Based On Comparsion Of Cell Values

Mar 7, 2007

I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col

if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................

View 4 Replies View Related

Cell Values Based On Input Of Another Cell And Sheet

Apr 2, 2007

I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example

If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.

View 4 Replies View Related

Doing IF Compare Against Concatenated Value In VBA

Jan 31, 2013

struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .

I've tried the following:

if MYWEEK = "WEEK " & A1 then ...
if MYWEEK = "WEEK " + A1 then ...
if MYWEEK = ("WEEK " & A1) then ...

I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?

View 2 Replies View Related

Concatenated TextBox

Oct 30, 2007

I have a TextBox that I want to show the concatenated value of two cells. Is that possible? Or do I need to concatenate it on the sheet and then have the TextBox = that cell?

View 9 Replies View Related

Vlookup Concatenated Value As A Variable

May 13, 2009

Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column

Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.

I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.

Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.

View 10 Replies View Related

Countif With Concatenated Range

May 2, 2012

How can I countif on a row/range

I tried this but this wont work. All i want to countif there are duplicate rows. This formula will be copied down

=countif($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,A1&B1&C1)

View 3 Replies View Related

Summing Concatenated Cells

Dec 7, 2006

I have concatenated the following cells:
A1, B1, C1

The data in each cell is:
1, +, 2

I can concatenate them to show "1+2".

My question is, can I then perform the calculation that the concatenation produces?

I would like to be able to enter different operators into B1 in order to carry out different calculations.

View 9 Replies View Related

Concatenated All Results Of Lookup

Aug 29, 2007

I'm trying to use this function which was posted as an answer (looking up a single value and returning multiple results, concatenating those results in one cell):

Function getfiles(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRng Then
holder = holder & ce.Offset(0, 1).Value & ", "
End If
Next ce
getfiles = Left(holder, Len(holder) - 2)
End Function

What is "ce" here? Auto Merged Post;additional info:

the original question was posted by jwhite68, Feb 27th 2007, "Return Multiple Values From Lookup To Single Cell"

View 9 Replies View Related

If Statement Based On Two Cell Values

Mar 28, 2009

If I have any value in cell A1 then the cell should show 1 if true or nothing if false. I have managed this via

View 3 Replies View Related

Generate Zero Based On Other Cell Values.

Aug 4, 2009

find the attached, I've some formulas on cells m,n & o and they related to each other, i just need n & o should be zero if m is zero.but the same formulas
should exist also.

View 3 Replies View Related







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