Tranferring from a list to a two parameter table

**f0r3st** · 05-12-2009, 09:44 PM

Hi, I have a problem that I have tried figuring out but to no avail. I need a formula that will fill every cell within the area of the tables.

The problem is this: I need a two parameters table deriving data from a list in another sheet. The vertical column of the two parameters table will be the names of the personnel, and the horizontal one will be the dates, in weeks form. The list contains the names, flight departure, flight arrival and no. of working days spent in the foreign country. What I need to do is to tranferr the data from that list onto the table.

For eg, if john flew overseas on 20-Dec to 15-Jan, the list will show john on column A, 20-Dec in column B, 15-Jan in column C and 20 working days on column D.

This list of personnel and their data should go into the two parameters table, where it will be identified when the personnel will be overseas according to the calendar of the project. Since the vertical heading is the dates of every week, a date that corresponds to period where the personnel is overseas will reflect a number in the cell. The number will be the number of working days the personnel spent on the foreign country, therefore the max will be a 5 and minimum is a 0. I need a formula that will fill every cell within the area of the tables.

I've attached a file with 2 sheets. first sheet is the data list named 'list of overseas trip' and second sheet is the 'table-weekly basis' that i need. Thank you very much.

**f0r3st** · 05-12-2009, 10:58 PM

Btw, I hope that it will be a formula and not use pivot table as there are various other tables in the sheet as well and I want to standardize how the tables appear

**DonkeyOte** · 05-13-2009, 05:55 AM

IMHO you need to re-think your approach... trying to populate the matrix on another sheet away from the list will require copious amounts of SUMPRODUCT formulae which will lead to v poor performance... I would propose you create the same matrix adjacent to the list and then run standard SUMIF from your summation sheet -- see attached -- this will be far more efficient.

**f0r3st** · 05-13-2009, 09:16 PM

great, but I would require another table that shows all the activity of the personnel in 1 line, from the example that you sent me, I would require john's activites to be summarized in 1 line rather than 2. In the real list that I'm using, most people will go overseas more than once, so i will need all the data of everyone to be in a single row. Is that possible?