Fil an excel sheet automatically

[Mikaelxls]

Hello,
I am a novice in Excel and I would like to fil automatically a file.

Here is my request :
Sheet "FULL PROGRAM'', I would like that A2 = SHEET ''Program 1'' A2 if there is data. If there is no data, copy the following cell. Then if there is no data go to next sheet ''Program 2'' and continue.
I am looking to have automatically the concatenation of the 3 sheets ''Program 1'', ''Program 2'' and ''Program 3'' on the sheet ''FULL PROGAM''.

I guess that you will catch me.

How could I attach the Xls sheet?

Thanks in advance,
Mikaël

[Pete_UK]

It would help if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[Mikaelxls]

Here is the file.

[Pete_UK]

Do you want the table in the Full Program sheet to appear automatically in columns A to F ?

And do you want to ignore Projet BBB from Program 1 because there is no data for it on any of the years?

Pete

[Mikaelxls]

Hi Pete,
Yes I would like the table in the Full Program sheet to appear automatically in columns A to F. For columns B to F, I was thinking about making a vlookup.
Even if Projet BBB from Program 1 has no data I don't want to ignore it.
Thanks for your help

[Pete_UK]

I've attached the file that shows how this can be done using a few formulae. I did not have to change anything in the 3 subsidiary sheets, though I inserted 3 rows in the middle of the Program 3 sheet to make it the same as the other two sheets.

In the Full Program sheet, I've set up a little table in columns T U and V, with the three sheet names in T2:T4 and this formula in U2 (copied down):

=COUNTA(INDIRECT("'"&T2&"'!a2:a16"))

which counts how many entries you have in each sheet, and this formula in V2:

=U2+V1

which gives a cumulative count of records down the column. Note that cell V1 contains zero, which is important for the other formulae. I then used another little table in columns X and Y, with this formula in X2:

=IF(ROWS($1:1)>MAX(V:V),"",IFERROR(INDEX(T:T,MATCH(ROWS($1:1)-1,V:V)+1),""))

and this one in Y2:

=IF(X2="","",IF(X2<>X1,2,Y1+1))

These have been copied down to row 16 to match your main table (I've applied a light green shading to this table, to make this clearer). The formulae in column X return (in turn) the name of the sheet where the records can be found, and the one in column Y return the row number on that sheet where successive records should be found.

I then used this formula in cell A2:

=IF($X2="","",INDIRECT("'"&$X2&"'!"&CHAR(64+COLUMNS($A:A))&$Y2))

which can be copied across to column F, and down to row 16 to complete the table. I had to change the conditional formatting on $B$2:$L$16 to use this formula:

=AND(B2<>"",B2>0)

so that the bottom 3 rows (blanks) did not trigger the green colour. I also changed the formulae in N2 to this:

=IFERROR(F2-C2,"")

with a similar change in O2, so that the blanks in the bottom rows did not cause a #VALUE error.

Hope this helps.

Pete

[Mikaelxls]

Thanks Pete. It's exactly what I was looking for.
I will try now to understand what you did ;-)
Have a good night.

[Pete_UK]

You're welcome - glad to help.

If that takes care of your original question, please take a moment to 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

[Mikaelxls]

Hi Pete,
I still need your support.
I was able to recreate your formulas on the sheet Synthesis for the following area A31:L60 BUT not the area A23:A28. Could you tell me why?
Why on the Cell A31 do you use 64 in your formula?
I am trying to concatenate Test 1, Test 2 and Test 3 on the Sheet Synthesis.
Thanks again for your support.
Mike

[Pete_UK]

The formula in A23 is looking at cell X23, but you've moved the tables over for that section so it should be looking at cell AE23. This applies to the other formulae in that section. However, I'm a bit surprised by the table starting in AA23 - you've used a sheet name in AA23 that doesn't exist in the workbook, so the formula in AB23 should return an error but it returns 1 (Evaluate Formula shows that =COUNTA(#REF) is 1 !! ).

Anyway, it seems as if you want to get the data from the current sheets, I put this formula in AA23:

=T31

and copied down to T33, so you only have to change the sheet names in one place now. I also changed the formula in AE23 to this:

=IF(ROWS($1:1)>MAX(AC:AC),"",IFERROR(INDEX(AA$22:AA$28,MATCH(ROWS($1:1)-1,AC$22:AC$28)+1),""))

as it wasn't picking up the sheet names after the first one, and then I changed the formula in A23 to this:

=IF($AE23="","",INDIRECT("'"&$AE23&"'!"&CHAR(64+COLUMNS($A:A))&$AF23))

which can be copied across and down as before (though there isn't room for the 8th record).

The 64 is added on to the COLUMNS($A:A) term within the CHAR function. The COLUMNS term initially returns 1, but then 2, 3, 4 etc. as it is copied across, so we are looking for CHAR(65), CHAR(66), CHAR(67) etc. on successive columns - those functions return A, B, C and so on.

Hope this helps.

Pete

[Mikaelxls]

Hi Pete,
Thanks again.
I think you forgot to attach the file.
Could you do it?
Thx in advance,
Mikaël

[Pete_UK]

... I think you forgot to attach the file...

No, I didn't intend to, as I told you which formulae needed changing. Anyway, here it is.

Pete