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...
... and than the rest of the drop down selections will return #N/A.
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.
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.
WHAT IN SAM HILL TARNATION IS GOING ON?!?!?!
You have mix of dates and texts in your data. In main sheet
date for Thu and Fri are actually texts. In other sheets they are also different, e.g. for Jennifer
these are dates, and for Kelly
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.
Accepted Solutions
You have mix of dates and texts in your data. In main sheet
date for Thu and Fri are actually texts. In other sheets they are also different, e.g. for Jennifer
these are dates, and for Kelly
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.