Indirect formula?

[Pierre]

Hello

I have production file which list some info per day and per week on the same sheet. I want to create a sheet which will take only information from columns with "semaine" written.
I think it's possible to do using the INDIRECT formula but I forgot how to use it...
Thanks in advance!

 

[Nunes]

Hi Pierre

Im not sure this will do the trick as I don't exactly understand what you need, but in your example file, if you put this formula in C1:

="Semaine "&TEXT(VALUE(RIGHT(B1;2))+1;"00")

It will return "Semaine 02".
And as you copy it to the right it will return Semaine 03, 04, 05....

You may need to replace ";" by ",".

Hope it helps.

Best
Nuno

 

[Nunes]

As for using INDIRECT, check the attached workbook.
If you add an auxiliary row with the letter of the column where the information for each week is located, you can use indirect to refer to the information directly.

Best
Nuno

 

[Pierre]

Thanks Nunes. Yes it's what I wanted to have
But would it be possible to have without the helper line? (that would be integrated in the formula?)

 

[Khalid NGO]

Hi,
I think this will work:
=IFERROR(INDEX('Prod dossier'!$B1:$IU1,,MATCH("Semaine "&TEXT(COLUMN(A1),"00"),'Prod dossier'!$B$1:$IU$1,0)),"")

Copy across and down...

Regards,