Tabelle collegate inserimento riga automatica - Unione celle foglio con formule

Buona giornata,
complimenti per il forum, veramente bello, interessante ed efficiente.


Ho creato una cartella Excel, che allego, con dei fogli di calcolo per la gestione di un piccolo hotel/residence dove sto lavorando.
In particolare, tra gli altri, ci sono i seguenti tre fogli:
- Prenotazioni dove ho impostato una Tabella "Prenotazioni" per
inserire i dati delle prenotazioni;
- Registrazione dove ho impostato una Tabella "Registrazione" ed
inserisco all'arrivo dei clienti altri dati che mi servono;
- Quadro A3 Esteso che fornisce una rappresentazione temporale
grafica delle varie prenotazioni.

Ciò premesso ho due problemi che non riesco a risolvere:

PRIMO PROBLEMA:
Le Tabelle Prenotazioni e Registrazione sono e devono rimanere collegate in quanto se ordino la prima per data di arrivo o per qualsiasi altro campo i dati da me inseriti nella Tabella Registrazione devono avere una corrispondenza biunivoca ad una data prenotazione presente nella prima Tabella e viceversa.
Per fare ciò ho collegato le due Tabelle con un campo denominato "Controllo" presente in entrambe e contenente la data e l'ora (io ora uso la funzione Now del VBA) della prima compilazione delle varie celle contenute nella riga, detto dato non è e non deve essere successivamente suscettibile di variazioni in quanto rappresenta il campo chiave del collegamento.
Per inserire una nuova prenotazione mi posiziono sull'ultima cella dell'ultima riga della Tabella Prenotazioni e premendo TAB Excel crea una nuova riga a questo punto vorrei (e non riesco ad ottenere per cui chiedo il vostro aiuto) che:
a) venisse inserito in automatico nella cella della nuova riga
della colonna "Controllo" della Tabella Prenotazioni la data
e l'ora attuali (ora io uso la funzione Now), tale dato deve
essere non più successivamente modificabile in quanto
rappresenta il collegamento con la Tabella Registrazione;
b) venisse creata in automatico una nuova riga anche nella Tabella
Registrazione, mantenendo le ventuali varie formule presenti in
alcune celle;
c) venisse copiato il dato della cella Controllo della Tabella
Prenotazioni nella cella Controllo della riga creata nella
Tabella Registrazione, anche questo dato non deve essere più
successivamente modificabile (ora questo lo faccio
manualmente);
d) che in tutte le celle vuote (eccetto quelle con formule, con
elenchi a discesa o per qualsiasi motivo già compilate in
automatico) di entrambe le tabelle, fosse inserito il valore 0.

SECONDO PROBLEMA
Nel foglio "Quadro A3 Esteso" che contiene la rappresentazione temporale grafica delle varie prenotazioni e che attualmente si aggiorna e/o si varia in automatico all'inserimento, alla variazione o alla cancellazione di una data prenotazione, vorrei che le celle di una stessa prenotazione risultassero unite in modo da poter leggere il Cognome ed il Nome del cliente e non come è adesso che viene scritto il Cognome in ogni cella.
Logicamente se viene inserita, variata (nr. stanza o date) o cancellata una prenotazione nella Tabella Prenotazioni la stessa cosa deve ripercuotersi anche nel foglio Quadro A3 Esteso.
Ho provato a cercare ma non ho trovato funzioni di Excel che uniscano celle al verificarsi di qualche condizione.

Spero di essere stato esauriente e chiaro, rimango a disposizione per qualsiasi chiarimento.

Vi ringrazio fin d'ora per quanto potrete fare.

Un saluto,

Luca

ciao
richiesta alquanto articolata.

1)"....Per inserire una nuova prenotazione mi posiziono sull'ultima cella dell'ultima riga della Tabella Prenotazioni e premendo TAB Excel crea una nuova riga...."

spiega meglio, ti posizioni sull'ultima riga (quella vuota) e premi TAB. Ma è il tasto della tastiera sopra lo shift?? Io ho provato ma non succede nulla, solo il cursore si sposta a destra. Forse utilizzi il tasto destro--->inserisci riga ?

Chiarito questo, per il resto non dovrebbero sorgere problemi.

2) SECONDO PROBLEMA
qui la vedo assai dura; non puoi unire le celle in quanto salterebbero tutte le formule tranne la prima.
Bisogna pensare a qualche altra cosa, tipo l'inserimento di un commento in ciascuna cella del sig. X.
Ma è cosa un po' complessa e, comunque da gestire con macro.

Fai sapere

saluti

Ciao Dodo,

Chiarisco e rispondo ai tuoi quesiti:

1) PRIMO PROBLEMA:
Per TAB intendo il tasto tabulatore quello con due frecce contrapposte di cui ti allego una foto (nella mia tastiera è a sinistra sopra il tasto Bloc Maius in altre tastiere non so dove sia collocato).
Nel foglio di prova che ho allegato nel precedente post quando mi colloco nella cella AF11 che è l'ultima cella dell'ultima riga della tabella Prenotazioni e premo il tasto TAB di cui alla foto allegata, Excel in automatico, senza alcun mio intervento, crea una nuova riga in cui sono già riportate tutte le formule della precedente, mentre il primo campo "Controllo" si "auto-compila" solo quando vado ad inserire il nr. di appartamento (per quest'ultimo evento invece è stata fatta una macro ad hoc).

2) SECONDO PROBLEMA:
Penso anch'io che lo si risolve appunto con una macro in VBA (che non ho la competenza sufficiente per scrivere) che a seconda dei casi vada ad unire o separare le celle tra la data d'inizio e di fine della prenotazione e quindi inserisca/modifichi/cancelli/ il cognome e nome dell'ospite.

Rimango fiducioso in attesa di possibili soluzioni.

Luca

Ok.

Faccio un piccolo riepilogo.

- nel momento che inserisci una riga utilizzando il tuo sistema:
1) nella colonna A (salvaguardando il tuo formato) e nella col. E viene inserita la data del momento statica; in tutti gli altri campi (salvaguardando le formule) si deve visualizzare 0. Ti chiedo: sei sicuro di voler vedere 0 in tutti gli altri campi ??

Contemporaneamente verrà inserita una riga in Registrazione, riportando i valori di Prenotazione.
NOTA: poichè tutti i valori di Registrazione puntano a Prenotazioni, non mi sembra il caso di far apparire 0 (zero) nei campi registrazione tipo: Adulti - Esenti.

Se posso più tardi ti invio una prova (senza gli zeri a meno che le formule non diano errore), altrimenti domani

saluti

Ciao
ecco la prova. Ho implementato la sub worksheetChange

saluti

Ciao Dodo,

scusa ma non vedo la prova che hai fatto, forse ti sei dimenticato di allegare il file da te modificato?

Luca

non so perchè non la carica. Sono due volte che ci provo.

Te la invio a minuti su hosting esterno

http://www.filedropper.com/prenot


per il foglio quadro A3 ci sto lavorando

Fai sapere

Ciao Dodo,

ho scaricato il file da te modificato e tutto sembra funzionare, ti ringrazio tantissimo.

Ora rimane da risolvere solo il primo problema quello dell'unione celle del foglio Quadro A3 esteso ma so che ci stai già lavorando ....
Per raggiungere il risultato ritieniti libero di modificare/cancellare le formule in esso contenute, fare tutto tramite VBA, ecc., insomma fa pure quello che ritieni sia meglio.


Ti ringrazio ancora, un abbraccio e un saluto,

Luca

Ciao Dodo,

ho scaricato il file da te modificato e tutto sembra funzionare, ti ringrazio tantissimo.

Ora rimane da risolvere solo il primo problema quello dell'unione celle del foglio Quadro A3 esteso ma so che ci stai già lavorando ....
Per raggiungere il risultato ritieniti libero di modificare/cancellare le formule in esso contenute, fare tutto tramite VBA, ecc., insomma fa pure quello che ritieni sia meglio.


Ti ringrazio ancora, un abbraccio e un saluto,

Luca

ciao
allego il file utilizzando un sito hosting esterno.
Tieni presente che tale sito mantiene il file per un po' di tempo (non so quanto) e poi lo cancella.

Nel caso dimmelo.

Naturalmente ho eliminato tutte le formule nel foglio A3... ed ho utilizzato una macro inserita nell'evento "Activate" del foglio stesso.

Pertanto ogni volta che selezioni tale foglio, lo stesso si aggiorna.

Se hai spiegazioni da chiedere sono qui.

Fai i tuoi controlli

saluti

http://www.filedropper.com/prenot2

Buona sera Domenico,

ti ringrazio per quanto hai fatto, gran lavoro, siamo a buon punto del problema 1) ma ci sono solo degli aggiustamenti da fare in merito alla variazione ad esempio del nr. di appartamento, delle date di inizio o fine e in caso di cancellazione: ho notato che il variare di questi dati non si ripercuote correttamente nel foglio.
In particolare ad esempio quanto metto la prenotazione a "Cancellata" nel prospetto grafico le celle rimangono unite, oppure se vario la data finale in diminuzione non succede nulla cioè rimangono evidenziate le date di prima, se vario l'appartamento le celle di quello precedente rimangono unite, se vario la data iniziale in aumento le celle relative rimangono bianche e così via.

Se vuoi prova a giocare anche tu variando date, appartamenti e cancellando prenotazioni e vedi che succede.

Un abbarrcio,

