Calling an existing “Name” by combining different cells

V

Vasco39

New Member
Joined
Jan 20, 2016
Messages
10
Ex:
Row 1: B1=Dom, C1=Seg, D1=Ter, E1=Quar, ……………………….
Column A A3=3, A4=2, A5=3, A6=1, …………………………………
Existing Names:
Dom§1§=0
Dom§2§=0
Dom§3§=6.15
Seg§1§=38.30
Seg§2§=38.34
Seg§3§=58.46 Shown in the example below
Ter§1§=11.78
Ter§2§=17.65………………………………………..

If I put in the grid on a cell C5 the formula =concatenate(“=”,C$1,”§”,$A5,”§”) I get as an answer =Seg§3§ but what I wanted to have in that cell was the real value i.e. 58.46. I know I can call the cell with F2 and then convert the cell into the real value by pressing F9 but that is long job because there are lots of cells that have to be changed.

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 8"]Then putting the formula '=concatenate(“=”,C$1,”§”,$A5,”§”)' in cell C5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]or equivalent what I get is:

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Dom
[/TD]
[TD="align: center"]Seg
[/TD]
[TD="align: center"]Ter
[/TD]
[TD="align: center"]Qua
[/TD]
[TD="align: center"]Qui
[/TD]
[TD="align: center"]Sex
[/TD]
[TD="align: center"]Sáb
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]=Dom§1§
[/TD]
[TD="align: center"]=Seg§1§
[/TD]
[TD="align: center"]=Ter§1§
[/TD]
[TD="align: center"]=Qua§1§
[/TD]
[TD="align: center"]=Qui§1§
[/TD]
[TD="align: center"]=Sex§1§
[/TD]
[TD="align: center"]=Sáb§1§
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]=Dom§2§
[/TD]
[TD="align: center"]=Seg§2§
[/TD]
[TD="align: center"]=Ter§2§
[/TD]
[TD="align: center"]=Qua§2§
[/TD]
[TD="align: center"]=Qui§2§
[/TD]
[TD="align: center"]=Sex§2§
[/TD]
[TD="align: center"]=Sáb§2§
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]=Dom§3§
[/TD]
[TD="align: center"]=Seg§3§
[/TD]
[TD="align: center"]=Ter§3§
[/TD]
[TD="align: center"]=Qua§3§
[/TD]
[TD="align: center"]=Qui§3§
[/TD]
[TD="align: center"]=Sex§3§
[/TD]
[TD="align: center"]=Sáb§3§
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]=Dom§3§
[/TD]
[TD="align: center"]=Seg§3§
[/TD]
[TD="align: center"]=Ter§3§
[/TD]
[TD="align: center"]=Qua§3§
[/TD]
[TD="align: center"]=Qui§3§
[/TD]
[TD="align: center"]=Sex§3§
[/TD]
[TD="align: center"]=Sáb§3§
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]But what I wanted was:
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]Dom
[/TD]
[TD="align: center"]Seg
[/TD]
[TD="align: center"]Ter
[/TD]
[TD="align: center"]Qua
[/TD]
[TD="align: center"]Qui
[/TD]
[TD="align: center"]Sex
[/TD]
[TD="align: center"]Sáb
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0.00
[/TD]
[TD="align: center"]38.30
[/TD]
[TD="align: center"]11.78
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0.00
[/TD]
[TD="align: center"]38.34
[/TD]
[TD="align: center"]17.65
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]6.15
[/TD]
[TD="align: center"]58.46
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]6.15
[/TD]
[TD="align: center"]58.46
[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Must be possible and simple but I’ve gone around Excel and could not find the solution. Can you help me? Thank you.
Vasco39
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi Vasco

You have to evaluate the string.

The easiest is to use a simpe vba function like:

Code: 
Function Eval(s As String) As Variant
Eval = Evaluate(s)
End Function

and then you can use in the cell

=Eval(CONCATENATE(C$1,”§”,$A5,”§”))
 
Last edited:
Upvote 0
Thank you pgc01 for your reply.

I've tried running the vba function but it doesn't work. I get the following reply when I try:

"Compile error:
Ambiguous name detected: Eval"

What am I doing wrong....
 
Upvote 0
Hi

The error means that you have already something else defined with the name Eval.

Change the name of the function to something else, like MyEval or some other name that you are not using for something else.
 
Upvote 0
Once again pgc01 for your reply.

Again I get a similar answer even if I change the name of the function. No I get:

"Compile error:
Ambiguous name detected: MyEval"

And besides I didn't have anything with the name of Eval in my Excel sheet.
 
Upvote 0
Sorry, I don't know how that can happen.

"Ambiguous name detected" is very clear, there are 2 entities with the same name.

One thing you can try is to delete de function and in the immediate window write

?Eval

Does vba recognise the name?

Also you can try it in a new workbook. Doing it from scratch may help you understand what's happening.
 
Upvote 0
PGC01
Thank you once more for your help and time consuming.
It's now working perfectly well. It wasn't before due to my inexperience.
THANKS THANKS
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Send cell values to 2 other sheets according to condition
Replies
2
Views
199
leobrice
L
L
  • Solved
Inserting new rows base on cell value, copying cell value on the first 3 column and transpose data for the rest
2
Replies
15
Views
971
Legacy 143009
L
P
  • Solved
Combine 3 columns and rename cells with 0 value
Replies
4
Views
408
Fluff
Fluff
M
  • Question Question
Challenging: VBA to Combine (1) Naming Ranges with Another Range's Values; (2) Row & Column Loops; (Finding Last Row/Column)
Replies
1
Views
597
Leith Ross
Leith Ross
C
  • Question Question
double lookup
2
Replies
12
Views
983
VoG
VoG

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top