How to create a mechanism to get the YTD?

[Doria/Warris]

Hi,

I have an urgent issue to solve into my Excel table.
In order to make it easy, please look at my sample below.

ROW COLUMN
Jan 5
Feb 7
March 10
Apr 10
May 10
Jun 8
Jul 7
Aug 5
Sept 13
Oct 10
Nov 10
Dec 5

Tot (Full Year) 100
YTD (YearToDate) 100

This table show sdifferent Targets to be reached, in this example there's
ony one column, but in reality the columns are more.
What I'd like to achieve is the possibility to enter in a cell outside the
table the month and get the corresponding YTD.
For instance, enter 5 (May) and get 42, enter 8 (Aug) and get 62 etc.
As I said the columns are many more, provided that you give me a solution,
how shall I do to link all the other columns in order to get the YTD for all
of them at the same time?

Thank you very much
Alex

 

[Don Guillett]

Where G1:g whatever has the numbers and f1:f whatever has the Months.
Mar in I1 will get 22. This is NOT an array formula.

=SUM(INDIRECT("g1:g"&MATCH(I1,F:F,0)))

 

[Bob Phillips]

Assuming the amounts are in column B, the month in A25, then
=SUM(INDIRECT("B1:B"&A25))

To extend to other columns then use

=SUM(INDIRECT("B1:H"&A25))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Doria/Warris]

Bob,

Thanks for your help.

I wasn't completely clear with my previous message, therefore I'll give you the missing information.

COLUMN A COLUMN B
Jan 5
X
X
Feb 7
X
X
March 10
X
X
Apr 10
X
X
May 10
X
X

ETC.

Jun 8
Jul 7
Aug 5
Sept 13
Oct 10
Nov 10
Dec 5

Tot (Full Year) 100
YTD (YearToDate) 100

1) The X in Col. B are other values that must not be part of the calculation, therefore I cannot take the whole header to SUM. In fact, the YTD 100 at the end is the sum of the RED numbers only, the X represents different numbers that are not part of the calculation.
2) You did not mention how do I create the mechanism to enter 5 (May) in a cell, (let's assume I chose cell C1 to be the "Button" to press by entering the month) and get 42, and every time I enter a no. from 1 to 12 in cell C1 I get the right YTD.
If by chance you have a nicer way, please let me know.

I hope it's now a bit clearer.

Thank you
Alex

 

[Dana DeLouis]

If your Column of data begins in C1, and you enter 5 in B1, this formula
returns 42. Entering 8 returns 62

=SUM(OFFSET(C1,0,0,B1,1))

but in reality the columns are more

Depending on what you are doing, the 3rd item in Offset can point to other
columns.

HTH
Dana DeLouis

 

[Doria/Warris]

Hi,

Can anybody help?

Alex
Bob,

Thanks for your help.

I wasn't completely clear with my previous message, therefore I'll give you the missing information.

COLUMN A COLUMN B
Jan 5
X
X
Feb 7
X
X
March 10
X
X
Apr 10
X
X
May 10
X
X

ETC.

Jun 8
Jul 7
Aug 5
Sept 13
Oct 10
Nov 10
Dec 5

Tot (Full Year) 100
YTD (YearToDate) 100

1) The X in Col. B are other values that must not be part of the calculation, therefore I cannot take the whole header to SUM. In fact, the YTD 100 at the end is the sum of the RED numbers only, the X represents different numbers that are not part of the calculation.
2) You did not mention how do I create the mechanism to enter 5 (May) in a cell, (let's assume I chose cell C1 to be the "Button" to press by entering the month) and get 42, and every time I enter a no. from 1 to 12 in cell C1 I get the right YTD.
If by chance you have a nicer way, please let me know.

I hope it's now a bit clearer.

Thank you
Alex

 

[Don Guillett]

I, for one, still do NOT understand your problem. It is NOT clear to me.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi,

Can anybody help?

Alex
Bob,

Thanks for your help.

I wasn't completely clear with my previous message, therefore I'll give you the missing information.

COLUMN A COLUMN B
Jan 5
X
X
Feb 7
X
X
March 10
X
X
Apr 10
X
X
May 10
X
X

ETC.

Jun 8
Jul 7
Aug 5
Sept 13
Oct 10
Nov 10
Dec 5

Tot (Full Year) 100
YTD (YearToDate) 100

1) The X in Col. B are other values that must not be part of the calculation, therefore I cannot take the whole header to SUM. In fact, the YTD 100 at the end is the sum of the RED numbers only, the X represents different numbers that are not part of the calculation.
2) You did not mention how do I create the mechanism to enter 5 (May) in a cell, (let's assume I chose cell C1 to be the "Button" to press by entering the month) and get 42, and every time I enter a no. from 1 to 12 in cell C1 I get the right YTD.
If by chance you have a nicer way, please let me know.

I hope it's now a bit clearer.

Thank you
Alex

 

[Doria/Warris]

Hi,

Hope somebody else will do, I really don't know how to better explain.

Sorry
Alex
I, for one, still do NOT understand your problem. It is NOT clear to me.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi,

Can anybody help?

Alex
Bob,

Thanks for your help.

I wasn't completely clear with my previous message, therefore I'll give you the missing information.

COLUMN A COLUMN B
Jan 5
X
X
Feb 7
X
X
March 10
X
X
Apr 10
X
X
May 10
X
X

ETC.

Jun 8
Jul 7
Aug 5
Sept 13
Oct 10
Nov 10
Dec 5

Tot (Full Year) 100
YTD (YearToDate) 100

1) The X in Col. B are other values that must not be part of the calculation, therefore I cannot take the whole header to SUM. In fact, the YTD 100 at the end is the sum of the RED numbers only, the X represents different numbers that are not part of the calculation.
2) You did not mention how do I create the mechanism to enter 5 (May) in a cell, (let's assume I chose cell C1 to be the "Button" to press by entering the month) and get 42, and every time I enter a no. from 1 to 12 in cell C1 I get the right YTD.
If by chance you have a nicer way, please let me know.

I hope it's now a bit clearer.

Thank you
Alex