Copying Data from one cell to umpteen Cells

[Scary123]

Hi Everyone,

Hope this is the right place for this question but here goes: -

I have a file where I want to copy data from one cell to a whole row the data needs to increase by the number and nothing else the words stay the same, it is set out like this: -

The Data in Column B1 =Data!Y2

The Data in Column C2 - AV48 needs to look like =Data!Y3, =Data!Y4, etc right up to =Data!Y48

The only way I can do it is to do every column one at a time don't know if there is a simpler way of doing it or not.

Kind Regards,

Mark

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[Glenn Kennedy]

??? Is this what you mean, in C2, dragged across and down:

=data!$Y3

[Scary123]

Please see the enclosed File.

[JeteMc]

From 2nd text box in Book2.xlsx: The cells going across take data from another sheet but need to read the columns.
So the First Line A1 - AL1 reads the column A1 - A38
The Second Line A2 - AL2 reads columns B1 B38 and so on.

I would seem to me that the easiest way to do this would be to copy and paste transpose as modeled in rows 13:21
If you need a formula then I believe that you are looking for one like the following that is modeled in rows 1:9 =INDEX(data!$A$1:$AL$38,COLUMNS($A$1:A$1),ROWS($A$1:$A1))
Let us know if you have any questions.

[Scary123]

Hi JetMc,

Thank you for the reply and the following worked great - =INDEX(data!$A$1:$AL$38,COLUMNS($A$1:A$1),ROWS($A$1:$A1)).

Much appreciated I knew there must have been a way to do it but I was not sure.

Kind Regards

Mark

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.