Solved: need help to create a "database" in excell

[Ger]

I'm using Excell 2003 dutch version.


With the help of this forum (in particular XLD) i made some sheets with VBA macro's. Now i'm trying to make a kind of database ( i don't have access) in Excell.
What i need is:
I have the next data: Naam (name), plnr (unique, 6 numbers), pm (=a name) ivw (=date), begl 1 to begl 24 (=date). IVW and begl are plannend (planning) en realised (realisatie).
What i need is a sheet (input) in which you can insert the naam, plnr, pm and the plannend and realised date. The key field is plnr (this is unique) .The data must be saved in the sheets planning or realisatie. If there is already data it must be displayed in input (for change or supplement).
To get a full view per PM (= manager) i need a sheet (output) in which all data of the personal of that pm is viewed ( what i mean is that you type the name of the pm in the sheet output cell B2)

See the (empty) example.

As a simple user of excell i don't know how to set up this thing.

Plaese help

[Bob Phillips]

Ger,

You don't need Access, that is just a (sloppy) front-end. The Jet engine is available to you reagrdless, and you can create tables, update them, read them ireepsective of having Access.

[Ger]

Xld,

are you able to help me.

Ger

[Bob Phillips]

I can. Do you want to go down a real database road?

[Ger]

if it is possible in the spreadsheet i attached (using VBA) its fine for me.

Ger

[Bob Phillips]

What is the relevance of planning and real, they look the same to me?

[Ger]

The PM (= managers) makes a plan (= planning) on which date he will accompany a staff member in 2009. The date he really accompanied the staff member is put in real (realisation). So if he planned a date and not realised it he sees that he must plan a new date.

Ger

[Bob Phillips]

How is this?

[Ger]

it looks good. how do i get output for a new added pm? i don't see him in the list?
How do i add new naam and plnr?

I think i found it. It is in resources? If correct also the "plnr" and "pm" needs to be in resources, because "plnr" is the unique key and can have only 1 "naam" and 1 "pm".
By typing the "plnr" the value "naam" and "pm" can be retrieved.


Ger

[Bob Phillips]

Just type the PM name on the pms sheet, and it will appear in the input DV list, and will appear in the pivot table after you refresh it. Similalrly with Naams.

[Ger]

Oke, now it works (the output).
But on the input you have not assigned the plnr to naam and pm.
Is it possible to put those 3 in resources and than to select either plnr or name in input?


Ger

[Bob Phillips]

Sorry, I am not sure what you mean.

[Ger]

Every member has an unique plnr and only 1 pm (manager)
example
plnr naam pm
123456 Janssen Alf Pieterse
234567 Jansen Bart Jacobs
345678 Cornelissen Piet Pieterse
456789 Derksen Klaas Vlokje
there a 500 plnr and 500 names (members) and 14 pm (managers)

The naam (name) can occurs many times (we've got 5x Janssen, 3x Jansen but they have a unique plnr).

If it is possible to select a person on plnr or naam in the input sheet it will make it easier to find the right person.

Ger

[Ger]

Xld,

is what i ask above possible to make?

Ger

[Bob Phillips]

So what exactly do you want to happen other than what is there now?

[Ger]

If possible on sheet resources: plnr , naam and pm
on sheet input: possibility to select either plnr or name using pulldown menu (if possible after selecting pm)

example: pm: 1
than you see all the plnr or names (depending on the key you select) in the pull down menu of that pm.

the output is perfect.

Ger

[Ger]

Why the plnr, naam and pm in resources: - I can retrieve them from SAP HR so the manager doesn't have to insert this things (and make mistakes), and the input is the same for everyone.
The manager only has to insert the dates for ivw and begl 1 etc.
As the manager can select himself in the input sheet and he only sees his own members the list is shorter to select from. Sometimes the managers doesn't know the plnr (and is to lazy to search) than he can select by name.

Ger

[Ger]

Xld,

i tried to change the selection buttons to a new range in resources but i can find out how you defined them.

Can you please help me?

Ger

[Ger]

as i can't find out how to configure to select either the naam or plnr i just select on naam.
Now i don't know how to get macro 4 to work as the value in sheet input cell c3 is changed.

see attachement.


Ger