using address from ADDRESS()

[MrFox]

I atempting to expand the fucntionality of the folllowing forumla by using the address() worksheet function.

Please Login or Register  to view this content.

I have replaced the "Tables!$H$4:$Q$24" in the above formula with the following ...

Please Login or Register  to view this content.

Although the ADDRESS formula DOES return the correct address - "Tables!$H$4:$Q$24" - when placed in a cell by itself, it gives me a VALUE error when used as a replacement to Tables!$H$4:$Q$24 in the first formula.

Is the result from the ADDRESS function a reference or is it simply a string type?

Thansk for the help.

Edit:
Oh yeah, WHY am I doing this?
I need to reference one of several tables for the results. The table to be referenced is determinded by the value in MAIN!$B$12. Each table has the same number of rows and columns and are situated vertically with a few rows in between. This should have no affect of the results. The test forumla is referencing the first table anyway and should work as intended.

[ConneXionLost]

Wrap your ADDRESS formula with INDIRECT() like this:

Please Login or Register  to view this content.

Then try it in your first formula.

Cheers,

[DonkeyOte]

By adding the INDIRECT call (to utilise the ADDRESS) you are making the function Volatile.

You might find you are better off using INDEX if as inferred each table has pre-determined (and consistent) dimensions.

Perhaps then:

=INDEX(INDEX(Tables!$H:$H,MATCH(MAIN!$B$12,Tables!$H:$H,0)+1):INDEX($Q:$Q,MATCH(MAIN!$B$12,Tables!$H:$H,0)+21),MIN(20,VALUE(MAIN!$AT$11))+1,MATCH(CP30,Tables!$H$4:$Q$4))

[MrFox]

Thanks for both answers. Good stuff.