Pivot tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1495269%22%20slang%3D%22en-US%22%3EPivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495269%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20setting%20up%20a%20budget%20which%20I%20obtained%20from%20Nabid%20Mourad%20and%20when%20trying%20to%20use%20vlookuo%20to%20view%20month%20name%20for%20each%20month%2C%20I%20get%20an%20error%20'%23REF%23'.%26nbsp%3B%20As%20per%20Nabid%20instruction%2C%20I%20have%20created%20a%20'mylist'%20table%20array%20to%20indicate%20the%20months%20but%20cannot%20get%20the%20formula%20to%20work%20-%20help!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1495269%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495283%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712890%22%20target%3D%22_blank%22%3E%40jeffjm%3C%2FA%3E%26nbsp%3BWould%20be%20helpful%20if%20you%20could%20show%20the%20file%20you%20are%20working%20on%20and%20the%20formula%20that%20is%20returning%20the%20%23REF!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712890%22%20target%3D%22_blank%22%3E%40jeffjm%3C%2FA%3E%26nbsp%3BI%20believe%20I%20found%20the%20video%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D5ZCBUgkVqqQ%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D5ZCBUgkVqqQ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20minutes%20into%20the%20video%2C%20he%20describes%20how%20to%20create%20a%20named%20range.%26nbsp%3BTen%20minutes%20into%20the%20video%2C%20he%20is%20describing%20how%20to%20create%20the%20VLOOKUP%20function%2C%20using%20the%20named%20range%20as%20the%20'table_array'.%3C%2FP%3E%3CP%3EA%20%23REF!%20error%20occurs%20when%20you%20try%20to%20call%20for%20a%20column%20index%20that%20is%20beyond%20the%20width%20of%20the%20table_array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20that%20the%20named%20range%20%22MyList%22%20is%20indeed%202%20columns%20wide%20and%20that%20the%20VLOOKUP%20function%20calls%20for%20the%202nd%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495411%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495411%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712890%22%20target%3D%22_blank%22%3E%40jeffjm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20mean%20Nabil%20Mourad%20why%20don't%20ask%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3Bhere.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495790%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712890%22%20target%3D%22_blank%22%3E%40jeffjm%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712890%22%20target%3D%22_blank%22%3E%40jeffjm%3C%2FA%3E%26nbsp%3B%20I%20am%20not%20sure%20what%20problem%20you%20have%20unless%20I%20see%20the%20file.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20this%20is%20a%20beginners'%20tutorial.%20That%20explains%20why%20the%20pace%20is%20slow%20for%20an%20advanced%20user%20...%20When%20you%20create%20%3CSTRONG%3EPublic%20Tutorials%3C%2FSTRONG%3E%20you%20realize%20that%20you%20cannot%20make%20everyone%20happy%20(although%20I%20wish%20to).%20But%20anyway%2C%20I%20respect%20your%20opinion.%3CBR%20%2F%3EFor%20the%20Named%20range%20I%20created%2C%20that's%20a%20%3CSTRONG%3EStatic%3C%2FSTRONG%3E%20named%20range%2C%20so%20it's%20normal%20to%20get%20a%20%23REF%20when%20providing%20a%20Col_Index_number%20outside%20the%20range).%20There%20are%20other%20more%20advanced%20methods%20for%20creating%20a%20%3CSTRONG%3EFloating%3C%2FSTRONG%3E%20named%20range%2C%20and%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B(the%20Excel%20guru)%20can%20confirm%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495835%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3BI%20had%20no%20problem%20with%20the%20pace.%20Just%20mentioned%20the%20time%20stamps%20so%20that%20the%20creation%20of%20%22MyList%22%20and%20the%20actual%20use%20of%20it%20could%20easily%20be%20traced.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1497267%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1497267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Please%20put%20me%20in%20touch%20with%20Nabil%20Mourad%20so%20I%20can%20chat%20with%20him%20about%20the%20pivot%20table%20and%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1497575%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1497575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712890%22%20target%3D%22_blank%22%3E%40jeffjm%3C%2FA%3E%26nbsp%3BYou%20can%20respond%20to%20the%20post%20he%20made%20earlier%20or%20send%20him%20a%20private%20message.%20Hover%20the%20mouse%20pointer%20over%20his%20name%20tag%20and%20click%20%22Message%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-06-30%20at%2006.38.07.png%22%20style%3D%22width%3A%20241px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202085i1BA48CFE9D982454%2Fimage-dimensions%2F241x192%3Fv%3D1.0%22%20width%3D%22241%22%20height%3D%22192%22%20title%3D%22Screenshot%202020-06-30%20at%2006.38.07.png%22%20alt%3D%22Screenshot%202020-06-30%20at%2006.38.07.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am setting up a budget which I obtained from Nabid Mourad and when trying to use vlookuo to view month name for each month, I get an error '#REF#'.  As per Nabid instruction, I have created a 'mylist' table array to indicate the months but cannot get the formula to work - help!!

6 Replies
Highlighted

@jeffjm Would be helpful if you could show the file you are working on and the formula that is returning the #REF! error.

 

Edit: @jeffjm I believe I found the video here:

https://www.youtube.com/watch?v=5ZCBUgkVqqQ 

Two minutes into the video, he describes how to create a named range. Ten minutes into the video, he is describing how to create the VLOOKUP function, using the named range as the 'table_array'.

A #REF! error occurs when you try to call for a column index that is beyond the width of the table_array.

 

Check that the named range "MyList" is indeed 2 columns wide and that the VLOOKUP function calls for the 2nd column.

 

 

Highlighted

@jeffjm 

If you mean Nabil Mourad why don't ask @nabilmourad here. 

Highlighted

@jeffjm 

Hi,

@jeffjm  I am not sure what problem you have unless I see the file.

@Riny_van_Eekelen  this is a beginners' tutorial. That explains why the pace is slow for an advanced user ... When you create Public Tutorials you realize that you cannot make everyone happy (although I wish to). But anyway, I respect your opinion.
For the Named range I created, that's a Static named range, so it's normal to get a #REF when providing a Col_Index_number outside the range). There are other more advanced methods for creating a Floating named range, and @Sergei Baklan (the Excel guru) can confirm that.

Highlighted

@nabilmourad I had no problem with the pace. Just mentioned the time stamps so that the creation of "MyList" and the actual use of it could easily be traced.

Highlighted

@Riny_van_Eekelen  Please put me in touch with Nabil Mourad so I can chat with him about the pivot table and formula.

Highlighted

@jeffjm You can respond to the post he made earlier or send him a private message. Hover the mouse pointer over his name tag and click "Message"

Screenshot 2020-06-30 at 06.38.07.png