Name problem (column to rows)

[Dag]

In Book1 I have values in:
A1
A2
A3
A4
In Book2 I want to get data from that cells in next:
A1 B1 C1 D1

Can I solve this by using one name only (with relative reference... combining with offset or anythin...)?

 

[Joe4]

Can you use the Transpose copy function?
i.e. highlight A1-A4 in Book1 and clicking Copy.
Then go to cell A1 in Book2, right click, select Paste Special, select Transpose, and then click OK.

Or does it have to be all formulaic?
If so, can you make use of VBA?

 

[Dag]

Can you use the Transpose copy function?
i.e. highlight A1-A4 in Book1 and clicking Copy.
Then go to cell A1 in Book2, right click, select Paste Special, select Transpose, and then click OK.

Or does it have to be all formulaic?
If so, can you make use of VBA?

Thank you Joe4.

Nope... problem is not how to do something with large data quantities but how to have it for long time and to KISS forever. Let's say, source for this:
http://orwell.ru/junk/valuess.png
is like this:
http://orwell.ru/junk/formulass.png

I want to use the same method.

 

[Joe4]

Well, it appears that there is also a TRANSPOSE function. I have never really used it, but it looks fairly straightforward. I don't know if there would be any problems using it across workbooks.

Here is an article on it, though you should also be able to find information about it in Excel help: http://www.techonthenet.com/excel/formulas/transpose.php.

 

[Dag]

Well, it appears that there is also a TRANSPOSE function. I have never really used it, but it looks fairly straightforward. I don't know if there would be any problems using it across workbooks.

Here is an article on it, though you should also be able to find information about it in Excel help: http://www.techonthenet.com/excel/formulas/transpose.php.

Hm That what I shaw you is made by me. I am advanced user. No transpose there. Transpose will not walk thrugh many many cells with relative reference... not very familiar with that function but I tried it of course... will give another try. Thanks for your time..

 

[Andrew Poulsom]

You can use a formula like this:

=INDEX(Sheet1!$A:$A,COLUMNS($A1:A1))

in A1 copied across.

 

[Joe4]

The details on your post a little sketchy.

I understand that who want to set-up something that works "simply" and for a "long-time", so you do not want users to have to manually do anything each time. But, is the size of your template "defined", as it is not going to change each time? Or do you need a kind of "build-on-the-fly" type of thing?

If you are just trying to build a template to use that is always going to be the same size, setting up those formulas shouldn't be too difficult, as you can make use of features like auto-fill and functions like ROW() and COLUMN().

 

[Dag]

The details on your post a little sketchy.

I understand that who want to set-up something that works "simply" and for a "long-time", so you do not want users to have to manually do anything each time. But, is the size of your template "defined", as it is not going to change each time? Or do you need a kind of "build-on-the-fly" type of thing?

If you are just trying to build a template to use that is always going to be the same size, setting up those formulas shouldn't be too difficult, as you can make use of features like auto-fill and functions like ROW() and COLUMN().

Not for others - only for me and nothing of above: that way is powerful way for checking out is everything correct or not. With many sheets and many cells... visual perception of one phrase in continuation is very good. And of course: if u must change something, u change only one time and in the name(formula). In my second image I've already found one error (SUM instead of the name).

Each of calculated reports has it's own source; well, that files or forms are for others! So I can simple change them and have proper results on the fly!

Well done Andrew Poulsom!
I've tried INDEX but didn't worked well seems... name of function didn't promise anything usable

Thank you very much guys!
Andrew - sit down: A

 

[Dag]

Any solution?

And none of solutions here so far... http://www.mrexcel.com/forum/showthread.php?t=33116

 

[Joe4]

Well done Andrew Poulsom!
I've tried INDEX but didn't worked well seems... name of function didn't promise anything usable

Thank you very much guys!
Andrew - sit down: A

I am finding some of your replies very confusing. I can't tell if you are saying things worked or didn't work.