Replace formula?

[qussai]

Hi,

On one cell I have this formula:

='Pasta March'!P17

On the cell below it I manually write

='Pasta April'!P17

I was wondering if there is a way to automate this process as I have to do this process for more than 100 cells?

[Mallycat]

I suspect that what ever you are doing, there is an easier way. But automating what you are asking for is surely not it. What is it you are trying to do?

[darokal]

Yes. Write:

If you have a table like (2 columns)
A | B
Month | Total
Jan | ='PastaJan'!P17
Feb | ='PastaFeb'!P17
Mar | ='PastaMar'!P17
Apr | ='PastaApr'!P17
May | ='PastaMay'!P17

Then write in B2
=INDIRECT("Pasta" & A2 & "!P17")

Where A2 is a Cell that contains the month written in words (like "Jan").
This formula will retrieve the value of a reference written as a string. In this case the string will be "PastaJan!P17" so it will retrieve the value there.

I reccomend that you get rid of the spaces in the sheet names, otherwise you must do a small twitch to that formula (not worth it). Also if it works reply back cause ther's also 1 more thing you should add to the formula for it to be really complete and reliable.

EDIT: Also if you dont wanna write January, Febraury, but instead have dates in the cells, its possible to make this work. Just tell me or upload an example.

[Paul]

You can use this formula without a helper column or table:

=INDIRECT("'Pasta "&TEXT(DATE(2012,ROW(A3),1),"mmmm")&"'!P17")

If you fill that formula down rows it will change from March to April, May, etc. (You can fill it upward up to two rows as well to get January and February.)