Concatenate referencing

[Donnybrook]

Having issues on something I'm building for work.

Is there a way to have a name indexed (Ex. Seattle.Hat), have those 2 words concatenated elsewhere, and be able to reference that concatenate to pull from the index?

The attached lays it out in a very high level way. Is there a way to be able to reference that formula without it REFing?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Price by month
2	Region	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Seattle	Hat	1	2	3	4	5	6	6	5	4	3	2	1	Seattle.Hat
4
5				1	2	3	4	5	6	7	8	9	10	11	12
6	Seattle	Hat	Seattle.Hat	Seattle.Hat	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
Sheet1

Cell Formulas
Range	Formula
C6	C6	=CONCAT(A6,".",B6)
D6:O6	D6	=INDEX($C$6,D5)

 

[Jamie McMillan]

Hello Donny Brooke,

When you concatenate cells the entry in the cell is not text - it is a formula. (Note - I very seldom use the INDEX function)

A work around:- in an empty row below: I will use row 7 - you can use row 700.

In cell C7 put the formula =CONCATENATE(A6,".",B6). {You can change the text to white; so it is not visible.}

Now you write a macro to copy the row 7 range and paste (as values) into row 6.

Then it should work. You will now have text in Cell C6, etc.

The macro should be like the one below.

Sub CopyPasteValues()
    Range("A7:J7").Copy 'Change the range to copy
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Calculate
End Sub

Let me know if this works for you.

Jamie McMillan

 

[Fluff]

Hi & welcome to MrExcel.
Not sure why you are trying to use index on a single cell.
Why not just use

=$C$6

Also your profile shows you are using 2007, but concat does not exists in that version, so what version are you using?

 

[Donnybrook]

Hi & welcome to MrExcel.
Not sure why you are trying to use index on a single cell.
Why not just use

=$C$6

Also your profile shows you are using 2007, but concat does not exists in that version, so what version are you using?

As I said, it's a very high level example to show what I'm want to do (reference the concate). I'm posting this off my personal computer which has a newer version of excel. My work version is 07

 

[Fluff]

I'm posting this off my personal computer which has a newer version of excel. My work version is 07

Ok

As I said, it's a very high level example to show what I'm want to do (reference the concate)

But it does not make any sense, why are you trying to index a single cell when you can just use the formula I suggested.

 

[Jamie McMillan]

Ok


But it does not make any sense, why are you trying to index a single cell when you can just use the formula I suggested.

Hello Fluff,

Reading the 'index' code, after your post, I can see the apparent intention. You are 100% right.

Jamie McMillan

 

[Fluff]

Glad to help & thanks for the feedback.