Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2988506%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2988506%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20universal%20formula%20for%20the%20attached%20book.%20As%20seen%20from%20the%20excel%20book%2C%20range%20F13%20in%20YTD%20sheet%20pulls%20value%20of%20C8%20of%20Budget%20sheet.%20However%2C%20it%20should%20pull%20up%20the%20value%20of%20C14%20of%20Budget%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20the%20help%20of%20a%20universal%20formula%20that%20can%20be%20copied%20and%20pasted%20to%20every%20cell%20and%20reverts%20the%20exact%20value%20of%20corresponding%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2988506%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-2988640%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2988640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3BSince%20both%20Sales%20and%20Cogs%20share%20the%20same%20row%20header%20names%2C%20you%20can't%20just%20use%20INDEX%20and%20MATCH%20on%20the%20entire%20range%20of%20the%20P%26amp%3BL%20account.%20Therefore%20I%20created%20named%20ranges%20for%20%22budsales%22%2C%20%22budcogs%22%20and%20%22budexp%22.%20Similarly%2C%20I%20added%20some%20named%20ranges%20for%20the%20months%2C%20the%20categories%20for%20Sales%20and%20Cogs%20and%20Expenses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20I%20changed%20the%20formulae%20so%20that%20they%20are%20%22looking%22%20at%20the%20correct%20named%20ranges.%20See%20attached.%20I%20hope%20you'll%20find%20it%20useful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi guys,

 

I want the universal formula for the attached book. As seen from the excel book, range F13 in YTD sheet pulls value of C8 of Budget sheet. However, it should pull up the value of C14 of Budget sheet. 

 

I need the help of a universal formula that can be copied and pasted to every cell and reverts the exact value of corresponding details.

 

Thanks in advance.

1 Reply

@Dharmendra_Bharwad Since both Sales and Cogs share the same row header names, you can't just use INDEX and MATCH on the entire range of the P&L account. Therefore I created named ranges for "budsales", "budcogs" and "budexp". Similarly, I added some named ranges for the months, the categories for Sales and Cogs and Expenses.

 

Then, I changed the formulae so that they are "looking" at the correct named ranges. See attached. I hope you'll find it useful.