Microsoft 365 Choisircols et ajout de colonne

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
S

Sissou71

XLDnaute Occasionnel
Bonjour,

J'ai un classeur (exemple ci-joint) dans lequel j'ai :
- Saisie : feuille ou les commerciaux saisissent les données et qui vient récupérer un code de facturation issu de la feuille Code Facturation

- Code Facturation : contient les codes de facturation par grossiste

- Facturation : feuille qui récupère les données de la feuille Saisie avec un choisircols et un filtre sur la présence d'un Bon de commande. Cette feuille est destinée à la comptabilité. Dans cette feuille, la comptabilité indique le numéro de facture
J'ai besoin que lorsque ce numéro de facture est renseigné, il soit recopié dans la feuille Saisie

Mon problème est que ce numéro de facture est en dehors du choisircols, du coup si un commercial vient renseigner un bon de commande pour une autre ligne, dans mon onglet Facturation, la référence de facture reste sur la mauvaise ligne.

J'espère que ce sera plus clair avec le fichier joint. C'est un fichier fictif mais il reflète la réalité : tout est dans le même fichier et je ne peux pas demander aux comptables d'aller saisir dans la feuille Saisie.

Si quelqu'un a eu idée pour m'aider ? J'ai bien pensé à une macro qui récupère la données référence de facture à la fermeture du fichier mais le fichier est partagé donc ca ne fonctionnera pas.

Merci pour votre aide
 

Pièces jointes

Solution
AtTheOne
Bonsoir à toutes & à tous, bonsoir @Sissou71

Je suis conscient que le fil est un peu mort mais le problème :
pouvoir renseigner une information à coté d'une plage générée par une formule matricielle dynamique et que cette information reste toujours à la même place.​
et cela sans macro instruction m'a intéressé.

Mon idée est de générer un horodatage lors de la saisie du N° de bon de commande et, dans la feuille "Facturation, de trier les commandes extraite en fonction de cet horodatage (ordre croissant) ainsi les lignes déjà renseignées seront toujours dans le même ordre et en tête de tableau.

Pour ce faire j'utilise le calcul itératif :
1740418902425.png

et des formules avec une référence...
Trier par date Trier par votes
Hello

un conseil, utilise les tables structurées==> tu peux ainsi utiliser le nom des colonnes dans les formules==> plus besoin de savoir combien de lignes il y a
et tu peux aussi les utiliser pour les listes de validation
voir PJ

PS: je n'ai pas la fonction choisircol dans ma version excel.. j'ai donc juste remplacé les références par le nom des colonnes des tables sans voir ce que ca donne
 

Pièces jointes

Vote positif 0 Vote négatif
Dans la PJ, j'ai aussi créé une TS t_Facturation
j'ai fait un copier coller special valeur pour supprimer la fonction choisircol (sinon, j'ai des #noms) qui apparaissent (==> il faudra donc que tu remettes la formule)
et mis une formule recherchex dans la première feuille pour récuprer le numéro de facturation associé au bon de commande
 

Pièces jointes

Vote positif 0 Vote négatif
vgendron à dit:
Dans la PJ, j'ai aussi créé une TS t_Facturation
j'ai fait un copier coller special valeur pour supprimer la fonction choisircol (sinon, j'ai des #noms) qui apparaissent (==> il faudra donc que tu remettes la formule)
et mis une formule recherchex dans la première feuille pour récuprer le numéro de facturation associé au bon de commande
Cliquez pour agrandir...
Merci à toi mais cela ne fonctionnera pas pour mon entreprise.
feuille saisie : les commerciaux saisissent tout ce qui va arriver en facturation. A ce moment là ils n ont pas forcément le bon de commande. Celui ci dépend des services facturation des clients et le numéro n est pas incremental.
Quand ils ont le bon de commande, ils viennent le saisir.
A partir du moment où il y a un bon de commande ca ajoute une ligne dans la feuille facturation.
Sauf que ça modifie de fait la feuille facturation :
Exemple des lignes 1 à 3 je n ai pas de bon de commande dans la feuille Saisie
Donc dans la facturation j affiche la ligne 4 mais pas les 1 à 3
Si un autre commercial vient saisir un BdC pour la ligne 2 alors dans la facturation vient s ajouter la ligne 2 mais avant la précédente.
Du coup le numéro de facture que la comptable avait renseignée pour la ligne 4 n est plus affecté à la ligne 4 mais à la ligne 2.
Il faudrait que je trouve un moyen d ordonnancer la facturation par ordre d arrivée du bon de commande mais je ne vois pas comment….
Pour les tableaux entièrement d accord avec toi mais mon entreprise les refusent… c est bien dommage.
Merci encore pour tes propositions
 
