CODEWITHSUNDEEP
openoffice-calcnamed-ranges
IshanAg24
IshanAg24

Reputation: 199

Using range names as cell source

I've been trying to work on this thing in OpenOffice Calc, where I have two drop lists in a spreadsheet, in which data is coming from other columns. I've attached a snapshot for better understanding.

Example

Cells A1-A3 are represented by named-range countries
Cells B2-B3 are represented by name-range country1.

Named-ranges country2 and country3 represent B5-B6 and B8-B9 respectively.

Cell A11 is a drop down list with range 'countries' as source so that it contains values : INDIA, ENGLAND ,AUSTRALIA.

Cell B11 is another drop down list whose values are to be determined by the value chosen in A11 i.e. B11 is dependant on A11. As an example, if I choose 'England', then values in B11 must contain IJ, KL. In other words, a named-range has to be set as source for B11 depending on value from A11.

Now the problem is, if I directly set the source of B11 with some named-range, say country1, it's successfully showing the values. I've shown the same here:

successfully showing the values

but when I'm retrieving the same range name using formula, it's not showing the range. Rather it just sets the name of the range as value, as shown here

not showing the range

The formula I've used for obtaining a range for source is:

CONCATENATE("country" & MATCH(A11;countries;0))

Can someone explain why is it happening?

Upvotes: 2

Views: 1733

Answers (1)

Lyrl
Lyrl

Reputation: 935

You have to wrap the formula in an INDIRECT to get Calc to evaluate the range name.

As a side note, the & has the same function as CONCATENATE, there's no need to use both. So either of these formulas will work: 

INDIRECT("country" & MATCH(A11;countries;0))
INDIRECT(CONCATENATE("country";MATCH(A11;countries;0)))

Upvotes: 3

Related Questions