Dynamic Table Update by column value

Shayanan

New Member
Joined
Feb 11, 2021
Messages
8
Office Version
  1. 2019
Platform
  1. Windows
Column A is return of formula. Column B is Alphabetically sort number of Column A.
I need a Dynamic table that in Table Column1, put sort numbers from 1 to how much is that from column B. and if Column b Updated, it update too.

Options-2021-v11.xlsm
IJKLMNOPQRSTU
1
2Column BColumn A
316Apple Inc.
4Column3Column2Column 120Atlas Air Worldwi...
5Aaron's, Inc.112Allianceberstein ...
6ABB Ltd217Applied Optoelect...
7AbbVie Inc.319Artius Acquisitio...
8AbCellera Biologi...418Ares Acquisition ...
9ABG Acquisition C...59Agilent Technolog...
10ABIOMED, Inc.60
11ABM Industries In...711Alcoa Inc.
12Advance Auto Part...813American Airlines...
13Agilent Technolog...91Aaron's, Inc.
14Alcoa Inc.110
15Allianceberstein ...128Advance Auto Part...
16American Airlines...130
17Ameris Bancorp142ABB Ltd
18AmerisourceBergen...150
19Apple Inc.160
20Applied Optoelect...170
21Ares Acquisition ...183AbbVie Inc.
22Artius Acquisitio...1915AmerisourceBergen...
23Atlas Air Worldwi...2014Ameris Bancorp
244AbCellera Biologi...
250
265ABG Acquisition C...
277ABM Industries In...
280
290
306ABIOMED, Inc.
310
320
330
3410Airbnb, Inc.
35
Sheet8
Cell Formulas
RangeFormula
S3:S34S3=COUNTIF($T$3:$T$2000,"<="&T3)
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Excel Formula:
O5=IFERROR(INDEX($S$3:$S$34,MATCH($N5,$T$3:$T$2000,0)),"")
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,787
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Book1
IJKLMNOPQRSTU
1
2Column BColumn A
316Apple Inc.
4Column3Column2Column 120Atlas Air Worldwi...
5Aaron's, Inc.112Allianceberstein ...
6ABB Ltd217Applied Optoelect...
7AbbVie Inc.319Artius Acquisitio...
8AbCellera Biologi...418Ares Acquisition ...
9ABG Acquisition C...59Agilent Technolog...
10ABIOMED, Inc.60
11ABM Industries In...711Alcoa Inc.
12Advance Auto Part...813American Airlines...
13Agilent Technolog...91Aaron's, Inc.
14Airbnb, Inc.100
15Alcoa Inc.118Advance Auto Part...
16Allianceberstein ...120
17American Airlines...132ABB Ltd
18Ameris Bancorp140
19AmerisourceBergen...150
20Apple Inc.160
21Applied Optoelect...173AbbVie Inc.
22Ares Acquisition ...1815AmerisourceBergen...
23Artius Acquisitio...1914Ameris Bancorp
244AbCellera Biologi...
250
265ABG Acquisition C...
277ABM Industries In...
280
290
306ABIOMED, Inc.
310
320
330
3410Airbnb, Inc.
35
Sheet3
Cell Formulas
RangeFormula
L5:L23L5=VLOOKUP(M5,$S$3:$T$2000,2,FALSE)
M5M5=MINIFS($S$3:$S$2000,$S$3:$S$2000,">" & 0)
M6:M23M6=MINIFS($S$3:$S$2000,$S$3:$S$2000,">" & M5)
S3:S34S3=COUNTIF($T$3:$T$2000,"<="&T3)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff 1.xlsm
IJKLMNOPQRST
1
2Column BColumn A
316Apple Inc.
4Column3Column2Column 120Atlas Air Worldwi...
5Aaron's, Inc.112Allianceberstein ...
6123117Applied Optoelect...
7ABB Ltd219Artius Acquisitio...
8AbbVie Inc.318Ares Acquisition ...
9AbCellera Biologi...49Agilent Technolog...
10ABG Acquisition C...50
11ABIOMED, Inc.611Alcoa Inc.
12ABM Industries In...713American Airlines...
13Advance Auto Part...81Aaron's, Inc.
14Agilent Technolog...90
15Airbnb, Inc.108Advance Auto Part...
16Alcoa Inc.110
17Allianceberstein ...122ABB Ltd
18American Airlines...130
19Ameris Bancorp140
20AmerisourceBergen...150
21Apple Inc.163AbbVie Inc.
22Applied Optoelect...1715AmerisourceBergen...
23Ares Acquisition ...1814Ameris Bancorp
244AbCellera Biologi...
250
265ABG Acquisition C...
277ABM Industries In...
280
290
306ABIOMED, Inc.
310
321123
330
3410Airbnb, Inc.
Master
Cell Formulas
RangeFormula
L5:L23L5=INDEX($T$3:$T$2000,AGGREGATE(15,6,(ROW($T$3:$T$2000)-ROW($T$3)+1)/($S$3:$S$2000=M5),COUNTIFS(M$5:M5,M5)))
M5:M23M5=AGGREGATE(15,6,$S$3:$S$2000/($S$3:$S$2000>0),ROWS(M$5:M5))
S3:S34S3=COUNTIF($T$3:$T$2000,"<="&T3)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,932
Latest member
mm07

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
Top