Vote positif 0 Vote négatif
je leur avait mis des tableaux ça les a perturbé et en plus à chaque fois certains saisissaient en dehors du tableau donc c était un bazar sans nom, ils ont supprimé les tableaux.
Tes formules fonctionnent le souci c est pas les formules.
Le souci est que pour afficher uniquement les factures à saisir avec uniquement les colonnes utiles j utilise la fonction choisircols et filtre.
Ça me donne un tableau mais dans ce tableau je ne peux pas ajouter une colonne dans laquelle on peut saisir car sinon j ai une erreur de propagation dans mon tableau.
Du coup, ils saisissent le numéro de facture dans une colonne en dehors du tableau.
Sauf que quand le tableau change parce qu un nouveau bon de commande est saisi, mon numéro de facture saisi lui reste sur son numéro de ligne, il ne suit pas la ligne sur laquelle il avait été créé au départ. Je sais c est complexe mon truc
Il faut donc que je trouve un moyen d afficher les lignes qui ont un un bon de commande mais que les nouvelles lignes s incrémentent après.
Du coup choisircols et filtre ne sont sûrement pas adaptées à ce que je veux faire…
Je pourrais faire autrement mais je ne sais pas comment.
Je comprends que ce ne soit pas clair et si tu n as pas les fonctions choisircols et filtre ça rend encore moins clair.
 
Vote positif 0 Vote négatif
vgendron à dit:
bah oui, mais à un moment, si la formule c'est C =A + B et qu'ils saisissent en D, forcément.. Excel ne peut rien contre la betise..
Cliquez pour agrandir...
Oui mais donc leur responsable a décider que pas de tableau. En soi c est pas grave jusqu ici je m en sors là je suis juste embêter parce que je n ai aucune donnée qui me permet de savoir quand est saisi un bon de commande. Si j avais la date et l heure de saisie renseigner en automatique je pourrais trier mes données sur cette date et je n aurai plus de souci.
 
Vote positif 0 Vote négatif
Sissou71 à dit:
Bonjour,

J'ai un classeur (exemple ci-joint) dans lequel j'ai :
- Saisie : feuille ou les commerciaux saisissent les données et qui vient récupérer un code de facturation issu de la feuille Code Facturation

- Code Facturation : contient les codes de facturation par grossiste

- Facturation : feuille qui récupère les données de la feuille Saisie avec un choisircols et un filtre sur la présence d'un Bon de commande. Cette feuille est destinée à la comptabilité. Dans cette feuille, la comptabilité indique le numéro de facture
J'ai besoin que lorsque ce numéro de facture est renseigné, il soit recopié dans la feuille Saisie

Mon problème est que ce numéro de facture est en dehors du choisircols, du coup si un commercial vient renseigner un bon de commande pour une autre ligne, dans mon onglet Facturation, la référence de facture reste sur la mauvaise ligne.

J'espère que ce sera plus clair avec le fichier joint. C'est un fichier fictif mais il reflète la réalité : tout est dans le même fichier et je ne peux pas demander aux comptables d'aller saisir dans la feuille Saisie.

Si quelqu'un a eu idée pour m'aider ? J'ai bien pensé à une macro qui récupère la données référence de facture à la fermeture du fichier mais le fichier est partagé donc ca ne fonctionnera pas.

Merci pour votre aide
Cliquez pour agrandir...
Bonsoir,
Une proposition avec ajout d'un calcul en colonne P de l'onglet 'Commande', si j'ai bien compris, ce dont je ne suis pas sûr.
Cordialement,
 

Pièces jointes

