| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complex custom fill series with formula
I always seem to waste time manually changing filled in series so I'm hoping
someone here has an answer: I have a formula in A1: =SUMPRODUCT('Sheet1'!D4:D500*'Sheet1'!$B4:$B500) I want to have it so column B stays constant but in each column I move across the earlier reference goes up by 3 columns: in B1: =SUMPRODUCT('Sheet1'!G4:G500*'Sheet1'!$B4:$B500) in C1: =SUMPRODUCT('Sheet1'!J4:J500*'Sheet1'!$B4:$B500) and so on... And if you can answer that: I'd also like to know how to do the above if the second reference ('Sheet1'!$B4:$B500) didn't stay constant but also had to go up two columns with each one I moved across so D4:D500 in B1, F4:F500 in C1 etc. Any response greatly appreciated as I think knowing this would save me a lot of time! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complex custom fill series with formula
For your first question:
=SUMPRODUCT(Sheet1!$B4:$B500,INDEX(Sheet1!$B4:$Z50 0,,COLUMNS($A1:A1)*3)) Copied across as needed. Adjust for the correct last column in the INDEX function. For your second question: so D4:D500 in B1, F4:F500 in C1 What would be the formula in A1? -- Biff Microsoft Excel MVP "Josh Craig" wrote in message ... I always seem to waste time manually changing filled in series so I'm hoping someone here has an answer: I have a formula in A1: =SUMPRODUCT('Sheet1'!D4:D500*'Sheet1'!$B4:$B500) I want to have it so column B stays constant but in each column I move across the earlier reference goes up by 3 columns: in B1: =SUMPRODUCT('Sheet1'!G4:G500*'Sheet1'!$B4:$B500) in C1: =SUMPRODUCT('Sheet1'!J4:J500*'Sheet1'!$B4:$B500) and so on... And if you can answer that: I'd also like to know how to do the above if the second reference ('Sheet1'!$B4:$B500) didn't stay constant but also had to go up two columns with each one I moved across so D4:D500 in B1, F4:F500 in C1 etc. Any response greatly appreciated as I think knowing this would save me a lot of time! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complex custom fill series with formula
Hi Biff,
Just about to check if your first one works... For the second one assume A1 is the same as in the first example (except no $ for the Bs) "T. Valko" wrote: For your first question: =SUMPRODUCT(Sheet1!$B4:$B500,INDEX(Sheet1!$B4:$Z50 0,,COLUMNS($A1:A1)*3)) Copied across as needed. Adjust for the correct last column in the INDEX function. For your second question: so D4:D500 in B1, F4:F500 in C1 What would be the formula in A1? -- Biff Microsoft Excel MVP "Josh Craig" wrote in message ... I always seem to waste time manually changing filled in series so I'm hoping someone here has an answer: I have a formula in A1: =SUMPRODUCT('Sheet1'!D4:D500*'Sheet1'!$B4:$B500) I want to have it so column B stays constant but in each column I move across the earlier reference goes up by 3 columns: in B1: =SUMPRODUCT('Sheet1'!G4:G500*'Sheet1'!$B4:$B500) in C1: =SUMPRODUCT('Sheet1'!J4:J500*'Sheet1'!$B4:$B500) and so on... And if you can answer that: I'd also like to know how to do the above if the second reference ('Sheet1'!$B4:$B500) didn't stay constant but also had to go up two columns with each one I moved across so D4:D500 in B1, F4:F500 in C1 etc. Any response greatly appreciated as I think knowing this would save me a lot of time! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complex custom fill series with formula
Hi Biff,
I got the first one to work but I had to replace Z with a much higher column letters (DD). Do you know what the maximum possible column letters would be so I can put that in? "Josh Craig" wrote: Hi Biff, Just about to check if your first one works... For the second one assume A1 is the same as in the first example (except no $ for the Bs) "T. Valko" wrote: For your first question: =SUMPRODUCT(Sheet1!$B4:$B500,INDEX(Sheet1!$B4:$Z50 0,,COLUMNS($A1:A1)*3)) Copied across as needed. Adjust for the correct last column in the INDEX function. For your second question: so D4:D500 in B1, F4:F500 in C1 What would be the formula in A1? -- Biff Microsoft Excel MVP "Josh Craig" wrote in message ... I always seem to waste time manually changing filled in series so I'm hoping someone here has an answer: I have a formula in A1: =SUMPRODUCT('Sheet1'!D4:D500*'Sheet1'!$B4:$B500) I want to have it so column B stays constant but in each column I move across the earlier reference goes up by 3 columns: in B1: =SUMPRODUCT('Sheet1'!G4:G500*'Sheet1'!$B4:$B500) in C1: =SUMPRODUCT('Sheet1'!J4:J500*'Sheet1'!$B4:$B500) and so on... And if you can answer that: I'd also like to know how to do the above if the second reference ('Sheet1'!$B4:$B500) didn't stay constant but also had to go up two columns with each one I moved across so D4:D500 in B1, F4:F500 in C1 etc. Any response greatly appreciated as I think knowing this would save me a lot of time! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
complex custom fill series with formula
It depends on what version of Excel you're using. If you're not using every
column it's a waste of resouces to include those that are not actually being used. Excel 2007 last column XFD All other versions IV I'm still not sure about your second question. Tell me specifically what columns the formula in cell A1 should reference. I'm calling it a day so someone else will have to take over. If not I'll check back tomorrow. -- Biff Microsoft Excel MVP "Josh Craig" wrote in message ... Hi Biff, I got the first one to work but I had to replace Z with a much higher column letters (DD). Do you know what the maximum possible column letters would be so I can put that in? "Josh Craig" wrote: Hi Biff, Just about to check if your first one works... For the second one assume A1 is the same as in the first example (except no $ for the Bs) "T. Valko" wrote: For your first question: =SUMPRODUCT(Sheet1!$B4:$B500,INDEX(Sheet1!$B4:$Z50 0,,COLUMNS($A1:A1)*3)) Copied across as needed. Adjust for the correct last column in the INDEX function. For your second question: so D4:D500 in B1, F4:F500 in C1 What would be the formula in A1? -- Biff Microsoft Excel MVP "Josh Craig" wrote in message ... I always seem to waste time manually changing filled in series so I'm hoping someone here has an answer: I have a formula in A1: =SUMPRODUCT('Sheet1'!D4:D500*'Sheet1'!$B4:$B500) I want to have it so column B stays constant but in each column I move across the earlier reference goes up by 3 columns: in B1: =SUMPRODUCT('Sheet1'!G4:G500*'Sheet1'!$B4:$B500) in C1: =SUMPRODUCT('Sheet1'!J4:J500*'Sheet1'!$B4:$B500) and so on... And if you can answer that: I'd also like to know how to do the above if the second reference ('Sheet1'!$B4:$B500) didn't stay constant but also had to go up two columns with each one I moved across so D4:D500 in B1, F4:F500 in C1 etc. Any response greatly appreciated as I think knowing this would save me a lot of time! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a custom fill series or sorting order | Excel Worksheet Functions | |||
| fill series, custom format question | Excel Discussion (Misc queries) | |||
| How do I create a custom fill series for dates? | Excel Discussion (Misc queries) | |||
| Custom fill series? | Excel Discussion (Misc queries) | |||
| I've created a custom fill series. Now I don't know how to use i. | Excel Discussion (Misc queries) | |||
Linear Mode
