incremental copy EXCEL 2010

[toscane2016]

hi everybody and sorry for my poor english
:my xls is made of 2 sheets

the first one is named COMMANDES(RESERVATION), with 42 columns (items) and 250 rows (stores) . All values in this sheet come from a copy/paste within another sheet : on each row we see the quntity oof the otem that the store has reserved

the second one is named LIVRAISONS(DELIVERY) in which i want to print a document for each of the 250 stores. this document has 42 lines (one for each item the store can reserve) :

REFERENCE - DESIGNATION - QUANTITE

the document starts at line 16

COLUMN A: REFERENCE(ITEM REFERENCE) i get the item reference in cell I1 : =IF(Commandes!$I$3<>0;Commandes!$I$1;"")
COLUMN B: DESIGNATION(ITEM DESCRIPTION), same : =IF(Commandes!$I$3<>0;Commandes!$I$2;"")
COLUMN C: QUANTITY RESERVED : =IF(Commandes!I3=0;"";Commandes!I3)
COLUMN E: QUANTITY DELIVERED : =IF(Commandes!I3=0;"";C16-F16)
COLUMN F : QUANTITY NOT DELIVERED :



My problem : the deliverydocument starts at line 16 and writes the above values
the next line (17) will increment :

=IF(Commandes!$J$3<>0;Commandes!$J$1;"") =IF(Commandes!$J$3<>0;Commandes!$J$2;"") =IF(Commandes!J3=0;"";Commandes!J3) =IF(Commandes!J3=0;"";C17-F17)


until the 42nd and last item

Any way to have these values automatically increment?

And if yes, how edit in this DELIVERY sheet, all the 250 documents, matching with the 250 stores and the reservations they've made


See the attached file
Thank you for helping.
Toscane

[Pete_UK]

You can change your formula in A16 to this:

=IF(INDEX(Commandes!$I$3:$AX$3;ROWS($1:1))=0;"";INDEX(Commandes!$I$1:$AX$1;ROWS($1:1)))

then copy down. Make similar changes to your other formulae.

Hope this helps.

Pete

[toscane2016]

thanks a lot.i will give it a try and be back to you asap.

[toscane2016]

hello i tried to translate your formula with XL french but it fails:

=IF(INDEX(Commandes!$I$3:$AX$3;ROWS($1:1))=0;"";INDEX(Commandes!$I$1:$AX$1;ROWS($1:1)))
is now
=SI(INDEX(Commandes!$I$3:$AX$3;LIGNES($1:1))=0;"";INDEX(Commandes!$I$1:$AX$1;LIGNES($1:1)))

xl says "formula contains text not recognized..."


any idea???


many thks

Toscane

[Pete_UK]

I'll set this up for you and post the file back here, so that when you open it Excel will automatically translate my formulae into your local language.

Instead of having 250 sheets, or 250 blocks on one sheet, for each shop, I think it will be better to have a drop-down where you can just select the shop and the formulae will adjust automatically to bring the data from the appropriate row in the Commandes sheet. Can you tell me which column in the Commandes sheet you would use to indicate the shop? Is it column C, and if so is that a numerical code or the name of the shop?

Pete

[toscane2016]

many thanks

for each delivery i will peint a document with the following values

column c refers to to the COMMAND NUMBER : it's numeric
column d refers to the internal client number

so my document will show

commande number : C3, C4 aso till C250
client number : D3, D4 ....D250
city location : G3, G4......

and next all the 42 lines

i surely agree with you that one document with a drop down is better, but is it possible to include in a macro the printing of all the 250 documents...without interaction of the user???

thanks for your time
Toscane

[Pete_UK]

I've attached the file so that it should translate into your language directly. On the Commandes sheet I've put some dummy data in (mainly with sequential numbers so you can see how it works) and set up the named range magasin which covers column D - this is used as the source for the data validation drop-down that I mentioned before.

In the second sheet I've left the columns A to G as they were, and copied the format of the cells from A1:G55 across to the right. In addition, I have set up the data validation in cell O2, so you can choose the shop from the drop-down The data will automatically adjust, so you can compare this table with the two that you had in the left-hand columns, but if you are happy that this does the job for you then you can delete columns A to G of this sheet.

Regarding the printing, I've set up the print area to suit the new table, but as I don't dabble with macros anymore I've not attempted to do that part for you. It would be fairly easy, though, to have a button to initiate the printing, and then to select from each element of magasin in turn and print the resulting table - maybe someone else can help you with that.

Hope this helps.

Pete

[toscane2016]

Hi pete this works fine..but for the need of the macro i m creating i need all the 250 delivery documents on a single page.actually before printing i add some arguments to the sheet then copy in a special foldet and then print out the whole thing
..can you just create the first one and then i will create all the 249...no problem...
Many thks.
Toscane

[Pete_UK]

Okay, in the second sheet of the attached file I've removed columns A to F, and what was column G (now column A) now contains the row numbers 1 to 42. I had to extend the table slightly, as it only went up to 38 before. I've changed the formulae so that instead of referring explicitly to ROWS($1:1) in the first row, they now refer to the numbers in column A. I also used Find & Replace to change I$2 to I2 in the top table, so that these will change as the block is copied down, and I've also put a border at the bottom of cells B61:G61 to indicate where the next page should occur.

I was then able to copy the block of cells A1:I61 and pasted into A62 - the drop-down in I63 needs to be changed to the next on the list (Shop_2 in my example).

You can repeat this for all the other shops, but before you do so I suggest you put the required formulae in cells G9 and G11, as well as in the range G16:G57 (I don't know what you want in there, as they were blank in your sample file), and you might also want to delete cell B3 and add some headings in cells B1:G6, so that when you copy the block down all of those will be included.

Good luck.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[toscane2016]

hi Pete, i was away for 2 days.
seen your last file which works perfectly..
one last question before closing this thread...let's say now i IMPORT all values for CODE CLIENT in column D (sheet COMMANDES).
how can i change the values in the drop down MAGASIN (sheet LIVRAISONS)
don't know if i m clear...
thks

Toscane