Generating name from cell value

This is my first post so excuse me if the answer is trivial.

I use names with implicit and explicit intersection extensively to work on a lot of data. Now I want to use explicit intersection to pass data from Sheet2 to Sheet1instead of direct reference functions like INDIRECT as there is a lot of movement in the source and target sheets, rows and columns comes and goes. I also want to avoid using VBA macros.

My problem is that I want to generate one of the names for the explicit intersection from a cell value.

I could simply manually setup each data item as:

cell1=Rowname1 Columname1 (in Sheet 2 but all names are Workbook level)
cell2=Rowname1 Columname2
cell3=Rowname1 Columname3
....

this forces me to change Rowname1 in each cell when I want to set up a dataset of 100 columns with cells. Could be done with Ctrl H "Replace" but I want to have Rowname1 defined as a parameter that I define and change once eg in the first cell in each column in the table. Once I change in this cell it shall then change Rowname1 in all target cells. Thereby I would have a general formula like cell=name1(from first cell) ColumnameX and can copy that to all 100 columns.

Whatever I try I get Value fault meaning Excel can't evalute the first expression and take it as a name.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Terrific - I didn't come up with anything!

I was going to ask you whether the sample was correct!

BTW - Welcome to the Forum!

Thank! Finding the answer was a relief, direct referencing to fetch data is a drag, every-time one needs to add a data item the references goes bust and changing 100 occurrences it just not OK.

Now I have a robust way of moving data between locations and sheets using explicit intersection with named ranges. Reliable, easy to maintain and no macros needed.

I added the sample with the correct way of doing it for a few rows in this answer for reference for others. Excelforum Ferpe_ answer file 2015.07.26.xlsx

In rows 4, 5 I have the ; (for Europe you can't use , as separator, have to use ; ) in the INDIRECT function, row 6 shows this can be left out.

Thanks Ferpe!