Excel help please.

I'm pretty sure you can solve this with a combination of the INDIRECT function and the ROW function.

I can't remember exactly how to format it but should be able to google it from there.

CONCATENATE might be another useful function, it's a while since I've done this

To build a cell refence with Indirect you use INDIRECT("D"&215)
If you're using this in the A1 I think you could use INDIRECT("D"&(208+(ROW(A1)*7))) and copy paste that down the list.

(ROW(a1) would be 1
when dragged down, row(b1) would be 2 etc)

Extend that to get sums etc ... I think.

Thanks for the replies Lori.

Sorry but I got dragged away from my PC after my initial post. On first look, what you have written went straight over my head but I will be able to sit down and look at it properly a bit later.

If you combine it with google and a blank sheet you'll probably do okay with it.

Indirect basically means that instead of using cell A1 you're using "A" and 1 ("A"&1) and you can add numbers to the one, so you can have cell A1+7 ("A"&(1+7)) and build a formula from there.

Check back in a few mins, I'm having a crack at this.

Okay, I think this works.

In A1 on a new sheet put:

=SUM(INDIRECT("Daily!D"&(208+(7*(ROW(A2))))):INDIRECT("Daily!D"&(214+(7*(ROW(A2))))))+SUM(INDIRECT("Daily!E"&(208+(7*(ROW(A2))))):INDIRECT("Daily!E"&(214+(7*(ROW(A2))))))

Copy that into A2, A3 etc and see if it has the desired effect, it's working on the one I knocked together to test on.

FFS

That actually has to go in A2, I copied the wrong line.

Put that lot in A2 and copy to A1, A3 etc

*Also, for some reason when copying from the forum it blocks out an extra cell. Hit the funny briefcase icon and use "match destination formatting"

This may be easier to do ~

use two helper columns, in the first lets say cell L1 enter 215 and fill down step 7
in the second helper column say Cell M1 enter 222 and fill down step 7

Now in your first cell this formula and fill down ~
=SUM(INDEX(D:D,L1):INDEX(E:E,M1))

Jesus! I thought I was asking something relatively simple! [smiley:crazy]

Okay, I had a crack with Lori’s formula and it works! Good stuff, thanks for that.

The only problem is if I need to know which cell a particular figure is coming from – I can’t immediately tell by looking at the formula. But it wouldn’t be too hard to work out.

Out of interest, I tried CM’s formula too. I couldn’t get it to work at first but then realised that the formula didn’t reference the Daily! sheet, so it should have looked like this:

=SUM(INDEX(Daily!D:D,P4):INDEX(Daily!E:E,Q4))

The P4 and Q4 are obviously different because they are the cells I used for my helper columns).

This works a little better for me because it is easier to see which cells a figure is coming from (by looking across to the helper columns).

Anyway, thanks to both of you for helping me out with that. If I spend a little more time working out what is happening with the above, I can think of a few instances where I can use it to save me some more time.

I am glad I asked for help otherwise I could have sat here until Christmas without working it out!

This might help a bit.

Click the Formulas tab at the top.
With this forum page open (so you can see the full formula) click on "Evaluate formula" from the excel menu, near the right hand side.

A popup should appear with the formula in. If you click evaluate, it will go through what it's doing step by step, so you can get an idea where each number is coming/going/whatever.
Might need to go through it a few times but I think that'll help the easy way.

Gin good to see you worked out the references.

looking back at my post I made an error with this line ~
in the second helper column say Cell M1 enter 222 and fill down step 7

Should have been ~
in the second helper column say Cell M1 enter 221 and fill down step 7

So if you used my formula you would have come up with the wrong answers.

Cheers.

Lori

Cheers, I will check that out.

Compound

Thanks - I had spotted that but thought it was churlish to point out!

Thanks again both of you for the help.

Okay, I’m a bit confused again.

I’m trying to do a similar thing in an adjacent cell using the same helper rows.
The original formula was:

=(SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)*-0.03))/2

So I tried to change this by following the previous example but couldn’t work out how to add the extra instruction (*-.03) on the second part.

Whilst trying to break it all down to work it out I started by inputting just the SUM parts of the formula:

=SUM(INDEX(Daily!G:G,P6):INDEX(Daily!E:E,Q6))

This gave an unexpected answer so to double check it I returned to the original part of the formula:

=SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)

Which gave a different answer again! So now I am really confused as I thought they should be the same.

Can somebody (Compound magic, Lori, anybody!) show me what I am doing wrong. I’ve been trying to work it out for a couple of hours now and its driving me mad.

Hi Gin,

Your very first formula ~
=SUM(Daily!D215:D221)+SUM(Daily!E215:E221)

Is the same as ~
=SUM(Daily!D215:E221)
Which my index did ~
=SUM(INDEX(Daily!D:D,P4):INDEX(Daily!E:E,Q4))

Your new formula ~
=SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)
Written as you have tried with sum index spans also column F, which you don't want.

So you would write it like this ~

=SUM(INDEX(Daily!E:E,P6):INDEX(Daily!E:E,Q6))+SUM(INDEX(Daily!G:G,P6):INDEX(Daily!G:G,Q6))

That adds the rows values in column E signified by the row numbers in P6 to Q6, then adds to that the same row values for column G.

Cheers.

Ah! thats worked. I was misunderstanding how the original formula you gave me was working.

With a bit of tweaking my original formula (=(SUM(Daily!G229:G235)+(SUM(Daily!E229:E235)*-0.03))/2) has become:

=((SUM(INDEX(Daily!E:E,P6):INDEX(Daily!E:E,Q6))*-0.03)+SUM(INDEX(Daily!G:G,P6):INDEX(Daily!G:G,Q6)))/2

Which works fine.

Many thanks CM - much appreciated. I can eat my lunch in peace now.