Fill column with default values

quemuenchatocha

Board Regular
Joined
Aug 4, 2021
Messages
50
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Dear best regards

I am trying to get the values that are located in column B, to be reproduced in column C in the same order as they appear. The problem is that I have entered some formulas that do not allow me to obtain the values I require, and instead they always reproduce the same one (Albania).

Exercice_1.xlsx
ABCD
1Region/Country/AreaPopulation and rates of growth in urban areas and capital citiesYear
21AfghanistanAfghanistan2005
32Albania 2010
43Algeria 2015
54American Samoa 2018
65AndorraAlbania2005
76Angola 2010
87Anguilla 2015
98Antigua and Barbuda 2018
109ArgentinaAlbania2005
1110Armenia 2010
1211Aruba 2015
1312Australia 2018
1413AustriaAlbania2005
1514Azerbaijan2010
1615Bahamas2015
1716Bahrain2018
1817Bangladesh2005
192010
202015
212018
Example
Cell Formulas
RangeFormula
C2:C5C2=IF(D2=2005,INDIRECT("B"&ROW()),"")
C6:C14C6=IF(D6=2005,INDIRECT("B"&COUNTA(A2:A4)),"")
A2:A18A2=ROW()-1


I would like to know if there is an option that allows me to obtain an appropriate result, i.e., that in cell C6 the value of cell B3 appears, in cell C10 the value of cell B4 appears, in cell C14 the value of cell B5 appears.....

I hope to be clear and understand what I am looking for, I appreciate any information.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
How about
Fluff.xlsm
ABCD
1Region/Country/AreaPopulation and rates of growth in urban areas and capital citiesYear
21AfghanistanAfghanistan2005
32Albania 2010
43Algeria 2015
54American Samoa 2018
65AndorraAlbania2005
76Angola 2010
87Anguilla 2015
98Antigua and Barbuda 2018
109ArgentinaAlgeria2005
1110Armenia 2010
1211Aruba 2015
1312Australia 2018
1413AustriaAmerican Samoa2005
1514Azerbaijan 2010
1615Bahamas 2015
1716Bahrain 2018
1817BangladeshAndorra2005
19 2010
20 2015
21 2018
Main
Cell Formulas
RangeFormula
A2:A18A2=ROW()-1
C2:C21C2=IF(D2=2005,INDEX($B$2:$B$18,COUNTIFS(D$2:D2,2005)),"")
 
Upvote 0
Solution
Dear @Fluff as always your valuable help is impeccable, thank you very much for your attention.
It is exactly what I am looking for
A big hug!
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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