Formula: chose data from different tables based on cell selection

Alecs 

this one is too complex for me. Need your help

I'm going to speak just for myself, here, but I suspect that others may share the sentiment. That may well be why after nearly 50 views of your appeal for help, you've had no replies. There are many of us here who love to help people (like you) figure out how to get from their raw data to the end product they need.

In your case, the elaborate color coding and multiple sheets actually serves to confuse me (and, I suspect, others). And your verbal descriptions of those sheets adds to the confusion more than it clarifies. (I DO realize you're doing your best to try to make things clear, so please excuse me for questioning what you've done)

I was the director of the HR and payroll database for a major corporation before I retired, so I do have experience dealing with multiple locations, people, pay, etc.  You describe this as a "rental spreadsheet" yet at the bottom line (in the TOTAL sheet) it appears more to be about payment to people (Admin 1, 2, etc). I'm hoping you can clarify this apparent discrepancy.

What I'd also like you to do is put the spreadsheet aside. Over there on that table, out of sight.

Now describe in words, words only, what the raw data is (uncalculated) (the Input) (such things as):

• people
• roles
• places
• pay rates (if any, and how related to the above)
• relationships (if any) between Persons and Admins and Locations
• the assignments on Sheet1: you say "done manually" but is the expectation that they'll continue to be selected manually, or are you expecting a computer function or algorithm to make those entries? If so, what are the rules
• what informs the euro amounts that appear in Sheet 2's many boxes (is there an hourly rate somewhere, or does all that get entered as raw data with no table or set of "salaries" or "wages" behind it?)

And then, when all is said and done, what is the desired Output? Is it simply the data in this Sum column,

along with the corresponding identifiers in the Admin column? 

Said another way, are all of the intervening columns in "final table: TOTAL" just details, stepping stones on the way to the SUM? Useful backup, perhaps.

I ask all this not to be a pain, but because my sense is that you're getting too far into the weeds too early, trying to design the spreadsheet based on how, say, one might have handled all the moving parts by means of paper ledger sheets. Yes, it IS complex when you lay it all out this way, but if we can step back from the weeds and just look at Input and Output, that might help us come up with simpler ways to handle the intervening steps.

I'm a firm believer in building a solid (and usually fairly simple) database (that's at the input end of things) and then letting Excel work its wonders on that database, adding payments per day per person per location over the period between day 1 and day 14. Excel can do that without visibly arraying the data as you've done--that kind of layout can, and often does, actually interfere with Excel's power to summarize data and produce the Output.

So, for example, a database that simply records, daily (or half-daily)

Date, Who, Where, Amount, (etc, other raw data as needed)

row after row after row

can be summarized by the feature known as the Pivot Table, by person, by day, with totals. Straight from transactional database (Input) to end result (OutPut) all by itself.

If, in other words, it is the case that your sheets 1 and sheet 2 represent raw data, generated on a transactional basis, you don't need to be filling in a ledger sheet (albeit a computerized ledger sheet); just record the raw data in a database or data table. Excel can handle the rest.

Again, forgive me for asking these. If you don't want to engage as I've suggested, that's fine...... I'll defer to others.