How to get the value from a cell given a name box in Excel

Question

I have a situation that I need to do a concatenation to add a single quote in the beginning and end of whatever is at column Q, and repeat the value twice by row in column I:

So far I was doing things manually, since Excel was not able to autofill things correctly (maybe I'm doing it wrong as well).

=CONCAT("'";Q2;"'")
=CONCAT("'";Q2;"'")
=CONCAT("'";Q3;"'")
=CONCAT("'";Q3;"'")

Since I need to do this for 500 different rows, I wanted to do something automatic, my thought process would be do something like:

In Column Y, I got the correct cell I want to access using:

CONCAT("Q") + INT(((ROW(A3)-1)/2))+MOD(ROW(A3)-1;2) 

which is the cell I need to get access, so I was thinking of having a formula like: ACCESS_VALUE(Y2), it would read "Q1" in Y2, and return the "1975351765" value, and I could add the single quote in column I.

Maybe I'm doing the most complicated way, let me know if there's an easier way to do so, any help is appreciated!

Thanks in advance!

Answer 1

The problem you are having is related to your layout of two rows sourcing data from one. With some flexibility in your layout it would be 2 cell copied as many as you want.

It seems that you source data is a single vertical list

Would it be acceptable to put you < and >= labels side by side in one row. Then you need one relative addressing formula in each of the two cell. Copy this down for however many rows of source data you have.

The problem you are having is related to your layourt of two row sourcing data from one.

Sample in A1 corner

Results

<	>=		source value
'123'	'123'		123
'234'	'234'		234

Formulas (flipped sheet)

<	>=		source value
"'"&D2&"'"	"'"&D2&"'"		123
"'"&D3&"'"	"'"&D3&"'"		234