Re: Lookups across multiple sheets

[Mark Verboom <mark verboom net>]

Hi Oliver,

On Fri, May 27, 2011 at 07:54:48AM +0100, Oliver Burnett-Hall wrote:
> Why are you putting the source data into multiple sheets?  Is it
> possible to revisit your design so that this data all goes into a single
> location?

They are seperate datasets that need to be maintained seperately. They
grow over time, I need room on each sheet to expand them downwards
for an unknown amount of lines. So it made sense to put them on
different sheets.

I read some article about this problem in excel where they did something
with array formula's and lookups. The formula was:

=VLOOKUP(C2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A20"),C2)>0),0))&"'!A1:B20"),2,0)

where C2 contains the value to be found and MySheets is name pointing to
a range with the names of the sheets. But it wasn't something I could
get working in gnumeric.

Mark
-- 
The more things change, the more they stay the same.