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.
