copying formula pattern

**marvinks** · 04-11-2007, 01:01 AM

Hi everyone

My work book has 13 worksheets. One for each month on which I record the monthly sales and purchases and the 13th sheet has a summary of sales such as BI, purchases , sales and EI for each month. This is broken down for 12 different items that we sell. On the summary worksheet I am entering a formula for purchases under the Jan columun as =Jan!B5, then for Feb column =Feb!B5 and so on. If I select these cells and drag it does not recongize the pattern of my formula. Is there anyhow I can fill this formula in faster without having to flip back and forth to the worksheets.

Thanks
Marvin

**starguy** · 04-11-2007, 10:36 AM

Originally Posted by marvinks

Hi everyone

My work book has 13 worksheets. One for each month on which I record the monthly sales and purchases and the 13th sheet has a summary of sales such as BI, purchases , sales and EI for each month. This is broken down for 12 different items that we sell. On the summary worksheet I am entering a formula for purchases under the Jan columun as =Jan!B5, then for Feb column =Feb!B5 and so on. If I select these cells and drag it does not recongize the pattern of my formula. Is there anyhow I can fill this formula in faster without having to flip back and forth to the worksheets.

Thanks
Marvin

can you attach a sample file with some data and summary sheet in it and also point out where in summary sheet you want to put the formula?

**marvinks** · 04-11-2007, 04:26 PM

Hi Starguy or others
I attached a file. In the sheet called Inv$ I want to pull the inventory purchase amount for the item (barley) for Jan from the Jan worksheet(cell Jan!B5) This is the same for all the months plus all the inventory items. If I have to do these all manually it will take a while. Is there anyhow to fill these formulas in.

Thanks

**kraljb** · 04-11-2007, 05:04 PM

Try using the INDIRECT formula to help you...

Assume in A1 on the summery sheet you has Jan and B1 has Feb etc.

=INDIRECT(A1 & "!B5") and pull it across.

Enjoy.

**starguy** · 04-12-2007, 04:20 AM

Originally Posted by marvinks

Hi Starguy or others
I attached a file. In the sheet called Inv$ I want to pull the inventory purchase amount for the item (barley) for Jan from the Jan worksheet(cell Jan!B5) This is the same for all the months plus all the inventory items. If I have to do these all manually it will take a while. Is there anyhow to fill these formulas in.

Thanks

see in attached file for INDIRECT formula implementation.