|
|
|
|
|
|
|
| |
How about creating Excel names with a dynamic definition, such as
=OFFSET($L$1,,,COUNTA($L:$L),1)
for the employees, then use that name in the formulae.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"Ronny Hamida" <RonnyHamida@discussions.microsoft.com> wrote in message
news:78B25E97-06B5-4340-A103-6BCDEAFE3D83@microsoft.com...
> Hi there!
>
> I have formulas like this in a worksheet:
>
> =SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
> =C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))
>
> When the columns on "Sheet2" change, this wouldn't be very "modular" or
> friendly.
>
> I was wondering if I can incorporate something like INDEX or MATCH in
these
> formulas. Here's some detail:
>
> "Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
> the header in row 1, but the column can be different every time. Can
this
> be something like "MATCH(xxxxx)"? (I'm not too familiar with these
formulas.)
>
> The ":$L462" can also be different - This is the end of the column. It's
> never more than 6000 rows, but it does change every time.
>
> (Just ignore the $B3 in the formulas - It's a reference point for the user
> to change which data they want to count/sum/etc.)
>
> If I knew how to change those two formulas by adding something like MATCH
or
> INDEX(MATCH), I think I can manipulate the rest of my formulas
accordingly,
> but I'm not sure how to use them in a format like this.
>
> Any ideas?
>
> Thank you!
>
> Ronny Hamida
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
In most cases, the EmployeeName is not always in the "L" column, however. I
would love to define dynamic names, however what would I do in a case of if
the "EmployeeName" column is moved from column L to column J? It varies
every time. :(
"Bob Phillips" wrote:
> How about creating Excel names with a dynamic definition, such as
>
> =OFFSET($L$1,,,COUNTA($L:$L),1)
>
> for the employees, then use that name in the formulae.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ronny Hamida" <RonnyHamida@discussions.microsoft.com> wrote in message
> news:78B25E97-06B5-4340-A103-6BCDEAFE3D83@microsoft.com...
> > Hi there!
> >
> > I have formulas like this in a worksheet:
> >
> > =SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
> > =C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))
> >
> > When the columns on "Sheet2" change, this wouldn't be very "modular" or
> > friendly.
> >
> > I was wondering if I can incorporate something like INDEX or MATCH in
> these
> > formulas. Here's some detail:
> >
> > "Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
> > the header in row 1, but the column can be different every time. Can
> this
> > be something like "MATCH(xxxxx)"? (I'm not too familiar with these
> formulas.)
> >
> > The ":$L462" can also be different - This is the end of the column. It's
> > never more than 6000 rows, but it does change every time.
> >
> > (Just ignore the $B3 in the formulas - It's a reference point for the user
> > to change which data they want to count/sum/etc.)
> >
> > If I knew how to change those two formulas by adding something like MATCH
> or
> > INDEX(MATCH), I think I can manipulate the rest of my formulas
> accordingly,
> > but I'm not sure how to use them in a format like this.
> >
> > Any ideas?
> >
> > Thank you!
> >
> > Ronny Hamida
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I forgot to ask with this: OFFSET wouldn't cover defining/finding the last
row of the column, would it? It looks like it would be great to just define
the header row, which is still a step further! :)
"Bob Phillips" wrote:
> How about creating Excel names with a dynamic definition, such as
>
> =OFFSET($L$1,,,COUNTA($L:$L),1)
>
> for the employees, then use that name in the formulae.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ronny Hamida" <RonnyHamida@discussions.microsoft.com> wrote in message
> news:78B25E97-06B5-4340-A103-6BCDEAFE3D83@microsoft.com...
> > Hi there!
> >
> > I have formulas like this in a worksheet:
> >
> > =SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
> > =C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))
> >
> > When the columns on "Sheet2" change, this wouldn't be very "modular" or
> > friendly.
> >
> > I was wondering if I can incorporate something like INDEX or MATCH in
> these
> > formulas. Here's some detail:
> >
> > "Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
> > the header in row 1, but the column can be different every time. Can
> this
> > be something like "MATCH(xxxxx)"? (I'm not too familiar with these
> formulas.)
> >
> > The ":$L462" can also be different - This is the end of the column. It's
> > never more than 6000 rows, but it does change every time.
> >
> > (Just ignore the $B3 in the formulas - It's a reference point for the user
> > to change which data they want to count/sum/etc.)
> >
> > If I knew how to change those two formulas by adding something like MATCH
> or
> > INDEX(MATCH), I think I can manipulate the rest of my formulas
> accordingly,
> > but I'm not sure how to use them in a format like this.
> >
> > Any ideas?
> >
> > Thank you!
> >
> > Ronny Hamida
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
Maybe something like this will help. First set up some named ranges like so:
Amt =OFFSET(Sheet2!$A$2,,MATCH("Amount",ColHead,0) - 1,COUNTA(Sheet2!$A:$A)-1)
ColHead =OFFSET(Sheet2!$A$1,,,1,COUNTA(Sheet2!$1:$1))
EmpName =OFFSET(Sheet2!$A$2,,MATCH("Employee Name",ColHead,0) -
1,COUNTA(Sheet2!$A:$A)-1)
Then with the spreadsheet looking something like so:
Date Code Employee Name Amount
1-May AAB Kevin Vaughn 50
3-May BBA Tom Jefferson 100
5-May CCD Hank Williams 20
6-May DDA Kevin Vaughn 1000
10-May AAB Tom Jefferson 750
12-May CCD Kevin Vaughn 99
13-May DDA Tom Jefferson 15
15-May AAB Hank Williams 33
20-May EEG Tom Arnold 150
Employee Name Amount
Kevin Vaughn 1149
And a formula like so:
=SUMPRODUCT(--(EmpName = B13), (Amt))
Note: I didn't analyze what you were asking, just sort of skimmed it, but
saw you were looking at dynamic ranges. Colhead is set up so that the number
of Column Headings could be variable (since it appeared from one of your
answers, Employee Name could jump around, I figured the number of columns
could vary.) Also note that I did the counta on column a. Hopefully you
have a column with no blanks interspersed among the data.
HTH
--
Kevin Vaughn
"Ronny Hamida" wrote:
> I forgot to ask with this: OFFSET wouldn't cover defining/finding the last
> row of the column, would it? It looks like it would be great to just define
> the header row, which is still a step further! :)
>
> "Bob Phillips" wrote:
>
> > How about creating Excel names with a dynamic definition, such as
> >
> > =OFFSET($L$1,,,COUNTA($L:$L),1)
> >
> > for the employees, then use that name in the formulae.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Ronny Hamida" <RonnyHamida@discussions.microsoft.com> wrote in message
> > news:78B25E97-06B5-4340-A103-6BCDEAFE3D83@microsoft.com...
> > > Hi there!
> > >
> > > I have formulas like this in a worksheet:
> > >
> > > =SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
> > > =C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))
> > >
> > > When the columns on "Sheet2" change, this wouldn't be very "modular" or
> > > friendly.
> > >
> > > I was wondering if I can incorporate something like INDEX or MATCH in
> > these
> > > formulas. Here's some detail:
> > >
> > > "Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
> > > the header in row 1, but the column can be different every time. Can
> > this
> > > be something like "MATCH(xxxxx)"? (I'm not too familiar with these
> > formulas.)
> > >
> > > The ":$L462" can also be different - This is the end of the column. It's
> > > never more than 6000 rows, but it does change every time.
> > >
> > > (Just ignore the $B3 in the formulas - It's a reference point for the user
> > > to change which data they want to count/sum/etc.)
> > >
> > > If I knew how to change those two formulas by adding something like MATCH
> > or
> > > INDEX(MATCH), I think I can manipulate the rest of my formulas
> > accordingly,
> > > but I'm not sure how to use them in a format like this.
> > >
> > > Any ideas?
> > >
> > > Thank you!
> > >
> > > Ronny Hamida
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
This worked, thank you!
"Kevin Vaughn" wrote:
> Maybe something like this will help. First set up some named ranges like so:
>
> Amt =OFFSET(Sheet2!$A$2,,MATCH("Amount",ColHead,0) - 1,COUNTA(Sheet2!$A:$A)-1)
> ColHead =OFFSET(Sheet2!$A$1,,,1,COUNTA(Sheet2!$1:$1))
> EmpName =OFFSET(Sheet2!$A$2,,MATCH("Employee Name",ColHead,0) -
> 1,COUNTA(Sheet2!$A:$A)-1)
>
>
> Then with the spreadsheet looking something like so:
>
> Date Code Employee Name Amount
> 1-May AAB Kevin Vaughn 50
> 3-May BBA Tom Jefferson 100
> 5-May CCD Hank Williams 20
> 6-May DDA Kevin Vaughn 1000
> 10-May AAB Tom Jefferson 750
> 12-May CCD Kevin Vaughn 99
> 13-May DDA Tom Jefferson 15
> 15-May AAB Hank Williams 33
> 20-May EEG Tom Arnold 150
>
> Employee Name Amount
> Kevin Vaughn 1149
>
> And a formula like so:
>
> =SUMPRODUCT(--(EmpName = B13), (Amt))
>
> Note: I didn't analyze what you were asking, just sort of skimmed it, but
> saw you were looking at dynamic ranges. Colhead is set up so that the number
> of Column Headings could be variable (since it appeared from one of your
> answers, Employee Name could jump around, I figured the number of columns
> could vary.) Also note that I did the counta on column a. Hopefully you
> have a column with no blanks interspersed among the data.
>
> HTH
> --
> Kevin Vaughn
>
>
> "Ronny Hamida" wrote:
>
> > I forgot to ask with this: OFFSET wouldn't cover defining/finding the last
> > row of the column, would it? It looks like it would be great to just define
> > the header row, which is still a step further! :)
> >
> > "Bob Phillips" wrote:
> >
> > > How about creating Excel names with a dynamic definition, such as
> > >
> > > =OFFSET($L$1,,,COUNTA($L:$L),1)
> > >
> > > for the employees, then use that name in the formulae.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "Ronny Hamida" <RonnyHamida@discussions.microsoft.com> wrote in message
> > > news:78B25E97-06B5-4340-A103-6BCDEAFE3D83@microsoft.com...
> > > > Hi there!
> > > >
> > > > I have formulas like this in a worksheet:
> > > >
> > > > =SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
> > > > =C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))
> > > >
> > > > When the columns on "Sheet2" change, this wouldn't be very "modular" or
> > > > friendly.
> > > >
> > > > I was wondering if I can incorporate something like INDEX or MATCH in
> > > these
> > > > formulas. Here's some detail:
> > > >
> > > > "Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
> > > > the header in row 1, but the column can be different every time. Can
> > > this
> > > > be something like "MATCH(xxxxx)"? (I'm not too familiar with these
> > > formulas.)
> > > >
> > > > The ":$L462" can also be different - This is the end of the column. It's
> > > > never more than 6000 rows, but it does change every time.
> > > >
> > > > (Just ignore the $B3 in the formulas - It's a reference point for the user
> > > > to change which data they want to count/sum/etc.)
> > > >
> > > > If I knew how to change those two formulas by adding something like MATCH
> > > or
> > > > INDEX(MATCH), I think I can manipulate the rest of my formulas
> > > accordingly,
> > > > but I'm not sure how to use them in a format like this.
> > > >
> > > > Any ideas?
> > > >
> > > > Thank you!
> > > >
> > > > Ronny Hamida
> > >
> > >
> > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
You're welcome. Thanks for the feedback.
--
Kevin Vaughn
"Ronny Hamida" wrote:
> This worked, thank you!
>
> "Kevin Vaughn" wrote:
>
> > Maybe something like this will help. First set up some named ranges like so:
> >
> > Amt =OFFSET(Sheet2!$A$2,,MATCH("Amount",ColHead,0) - 1,COUNTA(Sheet2!$A:$A)-1)
> > ColHead =OFFSET(Sheet2!$A$1,,,1,COUNTA(Sheet2!$1:$1))
> > EmpName =OFFSET(Sheet2!$A$2,,MATCH("Employee Name",ColHead,0) -
> > 1,COUNTA(Sheet2!$A:$A)-1)
> >
> >
> > Then with the spreadsheet looking something like so:
> >
> > Date Code Employee Name Amount
> > 1-May AAB Kevin Vaughn 50
> > 3-May BBA Tom Jefferson 100
> > 5-May CCD Hank Williams 20
> > 6-May DDA Kevin Vaughn 1000
> > 10-May AAB Tom Jefferson 750
> > 12-May CCD Kevin Vaughn 99
> > 13-May DDA Tom Jefferson 15
> > 15-May AAB Hank Williams 33
> > 20-May EEG Tom Arnold 150
> >
> > Employee Name Amount
> > Kevin Vaughn 1149
> >
> > And a formula like so:
> >
> > =SUMPRODUCT(--(EmpName = B13), (Amt))
> >
> > Note: I didn't analyze what you were asking, just sort of skimmed it, but
> > saw you were looking at dynamic ranges. Colhead is set up so that the number
> > of Column Headings could be variable (since it appeared from one of your
> > answers, Employee Name could jump around, I figured the number of columns
> > could vary.) Also note that I did the counta on column a. Hopefully you
> > have a column with no blanks interspersed among the data.
> >
> > HTH
> > --
> > Kevin Vaughn
> >
> >
> > "Ronny Hamida" wrote:
> >
> > > I forgot to ask with this: OFFSET wouldn't cover defining/finding the last
> > > row of the column, would it? It looks like it would be great to just define
> > > the header row, which is still a step further! :)
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > How about creating Excel names with a dynamic definition, such as
> > > >
> > > > =OFFSET($L$1,,,COUNTA($L:$L),1)
> > > >
> > > > for the employees, then use that name in the formulae.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "Ronny Hamida" <RonnyHamida@discussions.microsoft.com> wrote in message
> > > > news:78B25E97-06B5-4340-A103-6BCDEAFE3D83@microsoft.com...
> > > > > Hi there!
> > > > >
> > > > > I have formulas like this in a worksheet:
> > > > >
> > > > > =SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
> > > > > =C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))
> > > > >
> > > > > When the columns on "Sheet2" change, this wouldn't be very "modular" or
> > > > > friendly.
> > > > >
> > > > > I was wondering if I can incorporate something like INDEX or MATCH in
> > > > these
> > > > > formulas. Here's some detail:
> > > > >
> > > > > "Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
> > > > > the header in row 1, but the column can be different every time. Can
> > > > this
> > > > > be something like "MATCH(xxxxx)"? (I'm not too familiar with these
> > > > formulas.)
> > > > >
> > > > > The ":$L462" can also be different - This is the end of the column. It's
> > > > > never more than 6000 rows, but it does change every time.
> > > > >
> > > > > (Just ignore the $B3 in the formulas - It's a reference point for the user
> > > > > to change which data they want to count/sum/etc.)
> > > > >
> > > > > If I knew how to change those two formulas by adding something like MATCH
> > > > or
> > > > > INDEX(MATCH), I think I can manipulate the rest of my formulas
> > > > accordingly,
> > > > > but I'm not sure how to use them in a format like this.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thank you!
> > > > >
> > > > > Ronny Hamida
> > > >
> > > >
> > > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
Try this:
C13=SUMPRODUCT(--(EmpName = B13), Indirect($C$1))
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pinto G" <Pinto G@discussions.microsoft.com> wrote in message
news:66F9770A-AF0E-4E04-AA02-34D6A977F017@microsoft.com...
> Hi Kevin,
>
> Very useful. Follow-on question: in the formula
>
> C13=SUMPRODUCT(--(EmpName = B13), (Amt))
>
> is it possible to replace "(Amt)" with a reference to a cell which
> contains
> the range name I'd like to use? For example, something like:
>
> C13=SUMPRODUCT(--(EmpName = B13), ($C$1)), where C1 contains "AMT"
>
> In other columns, like D, D1 would hold a range name for something else,
> etc.
>
> Thanks.
>
>
> "Kevin Vaughn" wrote:
>
>> You're welcome. Thanks for the feedback.
>> --
>> Kevin Vaughn
>>
>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi Pinto
Try something like this:
C13=SUMPRODUCT(--(EmpName = B13), indirect($C$1))
Regards,
Pedro J.
> Hi Kevin,
>
> Very useful. Follow-on question: in the formula
>
> C13=SUMPRODUCT(--(EmpName = B13), (Amt))
>
> is it possible to replace "(Amt)" with a reference to a cell which contains
> the range name I'd like to use? For example, something like:
>
> C13=SUMPRODUCT(--(EmpName = B13), ($C$1)), where C1 contains "AMT"
>
> In other columns, like D, D1 would hold a range name for something else, etc.
>
> Thanks.
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi Pedro,
Thanks for the suggestion, but it didn't work (I tried it using the example
that Kevin used above).
Any other ideas?
"Infinitogool" wrote:
> Hi Pinto
> Try something like this:
> C13=SUMPRODUCT(--(EmpName = B13), indirect($C$1))
>
> Regards,
> Pedro J.
>
> > Hi Kevin,
> >
> > Very useful. Follow-on question: in the formula
> >
> > C13=SUMPRODUCT(--(EmpName = B13), (Amt))
> >
> > is it possible to replace "(Amt)" with a reference to a cell which contains
> > the range name I'd like to use? For example, something like:
> >
> > C13=SUMPRODUCT(--(EmpName = B13), ($C$1)), where C1 contains "AMT"
> >
> > In other columns, like D, D1 would hold a range name for something else, etc.
> >
> > Thanks.
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Question
Notify me of replies