Simple address data and 3 sheets

users
#1

I am trying to keep a spreadsheet file as simple as possible for a group of musicians who have very little to no knowledge of how a spreadsheet functions.

So, in the spreadsheet, I have:

* Sheet1: has a list of names, in a column, that will change monthly describing how long each musician played in a second column. A musician "general manager" would input the musicians names once a month. This list would change every month and would have different combinations of names depending on whether they were hired to perform or not.

* Sheet2: has a column where it lists the musicians' names who performed by referencing the corresponding name cells to the Sheet1 name cells, BUT, Sheet2 has to have their addresses show in a set of cells that mimics an address book (similar to Thunderbird address book)

* Sheet3: has ALL the lists of the musicians' names, addresses laid out like an address book similar to Thunderbird (simple to put together for any user)

I was wondering if there was an elegant way of doing the following:

Sheet2 would need to find the corresponding name from each name cell on Sheet1 and then look up the correct name on Sheet3 simple address book. The musicians data in the Sheet3 address book would then fill in the cells on Sheet2

My solution thus far:

* create a series of "if" arguments that would check each name in the name cell in Sheet2 until it finds the same name and then, once the "if" argument is satisfied, would then fetch the address data from Sheet3 simple address book and populate the cells on Sheet2

My question is ... Is there another simpler or more elegant way of accomplishing this process? Is there another argument I could use to do this?

Thanks for the help.

Marc

#2

Hi,
First of all, no professional IT person would do in a spreadsheet what you
perfectly describe as a relational database.
VLOOKUP is the key to mis-use a spreadsheet as a relational database. First
you have to ensure that Tools>Options>Calc>Calculate: "Enable Regular
Expressions" is OFF and "Match whole cell" is turned ON. These are per
document settings.

#3

Hi :slight_smile:
Base documentation is improving significantly. Even the older version of
the handbook might be helpful;
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook

Don't forget that you can use Writer or Calc as the front-front-ends so
that normal users don't see any of the under-laying Base design.
Regards from
Tom :slight_smile:

#4

Thanks! I'll have a look.

Luckily, I am not a professional IT person :slight_smile: and am not looking for recognition as such. Just a quick simple fix to a bookkeeping process.

Thanks for help, much appreciated.

Marc