# Excel formula

#### SDTMN

##### New Member
Hi all,

I have an excel which on "A" sheet has data on horizontal so A1=Me,B1=You,C1=They and then on the "B" sheet i retrieve these values and concatenate with some text but on vertical, so A1=Me,A2,You,A3=They. So far so good but then when i have values in the "A" sheet on A2,B2,C2 and so on , on the second sheet I want to just copy past the formulas from above but still to keep the pase by incrementing all 3 values with 1 not every value with 1. Does it make sense?

Thanks.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### FormR

##### MrExcel MVP
Hi, welcome to the forum!

Something like this maybe?

Excel 2013
A
1Me some text
2You some text
3They some text
4Other 1 some text
5Other 2 some text
6Other 3 some text
7More 1 some text
8More 2 some text
9More 3 some text
Sheet B
Cell Formulas
RangeFormula
A1=INDEX('Sheet A'!\$A\$1:\$C\$1000,ROUNDUP(ROWS(A\$1:A1)/3,0),MOD(ROWS(A\$1:A1)-1,3)+1)&" some text"

Excel 2013
ABC
1MeYouThey
2Other 1Other 2Other 3
3More 1More 2More 3
Sheet A

#### SDTMN

##### New Member
Hi FormR, thanks for looking into this and apologies for my late reply, ive been in a mini holiday

Thats not what I want, let me try to explain, below are the columns, A,B,C,D and on X I need a foruma to keep the pase like on the X2 to show me the value of B2, then on X3 to put the value of C2 and on X3 the value of D2. Then when i drag down to know to go to the next line which is line 3 and put the next 3 values.

 A B C D X Code Description Name 1 Supplier1 Nike Nike shoes Nike Inc Nike 2 Supplier2 Addidas Addidas shoes Addidas Co. Nike shoes 3 Supplier3 Pepsi Pepsi drink Pepsi Ltd Nike Inc Addidas Addidas shoes Addidas Co.

<colgroup><col span="3"><col><col><col span="5"><col></colgroup><tbody>
</tbody>

Hope the above makes sense...

#### FormR

##### MrExcel MVP
Hi, like this? - formula in X2 can be copied down as required.

Excel 2013
ABCDEX
1CodeDescriptionName
2Supplier1NikeNike shoesNike IncNike
4Supplier3PepsiPepsi drinkPepsi LtdNike Inc
8Pepsi
9Pepsi drink
10Pepsi Ltd
Sheet1
Cell Formulas
RangeFormula
X2=INDEX(\$B\$2:\$D\$1000,ROUNDUP(ROWS(X\$2:X2)/3,0),MOD(ROWS(X\$2:X2)-1,3)+1)

#### SDTMN

##### New Member
Yes, Brilliant ! Can you help me understand it?

#### FormR

##### MrExcel MVP
Can you help me understand it?

Excel 2013
ABCDEFGHI
1CodeDescriptionNameRow Formula is inRow to indexCol to indexGet row & col from range
2Supplier1NikeNike shoesNike Inc111Nike
4Supplier3PepsiPepsi drinkPepsi Ltd313Nike Inc
8731Pepsi
9832Pepsi drink
10933Pepsi Ltd
Sheet1
Cell Formulas
RangeFormula
F2=ROWS(F\$2:F2)
G2=ROUNDUP(F2/3,0)
H2=MOD(F2-1,3)+1
I2=INDEX(\$B\$2:\$D\$1000,G2,H2)

To gain a better understanding of the individual functions used the built in help is always a good place to start.

#### SDTMN

##### New Member
You are fantastic Sir!You make internet a better place Happy hollidays!

Replies
14
Views
510
Replies
3
Views
396
Replies
16
Views
734
Replies
3
Views
496
Replies
11
Views
2K

1,195,834
Messages
6,011,860
Members
441,651
Latest member
drewe2000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back