Vote positif 0 Vote négatif
Gégé-45550 à dit:
Bonsoir,
Une proposition avec ajout d'un calcul en colonne P de l'onglet 'Commande', si j'ai bien compris, ce dont je ne suis pas sûr.
Cordialement,
Cliquez pour agrandir...
Merci Gégé, j ai mal exposé mon problème, j aimerai ajouter une colonne en saisie libre mais ce n est pas possible d avoir une colonne en saisie libre avec choisircols.
Je pense que je vais faire beaucoup plus simple mais merci pour cette proposition parce qu elle me servira à un moment. Bonne journee
Cecile
 
Vote positif 0 Vote négatif
Bonsoir à toutes & à tous, bonsoir @Sissou71

Je suis conscient que le fil est un peu mort mais le problème :
pouvoir renseigner une information à coté d'une plage générée par une formule matricielle dynamique et que cette information reste toujours à la même place.​
et cela sans macro instruction m'a intéressé.

Mon idée est de générer un horodatage lors de la saisie du N° de bon de commande et, dans la feuille "Facturation, de trier les commandes extraite en fonction de cet horodatage (ordre croissant) ainsi les lignes déjà renseignées seront toujours dans le même ordre et en tête de tableau.

Pour ce faire j'utilise le calcul itératif :
1740418902425.png

et des formules avec une référence circulaire.

Si l'horodatage est déjà renseigné on garde cet horodatage (référence circulaire) sinon on met l'horodatage si le N° du bon de commande est saisi. En formule pour la ligne 2 (horodatage en N, bon de commande en E) cela donne :
Enrichi (BBcode): 
=SI(NBCAR(N2)=17;N2;SI(E2="";"";TEXTE(MAINTENANT();"0")&"-"&TEXTE(MAINTENANT();"hh:mm:ss,00")))

Pour limiter le risque d'une saisie qui laisserait une ligne vide dans la zone de saisie des commandes, des formats conditionnels et une invite (>>>) incitent fortement à écrire au bon endroit.
1740419330548.png


