Formula Not Autofilling

**Laura88** · 10-11-2013, 06:29 AM

Hi

I am new to in depth Excel (I know the basics and some formulas)

I have an issue when I am dragging a formula across, because its a special formula it doesn't autofill like =A1+b1 formula would.

In essence what I am trying to do is this;

Week 2
=IFERROR(('Day To Day'!HQ7*'Day To Day'!HQ5+'Day To Day'!HR7*'Day To Day'!HR5+'Day To Day'!HS7*'Day To Day'!HS5+'Day To Day'!HT7*'Day To Day'!HT5+'Day To Day'!HU7*'Day To Day'!HU5+'Day To Day'!HV7*'Day To Day'!HV5+'Day To Day'!HW7*'Day To Day'!HW5)/'Week On Week'!AH5,"-")

So to explain

Row 7 is the average amount of apples
Row 5 is the total of apples sold

Average amount of Apples * Total of apples sold Per day (7) then / the total of apples sold in the week

I need to input the 1st part of the formula per day so hence why it is so long, but when I drag this along it doesn't populate accordingly because I need the next cell to be the total of the next week so from the example I need week 3 which should look like this;

=IFERROR(('Day To Day'!HX7*'Day To Day'!HX5+'Day To Day'!HY7*'Day To Day'!HY5+'Day To Day'!HZ7*'Day To Day'!HZ5+'Day To Day'!IA7*'Day To Day'!IA5+'Day To Day'!IB7*'Day To Day'!IB5+'Day To Day'!IC7*'Day To Day'!IC5+'Day To Day'!ID7*'Day To Day'!ID5)/'Week On Week'!AI5,"-")

But when I drag across it ends up like this;
=IFERROR(('Day To Day'!HF7*'Day To Day'!HF5+'Day To Day'!HG7*'Day To Day'!HG5+'Day To Day'!HH7*'Day To Day'!HH5+'Day To Day'!HI7*'Day To Day'!HI5+'Day To Day'!HJ7*'Day To Day'!HJ5+'Day To Day'!HK7*'Day To Day'!HK5+'Day To Day'!HL7*'Day To Day'!HL5)/'Week On Week'!AI5,"-")

I have been told that I cannot get this to work using formulas and that I would need to use a VBA to get this to work, but I do not know anything about VBA. As you can imagine this is taking up a lot of my time having to do this manually for 52 weeks of the year.... and I have 3 of them to do.

Can anyone offer any advice?

*Please note this isn't in relation to selling apples I have used these as an example

*

Thanks

Laura

**galvinpaddy** · 10-11-2013, 06:41 AM

looking at the explanation, it may be easier to load a sample file for someone to have a clearer understanding of what you are working with

**galvinpaddy** · 10-11-2013, 08:59 AM

Hi, if you want to load a file, be sure to remove confidential info ;-)
The file only needs to be a snippet of the sheet to allow anyone who offers to help the ease of helping.

All the important and confidential data can be replaced with fictional. Without it you may find (or may not) that people will tend to not offer much help as we dont have a great deal to work with