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


​

 

[yrpsoa]

There is - but I also think there may have been some confusion... I thought you meant use find and replace>replace all (which is obviously a very different thing) because I am using excel for OSX which doesn't have the option for select all. I can see that, did I have that function, it would work quite well. Unfortunately, as mine does not have this feature, I will have to keep looking...

 

[footoo]

I am able to use relative refs in named formulas, so can use $J2 instead of INDIRECT("J" & ROW()).

Are you sure you cannot use relative refs on your system?
If when creating the named formula it defaults to absolute refs, try editing to relative refs.

 

[yrpsoa]

I can use relative formulas - i just can't use the 'Find All' Function. I tried it out on someone else's windows machine and it worked perfectly but it definitely isn't an option on OSX.

 

[footoo]

I can use relative formulas - i just can't use the 'Find All' Function. I tried it out on someone else's windows machine and it worked perfectly but it definitely isn't an option on OSX.

I meant using relative refs in the named formula instead of INDIRECT and ROW.

 

[yrpsoa]

Its sort of working but I get an offset i.e. row 16 references the cell on row 4, row 17 -> row 5 etc.

 

[RoryA]

When you define the named formula, the relative parts of the formula (row and/or column) will be relative to whatever cell is active at the time you create the named formula. So if you had a cell in row 17 selected when defining a named formula that refers to $J5 for example, your named formula is effectively referring to a cell in column J and 15 rows above wherever you use the named formula. If you want to refer to the same row, select a cell in row 5 and then create the named formula using $J5 - or in other words use whatever row number the active cell is in.

 

[yrpsoa]

Problem solved! You're a lifesaver! Do you know how you make the worksheet reference relative as well/ remove it?

Do you just change the scope from workbook to sheet?

 

[RoryA]

Just leave out the worksheet name but prefix the references with an exclamation mark. So use:

!A1

rather than :

Sheetname!A1

 

[yrpsoa]

Thanks so much guys