De la même façon on peut automatiser la date de saisie par une formule avec une référence circulaire :
Si je suis sur la bonne ligne de saisie et que je renseigne une cellule de la zone de saisie, la date s'affiche puis reste figée. ce qui donne en B2
pour une zone de saisie en C2:G2
Enrichi (BBcode): 
=SI(NBCAR(TEXTE(B2;"0;"""";"""";"""""))>=5;B2;SI((NBVAL($C2:$G2)>0)*($B1<>"");AUJOURDHUI();""))

Ces formules avec références circulaires sont à tirées vers le bas (moi je l'ai fait sur 500 lignes)
Les autres formules sont des formules matricielles dynamiques qui s'étendent automatiquement en fonction de la plage des saisies effectuées.

Dans la feuille de facturation la formule qui extrait les commandes avec un N° de bon de commande trie par Horodatage croissant, les lignes sont donc toujours présentées dans le même ordre. Une ligne avec un N° de bon de commande intercalé entre des lignes déjà renseignées apparaîtra sous les lignes déjà renseignées.
De même que dans la feuille de commande, des formats conditionnels invitent à faire les saisies au bon endroit.

1740420061866.png


J'ai utilisé des noms définis :

_LimitesZoneSaisies=Commande!$A$2:$A$502Limite de la zone de saisie (pour reduire la plage de formules. Ici 500 lignes)
Entêtes=Commande!$B$1:$P$1Entêtes de la feuille "Commande"
LigneVide={"".""."".""."".""}Ligne vide d'une commande 6 cellules de saisie Client, Produit, Bon de commande, Prix unitaire, Qté, Forçage de la date de facturation.
PlageOccupée=LET(D;DECALER(_LimiteZoneSaisies;0;1;;1);
Z;DECALER(_LimiteZoneSaisies;0;1;;COLONNES(LigneVide));
FILTRE(Z;BYROW(Z;LAMBDA(L;SOMMEPROD(N(L<>""))>0))*(D<>"");LigneVide))		Zone occupée par les saisies effectuées. (Sans lignes vides intermédiaires)
Commandes=DECALER(Entêtes;1;0;LIGNES(PlageOccupée))Zone occupée par les commandes saisies
Facturations=DECALER(Facturation!$A$2#;0;0;;COLONNES(Facturation!$A$2#)+1)Zone occupée par les facturations (correspond aux commande avec Bon de commande)
chx_Client=TS_Clients[Client]Liste pour le choix des clients
chx_Produit=TS_Produits[Produit]Liste pour le choix des produits
TS_Produits=Paramètres!$A$2:$D$7Tableau structuré contenant la liste des produits
TS_Clients=Paramètres!$F$2:$J$6Tableau structuré contenant la liste des clients


Voilà pour le principe, voir le fichier en pièce jointe pour l'application.

Remarque : le mode de calcul itératif ne s'active pas automatiquement lorsqu'un autre classeur est déjà ouvert, c'est un point à traiter.

A bientôt
 

Pièces jointes

Vote positif 0 Vote négatif
Solution
AtTheOne à dit:
Bonsoir à toutes & à tous, bonsoir @Sissou71

Je suis conscient que le fil est un peu mort mais le problème :
pouvoir renseigner une information à coté d'une plage générée par une formule matricielle dynamique et que cette information reste toujours à la même place.​
et cela sans macro instruction m'a intéressé.

Mon idée est de générer un horodatage lors de la saisie du N° de bon de commande et, dans la feuille "Facturation, de trier les commandes extraite en fonction de cet horodatage (ordre croissant) ainsi les lignes déjà renseignées seront toujours dans le même ordre et en tête de tableau.

Pour ce faire j'utilise le calcul itératif :
Regarde la pièce jointe 1213595
et des formules avec une référence circulaire.

Si l'horodatage est déjà renseigné on garde cet horodatage (référence circulaire) sinon on met l'horodatage si le N° du bon de commande est saisi. En formule pour la ligne 2 (horodatage en N, bon de commande en E) cela donne :
Enrichi (BBcode): 
=SI(NBCAR(N2)=17;N2;SI(E2="";"";TEXTE(MAINTENANT();"0")&"-"&TEXTE(MAINTENANT();"hh:mm:ss,00")))

Pour limiter le risque d'une saisie qui laisserait une ligne vide dans la zone de saisie des commandes, des formats conditionnels et une invite (>>>) incitent fortement à écrire au bon endroit.
Regarde la pièce jointe 1213596

De la même façon on peut automatiser la date de saisie par une formule avec une référence circulaire :
Si je suis sur la bonne ligne de saisie et que je renseigne une cellule de la zone de saisie, la date s'affiche puis reste figée. ce qui donne en B2
pour une zone de saisie en C2:G2
Enrichi (BBcode): 
=SI(NBCAR(TEXTE(B2;"0;"""";"""";"""""))>=5;B2;SI((NBVAL($C2:$G2)>0)*($B1<>"");AUJOURDHUI();""))

