Jun 28 2021 09:32 AM - edited Jun 28 2021 09:33 AM
Hi, I have in an excel the income of each month from an organization, so I want to make a formula in which I only write the month as a name in a cell bar, and automatically the formula select from the first month(In this case July) until the month i write in the cell bar(The last one is June).
Example: I want to write in the cell bar February so the formula will give me the sum of the income from July(First one) to February (The one i write in the formula).
I hope someone can understand and help me. I wanted to share a picture of the data but it is classified.
Thankss!!
Jun 28 2021 02:16 PM
Better if you share small sample file to illustrate how your data is structured replacing sensitive information with dummy one.
Jun 29 2021 06:12 AM
SolutionSubmitted for your appraisal (I know you don't always approve of the way I work!)
Since the formula returns a reference to a section of the table, I am able to intersect the extract with columns of the table so that the total income for the year to date could be evaluated using range intersection, giving
= SUM(income yearToDate)
Jun 29 2021 06:42 AM
Jun 29 2021 08:34 AM
As it stands, the formula requires Excel 365 and it is hidden away within the defined name 'yearToDate'. The formula is about as far from standard spreadsheet practice as you can get without turning to PowerQuery or VBA.
The key to using the formula is to recognise that the defined name refers to a (varying) region of the data, returning it as a range reference. If you do not have LET, it is possible to pick out the formula scoped names and given them workbook or sheet scope in Name Manager. The main problem then would be using LOOKUP rather than XLOOKUP.
Jun 29 2021 06:12 AM
SolutionSubmitted for your appraisal (I know you don't always approve of the way I work!)
Since the formula returns a reference to a section of the table, I am able to intersect the extract with columns of the table so that the total income for the year to date could be evaluated using range intersection, giving
= SUM(income yearToDate)