Concatenate into formula

[mdooley]

Hi

How do I get the results of a concatenation to be a formula rather than text? I've tried formatting the cell AFTER the concatenation.

A simplified version of what I'm doing, Cell A1 contains A1+ and cell B1 contains B1, if I use ="="&A1&B1, I get =A1+B1 but as text, when I want it to actually return the value of A1+B1. I've tried formatting to general, instead of text, but to no avail.

I'm probably missing something simple *sigh*

Thanks in advance

M.

[Glenn Kennedy]

A1 contains text and b1 contains text and you want a value returned? I don't understand. can you explain again.

[AlKey]

This should work
Formula in C1

Please Login or Register  to view this content.

v	A	B	C
1	10	15	1015

[Feronen]

I think you have this in mind:

Please Login or Register  to view this content.

If you have for example 2+ in A1 cell and 4 in B1 cell, this formula should show text "=6". You have to separate "+" from number 2 to be recognised as value.
I hope that is what you wanted to achieve.

[mdooley]

Thanks for reply, I probably should have explained it better but was trying to keep it simple

I am setting up a suite of marking sheets for student exams.

Worksheet 1 has marks awarded for exam questions.

Worksheet 2 picks up subtotals from worksheet 1 and more calculations are done.

So I can copy and paste links etc and all works fine.


However I have multiple marking sheets to set up and the sub total cells in worksheet 1 are not always the in the same cells, so pasting links for each individual sheet is quite long winded, so I was looking for a shortcut. I have worked out a way of listing the relevant cells in column D sheet 2. Now however I want to pick up the cell reference as a formula, so that the actual value appears in column e. So let's say cell D1 contains "E22" I was trying to say ="sheet1"&D1, so that the value in E22 on sheet 1 appears.

So go to go back to my original example, which is hopefully simpler, if you see the attachment below, I wanted cell d3 to return 30. (I know it doesn't make much sense to do that, but if I can do that, I can do what I'm trying to do above)



Sorry if that sounds dreadfully confusing :S

[AlKey]

You would have to use INDIRECT function
Try this formula in D3

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

v	D	E
1	10	15
2	D1	E1
3	25

Or if you trying to concatenate them

Please Login or Register  to view this content.