There are 86 sheets in 1 book.. i will PM you

Hi,

I am having kind of the same problem, so please could you put the solution in case there is one...but ok i have only 25 workbooks and i need to pollute them in different worksheet...and i do the first myself but is like too much work to do for 50 sheets.

sorry for the interference!

thank you a lot .

I dont really have a solution. I had to manually do the first then i copied the formula as text into the other columns then did a find/replace to relace the "Sheet" name. Then a text to columns to get the text to convert back to general.

You shouldn't have had to do all that stuff with text formats. As long as the formulas were set for the first sheet and references locked so the column/row can be dragged sideways/downward (depending on your setup) you could just use find and replace on each column/row in turn to change the sheet reference to the next and the next. It's a horrid workaround, but probably easier than the alternative if you only had to do it once.

The alternative would be writing the formulas using =INDIRECT() to reference each sheet name with a column/row header. It's easy enough to do if you know how to use it, but without example sheets I couldn't tell you how to set it up.

Hope it's of some help at least.

S.

It kept tying to get me to link to a worksheet when i did the formula then tried to find/replace within the formula. I found it just as easy to create the first set of formulas, set as text and copy all those into my columns then do the find/replace. it worked and only took 10 minutes to do 85 sheets so i wasnt complaining much

Very bizarre. I just tried it on your file and got nothing of the sort.
I've emailed it back to you so you can give it a try. Perhaps you were doing it a different way to me. Not that it matter as you've sorted it now anyway :)

Spencer,

I know indirect formula but is not convienient as you might have a lot of summaries in different ways so it takes time...i don't know if you can create a macro if you do your first template manually...maybe is not possible.

have a look at this thread please just to have an idea!!

http://www.excelbanter.com/showthread.php?t=446032


Thank you