Prev: Additonal info into newly created Subtotal row
Next: Macro to get rid of #DIV/0!
From: cbotos on 31 Mar 2010 13:50
Hi,

I have a worksheet full of data (Actual DT)with cost centers in column A,
subaccounts in column B, and data in columns D through O (each column is a
different month). In the main tab of my report, I have a dropdown box for
users to select the month they would like to see data for. In yet another
tab, I am trying to create a formula that is a 'sumifs' into the data sheet
that will sum the given month's column if both the cost center and subaccount
match those given in cells on that sheet.

So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match to in
A10 and the Subaccount in A9)

This formula works fine but what I would like to do is find some way to
remove the 'Actual DT'!K:K reference and have that be either a vlookup or
something into another sheet where I can lookup the month currently selected
in the main dropdown window and then have the corresponding sum range I want
to put into the sumifs statement in the next column over. I would even settle
for being able to reference one cell that I could format to hold the data
range for the given month. I just can't figure it out! I have had sucess
referencing the column number in a vlookup as a reference to another cell,
but I haven't figure out how to do this for a range of cells.

Is this possible? Please let me know if I can clarify anything.

Thank you in advance!!!

From: Pete_UK on 31 Mar 2010 13:59
What cell in the drop-down on your main sheet is used to select the
month? Is this the name of the month (Jan or January), or is it a
number to represent the month?

Pete

On Mar 31, 6:50 pm, cbotos <cbo...(a)discussions.microsoft.com> wrote:
> Hi,
>
> I have a worksheet full of data (Actual DT)with cost centers in column A,
> subaccounts in column B, and data in columns D through O (each column is a
> different month). In the main tab of my report, I have a dropdown box for
> users to select the month they would like to see data for. In yet another
> tab, I am trying to create a formula that is a 'sumifs' into the data sheet
> that will sum the given month's column if both the cost center and subaccount
> match those given in cells on that sheet.
>
> So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
> DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match to in
> A10 and the Subaccount in A9)
>
> This formula works fine but what I would like to do is find some way to
> remove the 'Actual DT'!K:K reference and have that be either a vlookup or
> something into another sheet where I can lookup the month currently selected
> in the main dropdown window and then have the corresponding sum range I want
> to put into the sumifs statement in the next column over. I would even settle
> for being able to reference one cell that I could format to hold the data
> range for the given month. I just can't figure it out! I have had sucess
> referencing the column number in a vlookup as a reference to another cell,
> but I haven't figure out how to do this for a range of cells.
>
> Is this possible? Please let me know if I can clarify anything.
>
> Thank you in advance!!!

From: cbotos on 31 Mar 2010 15:11
The main sheet is a tab called "SCORECARD" and the cell with the dropdown is
B7. The dropdown has users choose a month by full name (ex. January,
February, March, April, etc.)

i experimented with using a working sheet to pull the month selected from
Scorecard B7 and have the sumifs formula use an indirect into there but I
didn't have any luck.

Any help would be appreciated!

"Pete_UK" wrote:

> What cell in the drop-down on your main sheet is used to select the
> month? Is this the name of the month (Jan or January), or is it a
> number to represent the month?
>
> Pete
>
> On Mar 31, 6:50 pm, cbotos <cbo...(a)discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have a worksheet full of data (Actual DT)with cost centers in column A,
> > subaccounts in column B, and data in columns D through O (each column is a
> > different month). In the main tab of my report, I have a dropdown box for
> > users to select the month they would like to see data for. In yet another
> > tab, I am trying to create a formula that is a 'sumifs' into the data sheet
> > that will sum the given month's column if both the cost center and subaccount
> > match those given in cells on that sheet.
> >
> > So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
> > DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match to in
> > A10 and the Subaccount in A9)
> >
> > This formula works fine but what I would like to do is find some way to
> > remove the 'Actual DT'!K:K reference and have that be either a vlookup or
> > something into another sheet where I can lookup the month currently selected
> > in the main dropdown window and then have the corresponding sum range I want
> > to put into the sumifs statement in the next column over. I would even settle
> > for being able to reference one cell that I could format to hold the data
> > range for the given month. I just can't figure it out! I have had sucess
> > referencing the column number in a vlookup as a reference to another cell,
> > but I haven't figure out how to do this for a range of cells.
> >
> > Is this possible? Please let me know if I can clarify anything.
> >
> > Thank you in advance!!!
>
> .
>
From: T. Valko on 31 Mar 2010 16:44
Assuming in sheet Actual DT D1:O1 are the monthly column headers in the form
January, February, March, etc.

