Formula for Named Range in Excel - Dynamic Range

[hrayani]

Hello Friends,

If possible, what formula should I use to give the below list a dynamic range
In this case I want the name manger to look at cells (B2:B8) but ignore where the adjacent cell contains word *Total*

If I simply use offset then it picks the entire range B2:B8

=OFFSET(Sheet1!$A$2:$A$8,0,1)

But I want name list to ignore cells B5 & B8

Pls note that the word *Total* can come in any of the cells in the entire range (A2:A8) as its a result of formula

Book2
	A	B
1	Description
2	Apples
3	Apples
4	Apples
5	Total Apples
6	Bananas
7	Bananas
8	Total Bananas
Sheet1

 

[Crystalyzer]

=IFERROR(INDEX($B$2:$B$8,IF(NOT(ISNUMBER(SEARCH("Total",$A$2:$A$8,1))),ROW($A$2:$A$8),"")),"")

 

[hrayani]

=IFERROR(INDEX($B$2:$B$8,IF(NOT(ISNUMBER(SEARCH("Total",$A$2:$A$8,1))),ROW($A$2:$A$8),"")),"")

Thanks for the reply
I will try your solution tomorrow as I do not have access to my laptop right now
Will surely give feedback

Regards,
Humayun

 

[hrayani]

=IFERROR(INDEX($B$2:$B$8,IF(NOT(ISNUMBER(SEARCH("Total",$A$2:$A$8,1))),ROW($A$2:$A$8),"")),"")

Not working dear

 

[Crystalyzer]

hrayani 20210729.xlsx
	A	B	C	D
1
2	Apples	John		blue
3	Apples	blue		Harry
4	Apples	Harry		James
5	Total Apples	James
6	Bananas	joe		smalls
7	Bananas	smalls		killing me
8	Total Bananas	killing me
9
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(INDEX($B$2:$B$8,IF(NOT(ISNUMBER(SEARCH("Total",$A$2:$A$8,1))),ROW($A$2:$A$8),"")),"")
Dynamic array formulas.

 

[hrayani]

Thanks for the reply.... but I did not get it... Do I have to put this formula in the name manager... If yes then nothing happens

Let me explain again
See the data below... I have created the named range =Sheet1!$B$2:$B$4,Sheet1!$B$6:$B$7
I want this range to be changed automatically when Total is found in any of the cells in Range A2:A8

Like when total is found in cell A2 only then I would want the name ranged to be linked to cells B3:B8

Book1
	A	B
1	Fruits Names	Dynamic List
2	Apples
3	Apples
4	Apples
5	Total Apples
6	Bananas
7	Bananas
8	Total Bananas
Sheet1

 

[Crystalyzer]

sorry, I misread and thought you were looking to only return the VALUES that were not "Total" which my formula does. I'm not sure there is a way to do what you want since the starting and ending points of each range will be dynamic.

 

[hrayani]

sorry, I misread and thought you were looking to only return the VALUES that were not "Total" which my formula does. I'm not sure there is a way to do what you want since the starting and ending points of each range will be dynamic.

No issues.....

Perhaps some other friend might give it a go... if possible

Regards,

 

[jasonb75]

As with many of your threads, you have only asked part of the question so providing anything resembling a solution is going to be close to impossible without a lot of guesswork and time wasting.

Whilst it may be possible to create a named range in the way that you want to, the real question is what is that named range going to be used for once it has been created?

It will be difficult to do with your version of excel, with office 365 you could do it with the FILTER function, however, if you try to use that range in a formula afterwards, there are many that it will not work with.

 

[hrayani]

As with many of your threads, you have only asked part of the question so providing anything resembling a solution is going to be close to impossible without a lot of guesswork and time wasting.

Whilst it may be possible to create a named range in the way that you want to, the real question is what is that named range going to be used for once it has been created?

It will be difficult to do with your version of excel, with office 365 you could do it with the FILTER function, however, if you try to use that range in a formula afterwards, there are many that it will not work with.

This named range one created will never be used in any formula.
I will then copy a date from a cell within the sheet & paste in the named range via VBA