Convert text to Excel formula
Solved
Lepreschone
-
Lepreschone -
Lepreschone -
Hello,
I have an Excel workbook with 32 sheets, one for each day and one for the total of the month.
I would like to have a column on the totals sheet that reports the daily totals, so:
=Sheet1!D36
=Sheet2!D36
...
Not wanting to do all this manually... I tried to do it by incrementing...
However, "Sheet1" is the name of the sheet, not a value... so no incrementing.
So I tried to do it through concatenation... using a third column (A1 to A31) that lists the numbers from 1 to 31:
=CONCATENATE("=Sheet";A1;"!D36")
Which gives me correctly:
=Sheet1!D36
And then I can use the incrementing...
But it remains text... and Excel will not retrieve the value of D36 from Sheet1...
So that's my problem... I've searched a lot on Google... I've seen many answers talking about "INDIRECT()" but I don't see how that would help me.. and answers talking about macros and VBA scripts and others.. but that doesn't mean anything to me :)
It's my first day on Excel, so please don't go too hard on the technical jargon...
Thanks in advance :)
I have an Excel workbook with 32 sheets, one for each day and one for the total of the month.
I would like to have a column on the totals sheet that reports the daily totals, so:
=Sheet1!D36
=Sheet2!D36
...
Not wanting to do all this manually... I tried to do it by incrementing...
However, "Sheet1" is the name of the sheet, not a value... so no incrementing.
So I tried to do it through concatenation... using a third column (A1 to A31) that lists the numbers from 1 to 31:
=CONCATENATE("=Sheet";A1;"!D36")
Which gives me correctly:
=Sheet1!D36
And then I can use the incrementing...
But it remains text... and Excel will not retrieve the value of D36 from Sheet1...
So that's my problem... I've searched a lot on Google... I've seen many answers talking about "INDIRECT()" but I don't see how that would help me.. and answers talking about macros and VBA scripts and others.. but that doesn't mean anything to me :)
It's my first day on Excel, so please don't go too hard on the technical jargon...
Thanks in advance :)
4 réponses
Hello
1/in your total sheet, for example in column A
in A1 you write feuil1
you click and drag down on the black square at the bottom right of A1; you will get feuil2, feuil3, etc. in the column
2/in B1 you write:
=indirect(A1“!D36”)
and you double-click on the small black square of B1 (or drag down)
There you go
--
Best regards, Michel
1/in your total sheet, for example in column A
in A1 you write feuil1
you click and drag down on the black square at the bottom right of A1; you will get feuil2, feuil3, etc. in the column
2/in B1 you write:
=indirect(A1“!D36”)
and you double-click on the small black square of B1 (or drag down)
There you go
--
Best regards, Michel