Ces formules avec références circulaires sont à tirées vers le bas (moi je l'ai fait sur 500 lignes)
Les autres formules sont des formules matricielles dynamiques qui s'étendent automatiquement en fonction de la plage des saisies effectuées.

Dans la feuille de facturation la formule qui extrait les commandes avec un N° de bon de commande trie par Horodatage croissant, les lignes sont donc toujours présentées dans le même ordre. Une ligne avec un N° de bon de commande intercalé entre des lignes déjà renseignées apparaîtra sous les lignes déjà renseignées.
De même que dans la feuille de commande, des formats conditionnels invitent à faire les saisies au bon endroit.

Regarde la pièce jointe 1213598

J'ai utilisé des noms définis :

_LimitesZoneSaisies=Commande!$A$2:$A$502Limite de la zone de saisie (pour reduire la plage de formules. Ici 500 lignes)
Entêtes=Commande!$B$1:$P$1Entêtes de la feuille "Commande"
LigneVide={"".""."".""."".""}Ligne vide d'une commande 6 cellules de saisie Client, Produit, Bon de commande, Prix unitaire, Qté, Forçage de la date de facturation.
PlageOccupée=LET(D;DECALER(_LimiteZoneSaisies;0;1;;1);
Z;DECALER(_LimiteZoneSaisies;0;1;;COLONNES(LigneVide));
FILTRE(Z;BYROW(Z;LAMBDA(L;SOMMEPROD(N(L<>""))>0))*(D<>"");LigneVide))		Zone occupée par les saisies effectuées. (Sans lignes vides intermédiaires)
Commandes=DECALER(Entêtes;1;0;LIGNES(PlageOccupée))Zone occupée par les commandes saisies
Facturations=DECALER(Facturation!$A$2#;0;0;;COLONNES(Facturation!$A$2#)+1)Zone occupée par les facturations (correspond aux commande avec Bon de commande)
chx_Client=TS_Clients[Client]Liste pour le choix des clients
chx_Produit=TS_Produits[Produit]Liste pour le choix des produits
TS_Produits=Paramètres!$A$2:$D$7Tableau structuré contenant la liste des produits
TS_Clients=Paramètres!$F$2:$J$6Tableau structuré contenant la liste des clients


Voilà pour le principe, voir le fichier en pièce jointe pour l'application.

Remarque : le mode de calcul itératif ne s'active pas automatiquement lorsqu'un autre classeur est déjà ouvert, c'est un point à traiter.

A bientôt
Cliquez pour agrandir...
Merci beaucoup AtTheOne pour ce travail et sa documentation très précise. J'ai choisi de ne pas ajouter de colonne en saisie libre et me suis débrouillée autrement pour avoir mon information mais je suis particulièrement touchée par ton travail
Bonne journée
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

S
  • Question Question
Microsoft 365 Consolider des données de plusieurs feuilles
Réponses
2
Affichages
119
Sissou71
S
M
  • Résolu(e)
XL 2016 creation d'un logicile de facturation
Réponses
3
Affichages
120
MALKO
M
M
  • Résolu(e)
XL 2016 Comment contourner la fonction Indirect d'Excel avec code VBA
Réponses
11
Affichages
340
ma6me
M
S
  • Question Question
XL 2016 Création de factures à parti d'un tableau
Réponses
5
Affichages
85
sfconstant
S
C
  • Question Question
Microsoft 365 Ajout de données dans tableau existant
Réponses
8
Affichages
117
chris
chris
R
  • Question Question
XL 2019 Aide à la création d'un fichier de saisie et suivi des engagements budgétaires sous Excel
Réponses
2
Affichages
58
Royaulté
R
H
  • Question Question
XL 2013 Saisie sécurisée numéros de téléphone et ajout fonction clic droit de la souris
2
Réponses
21
Affichages
714
Helios77
H
P
  • Question Question
XL 2021 Ajout/adaptation de code VBA sur feuille qui en contient déjà
Réponses
3
Affichages
85
Pierreavecunp
P
FredeX
  • Question Question
Microsoft 365 Ajout de formule sur tableau
2
Réponses
16
Affichages
556
FredeX
FredeX
A
  • Question Question
Microsoft 365 Macro pour récupérer des colonnes qui ne sont pas conjointes
Réponses
4
Affichages
223
Anthony_51
A
N
  • Question Question
XL 2016 Formule entre onglet et calendrier
2 3
Réponses
32
Affichages
2 K
Nicolas37
N
C
  • Question Question
XL 2021 Excel facturation avec BD clients (modèle utilisable joint) - liste déroulante =INDIRECT sur classeur séparé
Réponses
12
Affichages
839
Calizarine
C
K
  • Question Question
XL 2021 Créer un document imprimable à partir d'une liste de commande
Réponses
1
Affichages
41
sylvanu
sylvanu
F
  • Question Question
Microsoft 365 gestion de stock
Réponses
4
Affichages
174
alexis38
A
S
  • Question Question
XL 2016 Différents calculs et graphiques
Réponses
8
Affichages
137
Stephane72
S
V
  • Résolu(e)
XL 2016 Facturier : Retour à la ligne sur la facture
2
Réponses
19
Affichages
1 K
Vermithor
V
L
  • Résolu(e)
XL 2021 recherche valeur si une cellule contient un numéro
Réponses
5
Affichages
172
lola18
L
V
  • Question Question
XL 2021 Planning GANTT et planification de ressources
Réponses
8
Affichages
181
laurent950
laurent950
B
  • Question Question
XL 2016 VBA - Ajout de consignes dans le corps d'un mail
Réponses
3
Affichages
252
kiki29
kiki29
S
  • Résolu(e)
XL 2019 VBA automatiser hauteur de lignes
Réponses
7
Affichages
186
sofmat
S
Retour