**Question description:**

*This user has given permission to use the problem statement for this blog.*

I have a formula that counts non blank cells in Cell A2 now i want to create another array formula where in i want to use this count as one of the reference i.e. $F$1:$F$(value in cell A2). Please advice?

Solved by B. C. in 45 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
08/09/2018 - 09:27

Hi

User
08/09/2018 - 09:27

hi

Excelchat Expert
08/09/2018 - 09:28

Please explain the problem in detail

User
08/09/2018 - 09:28

I am trying to use array formula for sorting.. so that i get the exact unique values in a list

User
08/09/2018 - 09:28

but the problem is my list is dynamic

User
08/09/2018 - 09:29

sometime i have 30 students / 50 students

User
08/09/2018 - 09:29

so now i want to create my array formula in such a way.. that the count of my list is taken into it

User
08/09/2018 - 09:29

=IFERROR(INDEX($F$2:$F$10,SMALL(IF(ISNUMBER(SEARCH($A$1,$F$2:$F$10)),ROW($F$2:$F$10)-ROW($F$2)+1),ROWS($1:1))),"")

User
08/09/2018 - 09:30

for example.. in this formula.. i am trying to use..

Excelchat Expert
08/09/2018 - 09:30

Would you be able to share the file?

User
08/09/2018 - 09:30

sure.. moment please

User
08/09/2018 - 09:32

just a sec.. my file was XLSB.. i will convert it into xlsm.. one moment please

Excelchat Expert
08/09/2018 - 09:32

convert it into xlsx

User
08/09/2018 - 09:33

i have done that.. and attached.. so shall i click enter?

[Uploaded an Excel file]

User
08/09/2018 - 09:33

oh ok

User
08/09/2018 - 09:34

Now in reality.. by list of column F is dynamic.. it can be 10-50 or even 100 students

User
08/09/2018 - 09:34

my list of*

Excelchat Expert
08/09/2018 - 09:34

okay

Excelchat Expert
08/09/2018 - 09:35

what's the problem here

User
08/09/2018 - 09:35

so in that formula.. I have dictated that F1:F10 right?

Excelchat Expert
08/09/2018 - 09:36

yes

User
08/09/2018 - 09:36

now the reference of F1 is fine.. but F10 cannot be static.. it has to be dynamic.. depending on the list of items in Column F

Excelchat Expert
08/09/2018 - 09:36

okay

User
08/09/2018 - 09:36

so i want that 10 to be picked from count of items

User
08/09/2018 - 09:37

not just putting the value 10 or 100 there

Excelchat Expert
08/09/2018 - 09:37

got it

Excelchat Expert
08/09/2018 - 09:37

ive me sometime to work on it

Excelchat Expert
08/09/2018 - 09:37

*give

User
08/09/2018 - 09:37

sure sir/madam..

User
08/09/2018 - 09:37

This is Manish here

User
08/09/2018 - 09:37

your good name?

User
08/09/2018 - 09:45

is there anything like.. dynamic reference.. where in the value on cell can be taken as a reference for a range? just guessing.. :)

Excelchat Expert
08/09/2018 - 09:46

I am trying to make the range dynamic only

User
08/09/2018 - 09:46

cool :D thanks

Excelchat Expert
08/09/2018 - 10:04

In the first cell I changed the formula by creating a dynamic range - List1

[Uploaded an Excel file]

Excelchat Expert
08/09/2018 - 10:05

And instead of $F$2:$F$F10, i have used List1 everywhere in the formula

User
08/09/2018 - 10:06

cool ... thanks a lot Sir / Madam for all your help and effort on this

Excelchat Expert
08/09/2018 - 10:06

you are welcome

User
08/09/2018 - 10:07

Sir/Madam.. what is the difference between the 2 lists?

User
08/09/2018 - 10:08

one says Offset

User
08/09/2018 - 10:08

the other is the sheet 1

User
08/09/2018 - 10:10

i think i got that... thanks again sir/madam.. for all your guidance here....

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*