Cell Adress through text (solved)

[arifanmol]

can we search cell address from the text or part of text it contains
see the attachment plz

 

[pecoflyer]

Why do you need to get a cell address ? There are many ways to reference a cell wothout having to use its address, avoiding making formulas unnecessarily complicated

 

[arifanmol]

i want go go that cell directly in a big data sheet then from there i want to direct a range for excel formula s

is it possible

 

[pecoflyer]

Could you explain better please ?

 

[arifanmol]

i will find out cell address
then suppose it is A15
then i wlll write =sum(A15:A30)
as i dont know what is the cell address where text 'Total' is written

 

[pecoflyer]

By using an INDEX/MATCH combination, the cell is referenced without having to go through the ADDRESS function.
See attached for a simple example
It looks like this =SUM(INDEX($B$1:$B$6,MATCH($A4,$A$1:$A$6,0)):$B$6)

 

[arifanmol]

but i want cell adress as i dont know where my preferred text is lying

 

[pecoflyer]

Again, the INDEX/MATCH combination will find and reference it. When embedded in a formula this combination returns a cell reference
Now if you want to makes things difficult see https://trumpexcel.com/return-cell-address-instead-of-value-excel/ ( which does the same adding an unnecessary step)

 

[arifanmol]

Yes got it now solved thanks a lot sir