Luca

ciao

vedi se mettendo:

Range("C8:EY25").UnMerge

prima del ciclo:

For i = 3 To ur

gli errori vanno via tutti o resta qualche altra cosa.

Nota:
- c'è nel vb il modulo1 che puoi eliminare, mi è servito per alcune prove
- in A1 di Quadro A3... c'è una formula da cancellare

saluti

EDIT: mi sono accorto solo ora (non me ne avei parlato) che il foglio Occupazione era legato al foglio A3...
vedo cosa fare.

Ciao
ho rivisto un po' il tutto sia la sub su evento change che creava problemi sia il resto.

Ho spostato in un modulo la sub originaria chiamandola Aggiorna che dovrebbe ora aggiornare entrambi i fogli Quadro....e Occupazione.

NOTA: Nel foglio Quadro... ho dovuto limitare l'evidenziazione della giornata del sabato alle solo righe iniziali (dove ci sono le date), questo perchè se un cliente prenota a partire dal sabato, unendo le celle ed essendo la prima gialla in quanto legata alla formattazione condizionale, la fascia d'occupazione, diventava tutta gialla e non verde. Spero sia chiaro.

Ho naturalmente tolto le formule anche nel foglio Occupazione, ma non sono riuscito a capire quando ci va la X

Saluti

File

Ciao Domenico,

ho visto il file, mi sembra che tutto funzioni e vada benissimo; ho fatto solo un paio di integrazioni al codice:
1) cambia colore in base allo stato della prenotazione;
2) aggiunge anche il nome per prenotazioni superiore ai 7 giorni.

Cotesemente, se vuoi, vedi se l'ho scritto correttamente, ti allego il file da me modificato.

Per quanto riguarda il foglio "Occupazione", spiego il significato delle XX o X.
Premessa: ci sono in totale 18 stanze, quelle pari sono tutte vista MARE, quelli dispari sono tutte vista strada/LATO. Noi affittiamo emtrambe minimo 3 giorni.
Quindi il foglio Occupazione e le XX per i vista mare e la X per i vista strada/lato era un espediente da me escogitato per diminuire la disponibilità risultante nelle prime righe di riepilogo "MARE" (XX) E "LATO" (X).
[Avrei volentieri gestito il tutto anche con il solo foglio "Quadro A3 esteso" ma mi incasinavo troppo con le formule nidificate e quindi ho creato il foglio Occupazione per seplificare.]
Pertanto quando tra una prenotazione e l'altra ci sono meno di 3 giorni le formule delle celle nel quadro Occupazione inserivano delle X per i LATO e XX per i MARE in modo che le formule presenti nelle due righe in alto di riepilogo LATO e MARE mi decrementassero la disponibilità per quei due giorni anche se in effetti non c'era alcuna prenotazione.

Ad esempio nel file che ti allego per la stanza nr. 6 tra le prenotazioni dei sigg. Perozzi e Necchi ci sono solo 2 giorni e quindi la dosonibilità nel riepilogo MARE per quei 2 gg. (25 E 26 MAGGIO) va tolta e deve essere 6 e non 7 come ora risulta; mentre per la stanza n. 5 tra le prenotazioni dei sigg. Verdirame e Rossi ci sono almento 3 gg e la disponibilità nel riepilogo LATO va lasciata.

Grazie ancora per quanto stai ottimamente facendo, rimango a tua disposizione per ulteriori chiarimenti.

Un saluto,

Luca

Ciao
fammi capire meglio
se ci sono 1 o 2 gg di differenza tra una prenotazione e l'altra, nel foglio Occupazione i due giorni "liberi" devono risultare occupati inserendo X per LATO e XX per MARE (però nella tua versione originale, nelle formule c'è solo 1 X sia lato che mare).

in attesa

saluti

EDIT: Purtroppo bisogna "spazzolare" il foglio Occupazione riga x riga e, laddove si rileva la differenza di 1/2 gg inserire le X o le XX.

Ora, siccome non mi sembri uno sprovveduto potresti provarci in autonomia aggiungendo nella sub Worksheet_Activate del foglio Occupazione, dopo Aggiorna un ciclo che fa ciò che serve.

Se hai difficoltà, sono qui.

Confermo se tra due prenotazioni per la stessa stanza ci sono solo 1 o 2 gg la disponibilità deve essere diminuita dal contatore giornaliero dei LATO (celle da C8:EY8) se il nr. della stanza è un nr. dispari e dal contatore dei MARE (celle da C7:EY7) se il nr. della stanza è un nr. pari.

