On Thu, 2011-05-26 at 16:35 +0200, Mark Verboom wrote: > Hi, > > I've recently been dyving a bit deeper into working with gnumeric and > I've ran into a problem I can't seem to solve. > > I've got a spreadsheet with multiple pages. Each page contains two > colums (A1:B100), where the first column is a unique number and the > second is a date. On a seperate summery page I want to be able to type > in one of the unique numbers and get the date that is next to it in one > of the other sheets to appear in a cell. > > The only way I've found to do this is to make the very ugly construct > with nested if's (where A6 is the cell I type in the unique number): > > =if(isna(vlookup(A6,sheet1!A11:B100,2,0)), > if(isna(vlookup(A6,sheet2!A11:B100,2,0)), > "Unknown", > vlookup(A6,sheet2!A11:B100,2,0)), > vlookup(A6,sheet1!A11:B100,2,0)) > > This doesn't scale very well when using 7 or 8 sheets to lookup values > and is pretty much hell to maintain :) > > Does anyone know a nice way to solve this? > > Thanks, > > Mark
I am not quite sure what your conditions are but something like this: =index(array(Sheet1!B:B,Sheet2!B:B),1+vlookup(C1,array(Sheet1! A:A,Sheet2!A:A),1,FALSE,TRUE)) In this case C1 holds what you are searching for should be reasonably easy to maintain even for 7 or 8 sheets since nothing needs to be changed if you add data rows but maybe I am missing something (the above assumes that you are not searching for empty values). Andreas -- Andreas J. Guelzow, PhD, FTICA Concordia University College of Alberta _______________________________________________ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
