Indirect cell addressing

Topic

New Reply

Dear loungers,

I have a spreadsheet where I want to construct an address that has the data I want to retrieve.

This is the address: ‘Missing Fields 16 July’$E$4
This is constructed like this if I wanted it as a string: =”‘”&PreviousTabName&”‘”&ADDRESS(ROW(),COLUMN())

If I just wrap INDIRECT round this it doesn’t like it, which may be to do with where I put the quotation marks. The simple formula =INDIRECT(“E4”) works but substituting the formula above for E$ and removing the double quotes in the formula just produced a #REF error.

What do i need to do?

thank you………………………………….. liz

Reply | Quote

Replies

Liz,

You didn’t separate the sheet name from the cells with an Exclamation Point. “!”

[noparse]=”‘”&PreviousTabName&”!'”&ADDRESS(ROW(),COLUMN() )[/noparse]

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Retired Geek,

How annoying obvious! I was going blind having stared at it for so long. Thank you

My problem then was how to wrap INDIRECT around it, I had a problem with quotation marks. Partly because I thought I had to have single quotation marks round the tab name – I removed embedded spaces and then was OK.

For anything with this problem here is the solution:

[INDENT]=INDIRECT(PreviousTabName&”!”&ADDRESS(ROW(),COLUMN( ) ))[/INDENT]

liz

Reply | Quote

If you have spaces in the tab name then you do need the quotes – I usually add them for safety anyway in case things change

=INDIRECT(“‘”&PreviousTabName&”‘!”&ADDRESS(ROW(),COLUMN ( ) ))

Reply | Quote