Non focalizzarti sulle X o XX che come detto nel mio precedente messaggio erano solo un escamotage da me escogitato per il calcolo della disponibilità con le formule.
Per me, se ci sono altre soluzioni più efficienti, puoi anche fare a meno di "spazzolare il foglio" per inserirle a me interessa solo che i contatori del giorno relativo vengano decrementati se tra una prenotazione e l'altra ci sono solo 1 o 2 gg.. Magari sarebbe bello, ma non necessario, che per quei 1 o 2 gg ci fosse almento un colore differente per far capire che non sono in vendita.
Se ti va meglio puoi anche eliminare il foglio Occupazione e spostare i contatori (nella stessa posizione) nel foglio Quadro A3 esteso ovvero mantenere entrambi i fogli.
Decidi tu, ampia libertà.
Ricordati solo questo:
stanze pari = contatore MARE
stanze dispari = contatore LATO
Partenza contatore: 9 MARE e 9 LATO.

Spero di aver chiarito, in caso chiedimi.

Un saluto,

Luca


P.S.: Si, hai ragione nelle formule delle celle da C9:EY26 del foglio Occupazione che ti ho inviato ci sono solo una "X" per tutte le stanze, ma è sbagliato, avrebbe dovuto esserci X sulle formule delle celle delle stanze di LATO e XX in quelle delle stanze MARE; l'errore lo si capisce esaminando le formule delle celle della riga dei contatori MARE (celle da C7:EY7) e quelle dela riga dei contatori LATO (celle da C8:EY8). Scusa non me ne ero accorto.

Ciao
ho eliminato il foglio Occupazione, fai le tue prove, ma per eventuali ulteriori incrementazioni ti prego di sottoporre qualcosa di tuo, Già te lo avevo chiesto nel post #16 cosa che tu hai ignorato.

saluti

Ciao Domenico,

ho provato l'ultima versione del foglio e mi sembra che tutto funzioni a dovere.

Ti ringrazio tantissimo, senza di te non sarei stato ingrado di realizzarlo con le mie modeste ed approssimative conoscenze di VBA.
Non avevo letto bene il tuo post precedente e mi era sfuggita la parte in cui mi dicevi di cimentarmi con la scrittura del codice, sicuramente non ci sarei riuscito con i tuoi stessi ottimi risultati.
Comunque prometto che da ora in poi mi dedicherò maggiormente allo studio del VBA.
Grazie ancora per tutto quello che hai fatto, se passi per Caorle scrivimi che ci si vede.

Nel frattempo auguro un Buon Natale a te e ai tuoi cari.

Luca

Ciao
bene.

Ho visto che tu non fai nessun controllo di sovrapposizione date/Apt, del tipo:

- PIPPO - pren: Apt 6 periodo: 16/05/2018 >>> 25/05/2018
- PLUTO - pren: Apt 6 periodo: 15/05/2018 >>> 20/05/2018

Ti "regalo" un controllo, a se stante, che analizza tutta la tabella per rilevare eventuali sovrapposizioni.

Certo sarebbe meglio, con le opportune modifiche, inserirlo all'inserimento o variazione delle date Dal Al.

Ti ricordo che le sovrapposizioni non sono controllate dalla sub Aggiorna, che, nel caso, fa pasticci.

Se non vuoi mettere tali controlli quando inserisci/modifichi un nuovo cliente, fai una Call alla sub controllo all'inizio di Aggiorna e, se ti ritorna errori, interrompi Aggiorna.

Comunque, vedi tu.

saluti



PICCOLA delucidazione: poichè è più facile rilevare una NON sovrapposizione, la routine rileva appunto la NON sovrapposizione e, se non si verifica segnala:
If Not (y1 <= a1 Or b1 <= x1) Then
...E' sovrapposto.

Ciao Domenico,

ti ringrazio del "regalino" del codice di controllo della sovrapposizione di date è proprio utile.

Non ti nascondo che ho fatto qualche difficoltà a capire come inserirlo ma alla fine ci sono riuscito e mi sembra che ora tutto funzioni. Ho fatto in modo che il controllo si attivi quando vado ad inserire/modificare l'appartamento e le date di arrivo e partenza, ho tolto il messagio "Nessuna sovrapposizione" che rallentava troppo il lavoro.

Ti segnalo solo una piccola anomalia che però non inficia il funzionamento del foglio e può andare bene anche così: se vado a nascondere la prima colonna del foglio Prenotazioni (di solito nascondo tutte le colonne sotto l'intestazione in verde "Servizio") e la prima del foglio Registrazione, la macro relativa all'inserimento delle date di creazione nel primo foglio e quella relativa all'inserimento di una nuova riga e copia della data nel secondo foglio, non funzionano.

Ti ripeto che non è un problema se non si riesce ad ovviare all'inconveniente: anzichè nasconderle le riduco notevolmente di larghezza.

Ad ogni buon conto ti invio il file con le modifiche da me fatte, così vedi se tutto funziona e sono stato bravino

Un saluto,

Luca