Incrémentation automatique de lignes entre 2 feuilles
Bonjour à toutes et à tous,
Je me permets de venir sur ce forum, je souhaiterai avoir vos lumières pour résoudre un problème, certainement anecdotique pour certains, mais qui me fait tourner en bourrique. Je vous expose ma problématique, en espérant être assez clair.
Je suis entrain de créer un tableau d'inscription pour la trésorière de mon club, afin de regrouper ses 9 feuilles d'informations en 1, avec des regroupements de formules, dans laquelle on retrouve plusieurs info comme le nom, prénom, âge, catégorie, mail, etc. des joueurs, jusque là aucun soucis.
Aujourd'hui, de ces informations, certain joueurs ont un "Pass Région" que je valide (en indiquant "oui") à l'aide d'un menu déroulant ou "non". Ce que je souhaite, c'est lorsque j'indique "Oui" pour ces joueurs au fil de l'inscription, certaines informations (nom, prénom, etc.) vont renseigner une ligne, au fur et mesure, d'un tableau d'une autre feuille intitulée "Pass_Région", afin de m'affranchir de l'utilisation des filtres "cellule non vide".
Cordialement,
Bonjour,
as-tu un fichier google sheets dont tu pourrais mettre un lien ?
d'autant que j'ai essayé d'ouvrir ton fichier et voici la réponse (c'est excel qui a pris la main) !!
Bonjour,
A mince pour le fichier, voici le lien.
https://docs.google.com/spreadsheets/d/1XyKXXrMxreFGNEzs1MDXktNRCQ5MMQKJ8Z-qTSe4HcU/edit?usp=sharing
Cordialement,
La meilleure des solutions aurait été de mettre en B5 de l'onglet PASS REGION
=query(INSCRIPTION!A3:AH;"select B,C,G,N,O,AH where M='OK' ";0)qui donne instantanément l'extrait souhaité
mais je suppose qu'en G,H et J des informations seront ajoutées ? c'est cela ?
Oui, les informations en G, H et J seront renseignées au fur et à mesure par la trésorière.
Pourriez-vous s'il vous plait, m'expliquer la fonction utilisée?
Je viens d'essayer la formule, effectivement la colonne G pose un problème. Je vais intervertir les colonnes G et I, et magnifique c'est ce que recherchait comme fonctionnalité.
Je vous remercie beaucoup pour avoir solutionné mon problème en quelques échanges. Je vais pouvoir avancer et terminer ce fichier.
Encore merci,
Michaêl
Query est une super fonction de GSheets https://www.sheets-pratique.com/fr/query
Oui, MAIS ... danger
si tu ajoutes des informations et que le premier onglet est trié, les infos ajoutées ne seront plus en face des bonnes personnes !! on en reparle ...
Après réflexion (désolé, hier j'étais pris de court) ... y a t'il un identifiant unique, genre n° de dossier ? le but étant que, lors d'un tri par exemple les informations ajoutées ne soient pas affectées à la mauvaise ligne !
Si oui, la façon la plus sûre serait de couper la page en 2 avec 2 tableaux côte-à-côte les 2 étant triés par ce n° unique. Sinon il y a d'autres solutions comme reporter en "dur" das le second onglet les valeurs dès lors qu'on mette "OK" dans la colonne adhoc (mais cela peut aussi poser des problèmes de réversibilité - en clair, si on revient en arrière sur "OK" il faudrait savoir effacer la ligne ?).
Bonsoir,
Désolé de ne pas vous avoir répondu plus tôt. Effectivement, il n'y a pas de n° de dossier ou ID pour chacun des licenciés. Mais rien ne m'empêche de créer une colonne la "A" par exemple avec un n° d'ID propre à chaque licencié.
Après réflexion (désolé, hier j'étais pris de court) ... y a t'il un identifiant unique, genre n° de dossier ? le but étant que, lors d'un tri par exemple les informations ajoutées ne soient pas affectées à la mauvaise ligne !
Si oui, la façon la plus sûre serait de couper la page en 2 avec 2 tableaux côte-à-côte les 2 étant triés par ce n° unique. Sinon il y a d'autres solutions comme reporter en "dur" das le second onglet les valeurs dès lors qu'on mette "OK" dans la colonne adhoc (mais cela peut aussi poser des problèmes de réversibilité - en clair, si on revient en arrière sur "OK" il faudrait savoir effacer la ligne ?).
Ca m'interesse énormément ce que tu dis car c'est le souci que je rencontre en ce moment.
J'ai un onglet de formulaire ou les adhèrents s'inscrivent. Ces données sont récuperés dans un autre onglet propre via Query & Sort : Nom Prénom Age Adresse Mail etc...
Et puis dans les colonnes de fin il y a : statut paiement commentaires etc... elles sont modifiables.
Mais comme tu le soulignes, les colonnes alimentés par Query sont dynamiques, la ou les colonnes qui se gérent manuellement sont statiques... un décalage se fait et ça ne correspond plus.
Du coup pour le moment, les saisies manuelles se font a même l'onglet du formulaire. J'ai percuté ton idée de faire une colonne de numéro d'adhèrent qui servirait de référence entre les 2 onglets, mais j'ai pas trop su comment l'exploiter.
Donc oui, ta solution m'interesse, si tu à l occasion on pourrait la developper. Je te remercie.
La les réponses auto du formulaire.
La l'onglet adhèrent ou on reclasse proprement et ou j'aimerais que les colonnes S T U V soient modifiables au sein de cet onglet. Et non pas retourné dans le bazar de "Reponse au formulaire" comme actuellement.
C'est un sujet assez récurrent (et piégeant) que j'ai souvent rencontré avec excel. Ici il y a une dimension supplémentaire que je connais mal, ce sont les formulaires. Ma question (@Hott) : est-ce qu'un formulaire a un n° d'enregistrement séquentiel ?
Pas de numéro séquentiel mais un horodatage que tu peux apercevoir en colonne C.
Ca peut servir de ref.
Pour le reste aucune ligne ne peut etre remplis manuellement à l'avance. Admettons que le formulaire contient 4 champs : Nom Prénom Age Mail.
La feuille automatique "Réponse au formulaire" va réserver 5 colonnes : A = Horodatage B = Nom C = Prénom D = Date de naissance E = Mail.
Chaque réponses de formulaire sera enregistré sur une ligne : 2, 3, 4, 5 etc... Admettons que les réponses s'arrête en ligne 6 (soit 5 réponses de formulaires).
Dans les colonnes F, G, H etc... celles hors réponses formulaire on peut mettre seulement en ligne 1 des formules matricielles pour pouvoir faire des calculs sur les ligne 2, 3, 4, 5, 6..
Exemple concret en F1 : =ArrayFormula(SI(D$2:D="";"";DATEDIF(D$2:D;AUJOURDHUI(); "y"))) permets de calculer l'age en fonction des dates de naissance entré dans la colonne D.
Mais si on reproduit et étire cette formule en non matricielle en F2 , F3 , F4 etc... Admettons juqu'à la ligne 7.
Donc pour résumer des réponses aux formulaires qui vont de A2:E6. Et une colonne de calcul étiré de F2:F7.
La 6 eme réponse au formulaire devrait logiquement aller de A7:E7, mais il ne vas pas aimer qu'une donné soit déjà présente sur la ligne 7 du coup ca fait comme une insertion de ligne. La formule de calcul d'age qui était en F7 vas se retrouver en F8 et F7 sans formule. Dans le meilleur des cas. Dans le pire des cas ca va supprimer toutes les réponses de la ligne 2 à 6.
Et pareil pour la mise en forme, insertion de ligne à chaque réponse de formulaire qui va mettre le bon vieux fond blanc avec contour noir.
Bref tout ceci pour dire que seulement les mise en forme ou formules en A:A B:B etc sont prises en comptes en ligne 1 (ligne des en tetes), après en dessous il faut rien toucher quand la ligne est vierge.
Parcontre une fois la ligne remplie par le formulaire après on peut remplir à sa guise les colonnes suivantes (ce que j'ai fais actuellement).
Mais ca oblige l'utilisateur à compléter son tableau dans le bazar des réponses aux formulaires avec tous les risques que ça comporte comme décrit + haut.
Si les modification pourraient se faire dans l'onglet propre ou on récupère les données avec Query ca serait génial.
En espérant avoir été plutot clair !
Oui, l'horodateur peut être une solution, à condition de ne pas avoir 2 valeurs identiques (un hasard), mais une référence / un identifiant de ligne serait mieux.
J'avais eu un sujet similaire en excel où la personne voulait gérer le personnel avec un onglet présence par mois / un onglet avance salaire / un onglet contrat etc... le tout avec des formules de ligne à ligne qui bien évidemment, dès qu'un tri était pratiqué, les informations ne correspondaient plus du tout !
Pour moi, il y a 2 solutions :
- avoir un identifiant qui permet de relier les informations entre onglets sans équivoque
- on oublie dans ce cas query
- on va chercher les informations communes entre les onglets, comme les noms, les intitulés de questions, etc. par RECHERCHEV dans un onglet maître à partir de l'identifiant
- si une partie seulement des données est concernée, on peut éventuellement par un script de type onEdit(e) ajouter l'identifiant aux onglets ... ,
- mais cet identifiant ne peut pas être relié par formule à l'onglet maître sous peine de revenir au problème de cohérence/d'alignement des données ajoutées
- l'identifiant doit être inscrit en "dur" dans les oglets, et ceci peut s efaire quand même mais par un simple script
- je ferai un démonstrateur pour @Delphine/Voegtlin
- spécifiquement avec GSheets, je pense qu'on peut TOUT avoir sur un même onglet et travailler avec des vues filtrées , ce qui n'existe pas à ma connaissance sur excel (<=2013)... attention, ce n'est pas la même chose que des filtres !! https://www.sheets-pratique.com/fr/cours/filtre
La vue filtrée permet aux autres utilisateurs qui consultent la feuille de pouvoir changer de vue pour visualiser les données de manière différente (contrairement au filtre où l'affichage est figé pour les utilisateurs en lecture seule).
Ce mode permet également de créer plusieurs vues différentes et de les nommer pour pouvoir ensuite afficher et quitter ces vues très facilement.
Dernière solution plus complexe, travailler avec des formulaires (par métier) qui alimentent une grande base de données enregistrée dans un onglet à l'image des ERP.
Ce qui mets le bazar c'est le SORT.
Si on fait un Query qui récupère les données du formulaires mais que ca reste toujours dans le même ordre, ca irait très bien, les nouvelles entrées se rajouteraient toujours vers le bas, les entrée précédentes resteraient statiques.
Le SORT rend le tout dynamique et génère le problème une partie dynamique l'autre statique.
Du coup RechercheV ou Query ca reviendrait au même non ? C'est le SORT le problème.
Pour ce qui est de l'horodatage, il fonctionne à la seconde prêt. Il faudrait vraiment ne pas avoir de chance pour que 2 personnes s inscrivent exactement à la meme seconde sur association de 500 adhèrents max à l'année (me concernant), mais pas impossible il est vrai.
Dernière solution plus complexe, travailler avec des formulaires (par métier) qui alimentent une grande base de données enregistrée dans un onglet à l'image des ERP.
Ce que j'ai pu penser, mais je sais pas si la on parle de la même chose, c'est que le client s'ajoute dans la base de données via le formulaire et génère automatiquement une fiche client qui est accessible et modifiable, cette meme fiche client detectera la ligne correspondante de la base de données et la completera.
Bref je vais réfléchir dans le week end à ceci, je fais un retour si j'ai du concret.
Merci Mikhail
P.S :
Voici un exemple concret et simplifié de notre problématique.
Formulaire : https://forms.gle/GZdttg45u2sgVCe7A
Tableau : https://docs.google.com/spreadsheets/d/1lefagz4tDB8DVpj7hbHxqEVSG_5n7lrP-9G5tMWmTYo/edit?usp=sharing
Ce qui mets le bazar c'est le SORT.
Si on fait un Query qui récupère les données du formulaires mais que ca reste toujours dans le même ordre, ca irait très bien, les nouvelles entrées se rajouteraient toujours vers le bas, les entrée précédentes resteraient statiques.
Le SORT rend le tout dynamique et génère le problème une partie dynamique l'autre statique.Du coup RechercheV ou Query ca reviendrait au même non ? C'est le SORT le problème.
Oui en partie, car
- si dans le query il y a une sélection comme le fait Voegtlin sur la présence d'une donnée en colonne N, il suffit alors d'en ajouter une ou d'en supprimer une que les données se décalent.
- Idem si on supprime une ligne entière de l'onglet maître.
Cela reste donc "risqué" mais si c'est figé alors ok (attention car google sheets ne peut pas interdire le tri ou la suppression de lignes ... sauf si l'onglet maître est dans un fichier séparé en lecture seule dont on importe les données par ImportRange)
P.S :
je vais créer un formulaire et le tableau qui va avec que je partagerai ici pour qu on puisse bosser sur un exemple concret et simplifié de notre problématique.
Je le post d'ici ce soir.
super ! moi je vais faire l'exemple simplifié pour Voegtlin où les données de base en viennent pas d'un formulaire
Oui tu as raison sur tes autres cas de figures. Bien vu.
Le formulaire : https://forms.gle/SbkzKQMpSwrxQhku9
Le tableau : https://docs.google.com/spreadsheets/d/1lefagz4tDB8DVpj7hbHxqEVSG_5n7lrP-9G5tMWmTYo/edit?usp=sharing
Voila le cas concret.
Pour ma part, je serai moyennement dispo pour les 2 jours qui suivent, mais j'essaie d'y réfléchir, bien que on soit aux limites de mes compétences.
Si idéalement on trouve une solution sans macro, alors ça serait top.
1- Un exemple (hors formulaire mais à adapter peut-être) fait rapidement, quelques améliorations possibles.
https://docs.google.com/spreadsheets/d/1ko5B7JTS9m04LZtCLCsRfk7BVwZ-mhUKD4anA7IHGjo/copy
Quand on renseigne la colonne "pass région", l'identifiant s'ajoute à la liste
function onEdit(event){
var feuille = event.source.getActiveSheet();
var cellule = event.source.getActiveRange();
if (feuille.getName()=='club sportif' && cellule.getColumn()==5){
if (cellule.getValue()!=''){
var classeur = SpreadsheetApp.getActiveSpreadsheet();
var cible = classeur.getSheetByName('pass région');
var identifiant = feuille.getRange('A' + cellule.getRow()).getValue()
// il faudrait vérifier que la ligne n'existe pas déjà
cible.getRange('A' + (cible.getLastRow()+1)).setValue(identifiant)
toast(identifiant + ' ajouté !')
}
else {
// il faudrait faire ici la suppression de la ligne devenue inutile
}
}
}
function toast(body, title, timeout) {
return SpreadsheetApp.getActive().toast(
body,
title || "information",
timeout || 5
);
}2- je vais voir pour les vues filtrées
Pour les vues filtrées
la base
https://docs.google.com/spreadsheets/d/1sgJOzEQm1sz2lB9rYKUm0VsgNaSCBQ7YmThMW-RjrW4/edit?usp=sharing
et la vue filtrée qui n'entrave pas le travail sur la base
voir si on peut masquer des colonnes sans toucher à l'autre vue
Pour le formulaire (@Hott18), voici une version qui ajoute en fin de feuille d'adhérents les nouveaux identifiants (horodatage) constituant le cordon ombilical entre les 2 feuilles ... on peut alors trier l'une et l'autre indépendamment, voire supprimer des lignes.
function maj() {
var classeur = SpreadsheetApp.getActiveSpreadsheet();
var formulaire = classeur.getSheetByName('Réponses au formulaire 1');
var liste = classeur.getSheetByName('Liste adhérents version Steelson Staliyevich');
var derF = getLastDataRow(formulaire,"A")
var derL = liste.getLastRow()
var valeurs = liste.getRange('A'+5+':A'+derL).getDisplayValues().join().split(",")
var ajout=[]
for (var i=3;i<=derF;i++){
var reponse = formulaire.getRange('A'+i).getDisplayValue()
var ligne = valeurs.indexOf(reponse);
if (ligne == -1) {
ajout.push(reponse)
}
}
for (var i=0;i<ajout.length;i++){
liste.getRange('A'+(derL+i+1)).setValue(ajout[i])
}
}
function getLastDataRow(sheet,col) {
var lastRow = sheet.getLastRow();
var range = sheet.getRange(col + lastRow);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
}et formule pour rapatrier les données via l'identifiant (qui est l'horodatage) :
=arrayformula(if($A$6:$A="";;arrayformula(vlookup(to_text(A6:A);query(arrayformula(to_text('Réponses au formulaire 1'!A3:E));"select *");{2\3\4\5};0))))un peu complexe avec les horodatage, mais plus simple avec un identifiant texte !