get properties of current cell e.g cell

[yrpsoa]

No-one ever seems to get anywhere with this so I think I must be missing something obvious.
I need get a reference for a specific column and the current row (AAx). I've currently used INDIRECT() but I know thats quite computationally inefficient and I need about 10,000 instances of this formula - each with 15-20 references:

=IF(ISNUMBER(SEARCH("NB", INDIRECT("AA" & ROW()) )),0,
IF(ISNUMBER(SEARCH("FB", INDIRECT("AA" & ROW()) )),350,
IF(OR(INDIRECT("P" & ROW())=5,
INDIRECT("P" & ROW())=6,
INDIRECT("P" & ROW())=7), 0.5,
IF(INDIRECT("P" & ROW())=4, 0.4,
IF(INDIRECT("P" & ROW())=3, 0.3,
IF(INDIRECT("P" & ROW())=2, 0.25,
IF(ISNUMBER(SEARCH("PM1", INDIRECT("O" & ROW()) )), 0.12,
IF(ISNUMBER(SEARCH("FMG", INDIRECT("O" & ROW()) )), 0.12,
IF(ISNUMBER(SEARCH("FMH", INDIRECT("O" & ROW()) )), 0.12,
IF(ISNUMBER(SEARCH("TAA", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("TAB", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("TAC", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("TBC", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("1A", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("1B", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("1C", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("1D", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("1E", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("1F", INDIRECT("O" & ROW()) )), 0.06,
IF(ISNUMBER(SEARCH("LF1", INDIRECT("O" & ROW()) )),350,
IF(ISNUMBER(SEARCH("LW", INDIRECT("O" & ROW()) )),350,
IF(ISNUMBER(SEARCH("LC", INDIRECT("O" & ROW()) )),350,
IF(ISNUMBER(SEARCH("LT", INDIRECT("O" & ROW()) )),350,
IF(ISNUMBER(SEARCH("L4A", INDIRECT("O" & ROW()) )),350,
IF(ISNUMBER(SEARCH("ZIP", INDIRECT("O" & ROW()) )), 0,
IF(ISNUMBER(SEARCH(“BC”, INDIRECT("J" & ROW()) )), 350,
""
))))))))))))))))))))))))))

I'm basically looking for a replacement for INDIRECT()

Thanks in advance


​

 

[footoo]

Why do need to use INDIRECT and ROW ?

For example, if your formula is in row 2, instead of : INDIRECT("J" & ROW())
can you not use instead : $J2

 

[yrpsoa]

The problem is the formula needs updating quite frequently which at the moment means I have to find every instance of it and change it which is quite a lot of work. So I wanted to have one instance of it in Name Manager that i can update that can be applied to all cells with no hard-coded reference to row.

There's probably a much simpler way of doing this that I've probably missed so I'm open to suggestions.

Thanks

 

[footoo]

If you use refs like $J2, instead of INDIRECT and ROW in a named formula, when you need to change the formula the cells can be located and selected via Find and changed in one entry with Ctrl+Enter.

In the Find box, 'Find What' could be for example "=IF(ISNUMBER", 'Look in:Formulas', Find All'.
Press Ctrl+A to select all the cells, amend the formula, press Ctrl+Enter

 

[yrpsoa]

I'm not sure that works because there's so many instances of IF(ISNUMBER in each...

 

[footoo]

It will work provided you do not select 'Match entire cell contents' in the Find box.

If you are not sure whether something will work or not, the easiest way to find out is to try it.
The steps I suggested are few and simple (probably quicker than it took you to post again)

 

[yrpsoa]

When I said I'm not sure, that was polite speak for 'that's not the solution I'm looking for'. I wasn't unsure. It just seems like a rather untidy solution and your condescension only demeaned your response.

 

[yrpsoa]

It seems I can only apologise - I've tried to delete my previous comment but there doesn't appear to be the option. It was a gut reaction through frustration and exasperation with my problem that got taken out on you who were trying to help. Please accept my apology.

 

[footoo]

Sorry, my mind reading abilities are poor - I should have known "I'm not sure that works" meant, in fact, that you were "not unsure" and "that's not the solution I'm looking for".
My suggestion does not seem any more untidy than having to change your named formula, and it eliminates INDIRECT.
I hope you find something suitable to you.

 

[footoo]

No need for apology. It's not a problem.