Copying these formulas to adjacent cells across the worksheet.

=SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!N$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!G$2:G$2500) Cell A1
=SUMPRODUCT((LinenData!D$2:D$2030=Totals!N$1)*(LinenData!C$2:C$2030=Totals!$A3),LinenData!H$2:H$2030) Cell B1

Is here is my dilemma! The two formulas are in their respective cells listed above. I need to change the formula in B1 to reflect =SUMPRODUCT((LinenData!D$2:D$2500=Totals!N$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!H$2:H$2500) the cell address then I need to copy it down to the next 25 rows which I can do, problem rest when I try to copy the formulas across the worksheet because it changes the data. Is there a way to copy these formulas faster than to have to manually go in and change them.

So then C1 would have =SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!P$1)*(LinenData!C$2:C$2500=Totals!$A$3),LinenData!G$2:G$2500) and D1 would have =SUMPRODUCT((LinenData!D$2:D$2500=Totals!P$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!H$2:H$2500) then all the way across the worksheet it would continue to increment the cell location in the formula by every other cell (Totals!P$1)

Hope this makes sense??

LeapingLizard

Hi LeapingLizard,

First of all you do not need the double-negation in your formula, removing that will make your formula more consistent.

It's hard to imagine the pattern of your formula by just looking at the 4 formulas above, could you manually fill the formulas for cells below so that members could have a better view of the scenario?
A1=
B1=
C1=
D1=
E1=
F1=

A2=
B2=
C2=
D2=
E2=
F2=


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Yes Alvin-Chung,

Here is how it would look. This information will be couple over around 40-60 columns then down about the same amount of rows.


A1=SUMPRODUCT((LinenData!D$2:D$2500=Totals!D$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!G$2:G$2500)
B1=SUMPRODUCT((LinenData!D$2:D$2500=Totals!D$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!H$2:H$2500)
C1=SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!F$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!G$2:G$2500)
D1=SUMPRODUCT((LinenData!D$2:D$2500=Totals!F$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!H$2:H$2500)
E1=SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!H$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!G$2:G$2500)
F1=SUMPRODUCT((LinenData!D$2:D$2500=Totals!H$1)*(LinenData!C$2:C$2500=Totals!$A3),LinenData!H$2:H$2500)

A2=SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!D$1)*(LinenData!C$2:C$2500=Totals!$A4),LinenData!G$2:G$2500)
B2=SUMPRODUCT((LinenData!D$2:D$2500=Totals!D$1)*(LinenData!C$2:C$2500=Totals!$A4),LinenData!H$2:H$2500)
C2=SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!F$1)*(LinenData!C$2:C$2500=Totals!$A4),LinenData!G$2:G$2500)
D2=SUMPRODUCT((LinenData!D$2:D$2500=Totals!F$1)*(LinenData!C$2:C$2500=Totals!$A4),LinenData!H$2:H$2500)
E2=SUMPRODUCT(--(LinenData!D$2:D$2500=Totals!H$1)*(LinenData!C$2:C$2500=Totals!$A4),LinenData!G$2:G$2500)
F2=SUMPRODUCT((LinenData!D$2:D$2500=Totals!H$1)*(LinenData!C$2:C$2500=Totals!$A4),LinenData!H$2:H$2500)


Hope this helps! Thanks

LeapingLizard

Hi LeapingLizard, is this what you're looking for?

Formula:

Please Login or Register  to view this content.

Then copy across rest of the cells.


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hello,


I tried this and it did not work. Sorry! Any other suggestions. It changed the value that is currently in that cell to 0.

LeapingLizard

Hi LeapingLizard, could you evaluate the formula and share which section that goes wrong?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Originally Posted by martindwilson

i can see you are missing a bit
INDIRECT(ADDRESS(1,2+ROUNDUP(COLUMN()/2,0)*2,,,"Totals"))

Oh dear, how could I missed that out
Thanks Martin.

Hi LeapingLizard, could you try again if this is working in your actual work?

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi again,


I have copied what you sent me into A1 and it through a #value error. Does it matter how it is formatted?

Thanks,

Dan

Hi Dan,

What is the result you get for below?

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Alvin,

It is number that is pulled from comparing cell contents within each worksheet. I have it formatted as general currently. Would it help to declare the type of number whether currency or numeric? The number that is pulled is displayed as 90.31. I don't know if this will help. I am just looking for a way to copy the formula across the cells in the row A1 through DF1 without having to manually edit where to pull the data from LinenData!D$2:D$2500. Currently it shows as LinenData!D$2:D$2030. Because of gathering more data as we get busier I have to extend the input range. Also, for me to copy the formula across the sheet, I can't, because it is not sequential. It is every other column that the information is pulled from. As you see in the example I sent above.

I hope this all makes sense. Essentially, the formula compares cells when it finds the matches it displays the value associated with it.

Thanks,

LeapLizard

Yea, that's the purpose of the formula proposed

Could you try change the range of my formula from 2500 to 4 (e.g. $C$2500 become $C$4) for troubleshooting purposes, then use the Evaluate function to step through the details and see where you're getting the error?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

So LinenData is a sheet that has a data base which orders are made everyday. So for instance each day, company A, orders towels at different quantities and the totals sheet calculates how many towels throughout the month did Company A order. So the Company or Department when the order the formula finds all Towel orders in cells(c$2:c$2500) for company A in cells(d$2:d$2500) and totals up number of towels ordered for company A was 1500 and stores it in the totals sheet.
The totals sheet has a column for every company that orders and all the products they order and as the orders increase the totals page keeps that running total.

With that said, everyday the totals sheet will have an incremented number of towels that they've purchased. So day 1 (5), day 2 (25), day 3 (10) and day 4 (30) total towels for company A is stored in totals sheet in d$3 (70). Then company b total number of towels for the month is stored in F$3 and so on. In order to adjust the row to D$2500 from d$2030 because of increased data entries I would have to physically go in and change every formula.

I hope this makes sense. If not maybe I will attach a shorten version for your viewing.

Let me know,

Thanks

LeapingLizards

Hi Alvin,

Just wondering if you had read my reply.

Thanks,

LeapingLizard

Hi LeapingLizard,

Just read your message, I believe we've come to a stage where sample file with your working formula (shorter range) is required.


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Okay! So with the totals sheet, the formula that is in D3 and E3 colored in red need copied all the way across the sheet in the same row to update and to reflect D2:D2500 in the formula. It will copy down just fine. The way it is set up it will not copy all the way across correctly to reflect the formulas in D3 and E3.