Copy excel formula

dlogic

New Member
Joined
Dec 7, 2020
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hello, i need help with following problem:

A1 needs to be equal data in B1
A2 needs to be equal data in C1
A3 needs to be equal data in D1
A4 needs to be equal data in B2
A5 needs to be equal data in D2
A6 needs to be equal data in C2
and so on for 10000 rows.

Can anyone help me with this?
 

Attachments

  • Excel.png
    Excel.png
    13.3 KB · Views: 11

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this helps.

20 12 07.xlsm
ABCD
1AAE1
2EBF2
31CG3
4BDH4
5F
62
7C
8G
93
10D
11H
124
List
Cell Formulas
RangeFormula
A1:A12A1=INDEX(B$1:D$4,INT((ROWS(A$1:A1)-1)/3)+1,MOD(ROWS(A$1:A1)-1,3)+1)
 
Upvote 0
Also you can use this:
Excel Formula:
=INDIRECT(IF(MOD(ROW(),3)=1,"B",IF(MOD(ROW(),3)=2,"C","D"))&INT((ROW()-1)/3)+1)
 
Upvote 0
WOW! Thank you! Its working like a charm.
You're welcome. Thanks for the follow-up. :)


Also you can use this:
You can, but I would recommend against it for two reasons
  1. INDIRECT is a volatile function so in general I would avoid if non-volatile options are readily available - which they are in this case.
  2. If subsequently rows are are added above or columns added to the left, incorrect results will result. For example after entering and copying the formulas down, in the sheet below I then added a new row 1.
20 12 07.xlsm
ABCD
1
20AE1
30BF2
4ACG3
5EDH4
61
7B
8F
92
10C
11G
123
13D
14H
List (2)
Cell Formulas
RangeFormula
A2:A14A2=INDIRECT(IF(MOD(ROW(),3)=1,"B",IF(MOD(ROW(),3)=2,"C","D"))&INT((ROW()-1)/3)+1)
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top