=SUMIFS(INDEX('Actual DT'!D:O,,MATCH(B7,'Actual DT'!D1:O1,0)),'Actual
DT'!A:A,A10,'Actual DT'!B:B,A9)

--
Biff
Microsoft Excel MVP


"cbotos" <cbotos(a)discussions.microsoft.com> wrote in message
news:B2B3A3E9-506E-4F31-A3A7-59DAAA6332E9(a)microsoft.com...
> The main sheet is a tab called "SCORECARD" and the cell with the dropdown
> is
> B7. The dropdown has users choose a month by full name (ex. January,
> February, March, April, etc.)
>
> i experimented with using a working sheet to pull the month selected from
> Scorecard B7 and have the sumifs formula use an indirect into there but I
> didn't have any luck.
>
> Any help would be appreciated!
>
> "Pete_UK" wrote:
>
>> What cell in the drop-down on your main sheet is used to select the
>> month? Is this the name of the month (Jan or January), or is it a
>> number to represent the month?
>>
>> Pete
>>
>> On Mar 31, 6:50 pm, cbotos <cbo...(a)discussions.microsoft.com> wrote:
>> > Hi,
>> >
>> > I have a worksheet full of data (Actual DT)with cost centers in column
>> > A,
>> > subaccounts in column B, and data in columns D through O (each column
>> > is a
>> > different month). In the main tab of my report, I have a dropdown box
>> > for
>> > users to select the month they would like to see data for. In yet
>> > another
>> > tab, I am trying to create a formula that is a 'sumifs' into the data
>> > sheet
>> > that will sum the given month's column if both the cost center and
>> > subaccount
>> > match those given in cells on that sheet.
>> >
>> > So far, I have gotten to the following: =SUMIFS('Actual DT'!K:K,'Actual
>> > DT'!A:A,A10,'Actual DT'!B:B,$A$9) (I have the CC i would like to match
>> > to in
>> > A10 and the Subaccount in A9)
>> >
>> > This formula works fine but what I would like to do is find some way to
>> > remove the 'Actual DT'!K:K reference and have that be either a vlookup
>> > or
>> > something into another sheet where I can lookup the month currently
>> > selected
>> > in the main dropdown window and then have the corresponding sum range I
>> > want
>> > to put into the sumifs statement in the next column over. I would even
>> > settle
>> > for being able to reference one cell that I could format to hold the
>> > data
>> > range for the given month. I just can't figure it out! I have had
>> > sucess
>> > referencing the column number in a vlookup as a reference to another
>> > cell,
>> > but I haven't figure out how to do this for a range of cells.
>> >
>> > Is this possible? Please let me know if I can clarify anything.
>> >
>> > Thank you in advance!!!
>>
>> .
>>


From: Ziggy on 31 Mar 2010 17:16
I like to create two colums off to the right of the financial data
with a 'CHOOSE' formula, say columns Q & R

One, with a simple 'CHOOSE' formula to select the current month or the
month you want to select, and
Two, also with a cummulative CHOOSE formula a year-to-date column
based on the selected month.

=CHOOSE(ref_cell,D5,sum($D5:E5), sum($D5:F5)... Sum($D5:O5))

It's then simple to reference only those two columns for your data.

Your SUMIFS formula is tied only to these columns

 |  Next  |  Last
Pages: 1 2
Prev: Additonal info into newly created Subtotal row
Next: Macro to get rid of #DIV/0!