SOLVED

Formula for administration purposses

%3CLINGO-SUB%20id%3D%22lingo-sub-2493330%22%20slang%3D%22en-US%22%3EFormula%20for%20administration%20purposses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493330%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20in%20an%20excel%20the%20income%20of%20each%20month%20from%20an%20organization%2C%20so%20I%20want%20to%20make%20a%20formula%20in%20which%20I%20only%20write%20the%20month%20as%20a%20name%20in%20a%20cell%20bar%2C%20and%20automatically%20the%20formula%20select%20from%20the%20first%20month(In%20this%20case%20July)%20until%20the%20month%20i%20write%20in%20the%20cell%20bar(The%20last%20one%20is%20June).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20I%20want%20to%20write%20in%20the%20cell%20bar%20February%20so%20the%20formula%20will%20give%20me%20the%20sum%20of%20the%20income%20from%20July(First%20one)%20to%20February%20(The%20one%20i%20write%20in%20the%20formula).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20can%20understand%20and%20help%20me.%20I%20wanted%20to%20share%20a%20picture%20of%20the%20data%20but%20it%20is%20classified.%3C%2FP%3E%3CP%3EThankss!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2493330%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2494530%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20administration%20purposses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2494530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1090081%22%20target%3D%22_blank%22%3E%40Sergio_Garza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20if%20you%20share%20small%20sample%20file%20to%20illustrate%20how%20your%20data%20is%20structured%20replacing%20sensitive%20information%20with%20dummy%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!!

4 Replies

@Sergio_Garza 

Better if you share small sample file to illustrate how your data is structured replacing sensitive information with dummy one.

best response confirmed by Sergio_Garza (New Contributor)
Solution

@Sergio_Garza 

@Sergei Baklan 

Submitted for your appraisal (I know you don't always approve of the way I work!)

 

image.png

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)

 

Thanks, I am a little confused about what to do with that formula but i will make it.

@Sergio_Garza 

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.