Accessing data from a specified worksheet based on a cell reference

[Paid 2 Heat]

Good afternoon,

I have a workbook containing many worksheets (20+). Each worksheet contains solar irradiance data based on given postcode regions within the UK.

The lookups for each postcode region are based on: angle of roof (column A) and deviation from South (Row 1). This intersection lookup is not a problem.

I should like to find a solution so that, for example, if the UK postcode was entered as NE - ie Newcastle - the system would perform the intersection lookup in that particular worksheet only.

If I used independent named ranges for each of the 20+ postcode data sets, would a CHOOSE() work, based on the postcode cell reference in the data-entry worksheet?

Thank you in advance for your assistance.

 

[Somendra Misra]

Hi,

Welcome to chandoo.org forum.

Although you have not attached a sample file. I think INDIRECT function can help you in this regard.

Try uploading a sample file with say 3 sheets of dummy data and output sheet.

Regards,

 

[NARAYANK991]

Hi ,

Try something like this :

=INDEX(INDIRECT("'" & Postcode & "'" & "!" & "$B$2:$Z$100"), MATCH(AngleofRoof, INDIRECT("'" & Postcode & "'" & "!" & "$A$2:$A$100"), 0), MATCH(Deviation, INDIRECT("'" & Postcode & "'" & "!" & "$B$1:$Z$1"), 0))

where each worksheet should have data in the range $B$2:$Z$100 ; change this to suit your data. The range $A$2:$A$100 will have the roof angles , while the range $B$1:$Z$1 will have the deviation values.

Postcode is the selected postcode , AngleofRoof is the selected roof angle , while Deviation is the selected deviation value.

Narayan

 

[Paid 2 Heat]

Thanks Somendra

Please find attached sample file.

In short, the cell D4 in the Data Entry sheet dictates which sheet I'm using to perform the intersection lookup.

Thanks again

 

[Somendra Misra]

See this file.

Regards,

 

[Paid 2 Heat]

Somendra

Thank you! Perfect!

Am going to tweak as Narayan has suggested with named ranges.

Nevertheless, thank you both for your help - it's very much appreciated