CrazyAMonkey
New Member
- Joined
- Nov 3, 2009
- Messages
- 19
Hello everyone, i really hope someone can help me as i have been working on this for 2 nights now with no solution!!
I have a data worksheet that contains basic row/column information. The row contains a comprehensive list of components (19) that are required for assembly of more complex items. The list of complex items (90) are running down the column.
The resulting matrix is populated with the number of each basic items required for each complicated item. Each complex item only requires 5-6 types of component so there are blanks and a few numbers in each row.
I am trying to create a formulae to look across each row, detect numbers over 0, then multiply each one by a unique cost and sum them together.
At the minute i have a very complicated formula that is not copying well so i am looking for a better way to do it. Current formula :-
=(B!B3*A!G3)+(B!C3*A!G4)+(B!D3*A!G5)+(B!E3*A!G6)+(B!F3*A!G7)+(B!G3*A!G8)+(B!H3*A!G9)+(B!I3*A!G10)+(B!J3*A!G11)+(B!K3*A!G12)+(B!L3*A!G13)+(B!M3*A!G14)+(B!N3*A!G15)+(B!O3*A!G16)+(B!P3*A!G17)+(B!Q3*A!G18)+(B!R3*A!G19)+(B!S3*A!G20)+(B!T3*A!G21)
As you see i have simply multiplied and added EVERY bloomin cell. When i drag the formula down to populate lower cells, the starting cell in the G column increments giving a false final figure.
Is this possible? Is there a better way to do it?!
Thank you very much for reading, hope it makes sense!
I have a data worksheet that contains basic row/column information. The row contains a comprehensive list of components (19) that are required for assembly of more complex items. The list of complex items (90) are running down the column.
The resulting matrix is populated with the number of each basic items required for each complicated item. Each complex item only requires 5-6 types of component so there are blanks and a few numbers in each row.
I am trying to create a formulae to look across each row, detect numbers over 0, then multiply each one by a unique cost and sum them together.
At the minute i have a very complicated formula that is not copying well so i am looking for a better way to do it. Current formula :-
=(B!B3*A!G3)+(B!C3*A!G4)+(B!D3*A!G5)+(B!E3*A!G6)+(B!F3*A!G7)+(B!G3*A!G8)+(B!H3*A!G9)+(B!I3*A!G10)+(B!J3*A!G11)+(B!K3*A!G12)+(B!L3*A!G13)+(B!M3*A!G14)+(B!N3*A!G15)+(B!O3*A!G16)+(B!P3*A!G17)+(B!Q3*A!G18)+(B!R3*A!G19)+(B!S3*A!G20)+(B!T3*A!G21)
As you see i have simply multiplied and added EVERY bloomin cell. When i drag the formula down to populate lower cells, the starting cell in the G column increments giving a false final figure.
Is this possible? Is there a better way to do it?!
Thank you very much for reading, hope it makes sense!
