SOLVED
Home

Using VLOOKUP across multiple tabs with INDIRECT causing HAVOC

%3CLINGO-SUB%20id%3D%22lingo-sub-1074936%22%20slang%3D%22en-US%22%3EUsing%20VLOOKUP%20across%20multiple%20tabs%20with%20INDIRECT%20causing%20HAVOC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1074936%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20multiple%20tabs%20and%20i%E2%80%99m%20using%20the%20VLOOKUP%20paired%20with%20INDIRECT%2C%20its%20%E2%80%9Cref%20text%E2%80%9D%20is%20a%20drop%20down%20list%20titled%20exactly%20as%20the%20tabs.%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20unable%20to%20get%20it%20to%20adjust%20the%20Vlookup%20results%20when%20i%20change%20the%20selection%20in%20the%20drop%20down%20list.%20Only%20the%20initial%20selection%20i%20write%20the%20formula%20with%20will%20work...%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162624i5593D08AA67F5944%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E...%20and%20than%20the%20rest%20of%20the%20drop%20down%20selections%20will%20return%20%23N%2FA.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162625iFD6A7675BFB5BF94%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%E2%80%99ve%20verified%20it%20is%20not%20the%20DATA%20itself%20because%20i%20can%20make%20multiple%20drop%20down%20lists%20and%20do%20the%20same%20thing%20with%20different%20selections%20as%20the%20initial%2C%20but%20they%20all%20respond%20the%20same%20way%20when%20i%20try%20to%20change%20the%20selection%20in%20their%20drop%20down.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EMy%20lookup%20value%20is%20a%20date%2C%20the%20crazy%20thing%20is%20my%20list%20of%20dates%201%2F1%2F2019%20through%201%2F5%2F2019%20in%20my%20chart%20where%20results%20are%20being%20displayed%20based%20on%20the%20initial%20selection%20on%20the%20drop%20down%20menu%20will%20correct%20the%20%23N%2FA%20if%20i%20select%20date%20cells%26nbsp%3B%20%26gt%3B%20Format%20cells%20%26gt%3B%20Select%20%E2%80%9CText%E2%80%9D%20(as%20it%20was%20defaulted%20to%20date%20but%20still%20working%20with%20my%20initial%20selection)%2C%20when%20i%20do%20this%20the%20date%20changes%20to%20some%20numbers%2C%20i%20than%20re-type%20in%20the%20date%20looking%20like%20%E2%80%9C1%2F1%2F2019%E2%80%9D%20etc%20and%20it%20resolves%20the%20issue%20and%20stays%20in%20%E2%80%9Ctext%E2%80%9D%20format%20and%20works%20for%20all%20the%20other%20drop%20down%20selections%20moving%20forward.%20An%20issue%20with%20this%20is%20i%20need%20365%20days%20worth%20of%20information%20for%20manually%20doing%20this%20for%20365%20days%20is%20not%20feasible.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162628i0CA17B268AE3BF84%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHowever%2C%20if%20i%20switch%20back%20to%20the%20initial%20selection%20in%20the%20drop%20down%20menu%20now%20any%20date%20cells%20i%20rewrote%2Fchanged%20their%20format%20to%20text%20will%20not%20work%20and%20only%20date%20cells%20i%20did%20not%20rewrite%20will%20work.%20Also%2C%20the%20issue%20is%20not%20with%20the%20tabs%20it%20is%20looking%20through%20because%20all%20of%20those%20tabs%20have%20their%20dates%20formatted%20as%20dates.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162629i44B83E94EF4EF366%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWHAT%20IN%20SAM%20HILL%20TARNATION%20IS%20GOING%20ON%3F!%3F!%3F!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1074936%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1074955%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20across%20multiple%20tabs%20with%20INDIRECT%20causing%20HAVOC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1074955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20possible%20to%20submit%20the%20file%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1074962%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20across%20multiple%20tabs%20with%20INDIRECT%20causing%20HAVOC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1074962%22%20slang%3D%22en-US%22%3EIt%20is%20attached%20now%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1076812%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20VLOOKUP%20across%20multiple%20tabs%20with%20INDIRECT%20causing%20HAVOC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494154%22%20target%3D%22_blank%22%3E%40shade206%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20have%20mix%20of%20dates%20and%20texts%20in%20your%20data.%20In%20main%20sheet%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20398px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162727i218848E18B2E8087%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Edate%20for%20Thu%20and%20Fri%20are%20actually%20texts.%20In%20other%20sheets%20they%20are%20also%20different%2C%20e.g.%20for%20Jennifer%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20127px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162728i819C7286AE4E0B19%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethese%20are%20dates%2C%20and%20for%20Kelly%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20103px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162729i457FA98FC4285C50%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethere%20are%20texts.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20format%20all%20cells%20as%20dates%20and%20re-enter%20texts%20as%20dates%20(they%20are%20actually%20number%20in%20Excel).%20Simple%20test%20-%20texts%20by%20default%20are%20aligned%20to%20the%20left%2C%20and%20numbers%20to%20the%20right.%20Or%20you%20may%20in%20any%20empty%20cell%20to%20test%20the%20formula%20as%20%3DA27%2B1.%20If%20returns%20%23VALUE%20error%20you%20have%20the%20text%20in%20the%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E
shade206
Contributor

Hello,


I have multiple tabs and i’m using the VLOOKUP paired with INDIRECT, its “ref text” is a drop down list titled exactly as the tabs.

I’m unable to get it to adjust the Vlookup results when i change the selection in the drop down list. Only the initial selection i write the formula with will work...

clipboard_image_0.png



... and than the rest of the drop down selections will return #N/A.

clipboard_image_1.png

I’ve verified it is not the DATA itself because i can make multiple drop down lists and do the same thing with different selections as the initial, but they all respond the same way when i try to change the selection in their drop down.

My lookup value is a date, the crazy thing is my list of dates 1/1/2019 through 1/5/2019 in my chart where results are being displayed based on the initial selection on the drop down menu will correct the #N/A if i select date cells  > Format cells > Select “Text” (as it was defaulted to date but still working with my initial selection), when i do this the date changes to some numbers, i than re-type in the date looking like “1/1/2019” etc and it resolves the issue and stays in “text” format and works for all the other drop down selections moving forward. An issue with this is i need 365 days worth of information for manually doing this for 365 days is not feasible.

clipboard_image_2.png


However, if i switch back to the initial selection in the drop down menu now any date cells i rewrote/changed their format to text will not work and only date cells i did not rewrite will work. Also, the issue is not with the tabs it is looking through because all of those tabs have their dates formatted as dates.

clipboard_image_3.png

 

WHAT IN SAM HILL TARNATION IS GOING ON?!?!?!

3 Replies
Highlighted

@shade206 

Is it possible to submit the file?

Highlighted
Highlighted
Solution

@shade206 

You have mix of dates and texts in your data. In main sheet

image.png

date for Thu and Fri are actually texts. In other sheets they are also different, e.g. for Jennifer

image.png

these are dates, and for Kelly

image.png

there are texts.

 

You need to format all cells as dates and re-enter texts as dates (they are actually number in Excel). Simple test - texts by default are aligned to the left, and numbers to the right. Or you may in any empty cell to test the formula as =A27+1. If returns #VALUE error you have the text in the cell.

Related Conversations
Remove Organization tab
fmims in Microsoft Teams on
4 Replies
Collections UI
rosenbloomsnka in Discussions on
4 Replies
Keep tabs on close
Sc0tTy in Discussions on
1 Replies
(Custom) Tabs are no longer displayed to guests
koenvissers in Microsoft Teams on
14 Replies