Quick way to fill formulas?



Hi, I’m trying to do something that might be difficult to explain
without actually seeing the file I’m working with. I have a workbook
with a sheet where the data is set up like this:

10 20 30
40 50 60
70 80 90

In other words, every other column has data. A blank column separates
each column of numbers. And I have another sheet that needs formulas
something like this:

=100*Sheet1!A1 =100*Sheet1!A2 =100*Sheet1!A3
=100*Sheet1!C1 =100*Sheet1!C2 =100*Sheet1!C3
=100*Sheet1!E1 =100*Sheet1!E2 =100*Sheet1!E3

In other words, I want to be able to copy the formulas down for each
row in each of the columns. But, problem number 1 is that I am trying
to copy a formula down rows when the data is actually going across
columns from Sheet1, and problem number 2 is that there is a blank
column between each column of data on Sheet1. I guess it could be
done manually, but these formulas need to be copied down for hundreds
of rows and this is only one of many similar worksheets. Needless to
say, doing it manually would take a very long time. Is there a quick
way to autofill formulas like this with a pattern of going across and
skipping every other column? I might be missing something obvious,
but I just can’t figure out a quick way to do this. Does anybody know
of one? Thanks in advance for any information, and I’m sorry if my
explanation was confusing.


Put this formula in A1 of Sheet2:


You will need to change the first range to suit the extent of your
data in Sheet1 - I've just used the range A1:N39. Make sure that you
use absolute addresses (i.e. the $ symbols).

Then you can copy this formula across and down as far as you need to.

Hope this helps.



Hi, Pete, thank you very much for your response. I tried your formula
and it worked great. :)

Thanks again! :)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question