Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
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 "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
Hi,
Can anybody help? Alex "Doria/Warris" wrote in message ... 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 "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to create a mechanism to get the YTD?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel password mechanism failure | Excel Discussion (Misc queries) | |||
Create a macro to create excel line graph with coloured pointers anddata lables | Charts and Charting in Excel | |||
any function or mechanism to get in a cell the name of the sheet? | Excel Worksheet Functions | |||
can you place an alarm or tickler mechanism within excel based on. | Excel Worksheet Functions | |||
can you place an alarm or tickler mechanism within excel based on. | Excel Worksheet Functions |