Dragging thousands of formulas

[udrmichelle]

I'm so thankful for all of the help that I've received on this site. I'm hopeful that someone can figure this one out for me. I don't even know if it's possible with how it's set up in this book. I've attached a small example.

Two sheets. I need help with the cells I colored blue in the Community List sheet.

In All Data sheet, E:I adds up the cells to the right, by their value. E2 is a total of J2 and O2; it shows us how many 5's we got in Jan 2013.

I need to get that data into the format on the Community List sheet. C2 on Community List is good to go. But I want to be able to drag this formula over and down, making sure that for D2 on Community List it pulls the data from All Data F2 and so on.example3.xlsx

[Pete_UK]

You might want to change the formula in D2 of the AllData sheet to this:

=A2&"_"&TEXT(C2,"mmm yyyy")

(you had 5 y's). Then copy down.

You can put this formula in C2 of the Community sheet:

=INDEX('All Data'!E:E,MATCH($A2&"_"&SUBSTITUTE(C$1,"Total "&6-COLUMNS($C:C)&" ",""),'All Data'!$D:$D,0))

and copy that across to I2. Then put this formula in H2:

=INDEX('All Data'!E:E,MATCH($A2&"_"&SUBSTITUTE(H$1,"Total "&6-COLUMNS($H:H)&" ",""),'All Data'!$D:$D,0))

and copy that across to L2.

Then you can copy the formulae in C2:L2 down as far as you need to.

Hope this helps.

Pete

[udrmichelle]

thank you. I haven't tried this out yet because I need to change the formula for the actual book. What does the 6-columns do?

[Pete_UK]

The SUBSTITUTE term removes Total 5 from C1, then Total 4 from D1, and so on, so you can compare the dates - by having it as "Total " & 6-COLUMNS etc., this will give you 5, 4, 3 etc. as you copy it across.

Hope this helps.

Pete

[FlameRetired]

For a slightly different approach this in C2 of Community List sheet and copy across to L2.

Please Login or Register  to view this content.

[udrmichelle]

Thanks for both of your help. I tried both formulas and couldn't get it to work. We did get the data we needed, it just took a lot longer. Thanks for trying to help me.