Apache OpenOffice Community Forum

hello im using office calc and i have 2 spreadsheets on first spreadsheet i have in cells A5 to A35 the name ALEX in A36 to A66 the name JOHN in A67 to A97 the name VICKY and so on, in cell B5 to B35 the date correspondingto a certain month then from B36 to B66 tha same dates . Now on the 2nd spreadsheet in cell C3 i have the name ALEX in cell E3 the name Vicky in cell G3 the name JOHN and so on, and in B4 to B34 i have the dates corresponding to the dates for certain month. Now what i am trying to do is on the spreadsheet 2 in the cell C4 to get the content of cell E# from spreadsheet 1 where the name of C cell of spreadsheet 2 is the same with the spreadsheet 1 and have the same date, or at least to get the ROW number of spreadsheet 1.

Any ideas how may achieve that ?

thanks in Advance

It is far easier to help you if you upload a real file, not an image.
If I have understood the pattern of your data on the first sheet correctly, I would use a method like the one demonstrated in the attached file. The assumed pattern is January data Alex; January data John; January data Vicky; February Data Alex; February Data John etc. I only constructed data through April in columns A:C

The formulas in columns F, H and J return values from column C. The table in L2:P14 provides an offset to the INDEX function that depends on the month and the person.

FJCC wrote:It is far easier to help you if you upload a real file, not an image.
If I have understood the pattern of your data on the first sheet correctly, I would use a method like the one demonstrated in the attached file. The assumed pattern is January data Alex; January data John; January data Vicky; February Data Alex; February Data John etc. I only constructed data through April in columns A:C

The formulas in columns F, H and J return values from column C. The table in L2:P14 provides an offset to the INDEX function that depends on the month and the person.

here are the files i have..