Dear all, I've scoured the web but can't find an easy way to get around this, hope you can help. For adding value to the table with 5 column (actual month and last 4 month) I want to get the value from a Pivot table by using a vlookup as my data changes every month (every month I get a new month). To avoid manual enetry I've used formula Today() (format changed to Date:mmm), result Jul, last month Today()-31 etc... No I want to use this cell as Lookup_Value in vlookup formula. As the lookup_value cell contains fomula and the format is different I get N/A# as result. does anybody have any idea how to resolve this issue? Thanks all.
Excel stored dates as numbers. Today = 42047 for example. By changing the format of the cell that contains that date to mmm you are really only changing the way you see the data (meaning that even though you only see "Feb" that doesnt change the underlying value of the data (42047 or 2/11/2015). So depending on how your dates are stored in your dataset (that you are building your pivot tables off of) the formula just cant find the data you are looking for. For instance if your months in your dataset are set up using the first of the month as the Month (2/1/2015) then you wont find it by using the Today() function except on the first of the month. Or if your dates in your dataset are truely text values like "Feb" then you wont find it unless you search using "Feb" instead of Today(). You have to search the data using the same value as the data you are looking for.
