Can you use AND / OR in an INDEX MATCH
Hi I have am array formula that looks like this: =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works. I want to add in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria I tried using the + to add T criteria but gave me a 0 =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1) Thanks for the help!Extra and "double" double quotes when using carriage return in formulas
Hi, all. I am attempting to copy Excel cell contents in a text file (for further use later). However, when copying and pasting cells with formulas that contain both quotes ("") and carriage returns (either CHAR(13) or "Alt+Enter"), the result contains "extra" quotes at odd places. For example, if formula is =[@Type] & [@[Callout Pipe 1]] (with no carriage returns), the result is (after pasting into Notepad): SDMH12" CPP N IE= 320.8 Just as desired. However, we need a carriage return after "SDMH" (from column [@Type]), so if we simply add a carriage return (again, either CHAR(13) or "Alt+Enter") with this formula =[@Type] & CHAR(13) & [@[Callout Pipe 1]], pasting it in Notepad results in: "SDMH 12"" CPP N IE= 320.8" As can be seen, the return carriage worked, but my text now has three extra double quotes that weren't meant to be there. Could anyone help me solve this problem? Thanks in advance, Edgar
pre-1900 dates
I can find no help on how to tell Excel to properly format and calculate dates before 1900. I want to calculate age at death and have birth and death dates in columns. But Excel does not recognize March 30, 1894. It calls is YYYY = 3794. And subtracting death date from birth date returns "#VALUE!" How do I use the DATE(YYYY,MM,DD) function or format March 30, 1894 so Excel will understand and I can have it calculate the age at death correctly? TIA. Also I've seen #1 shown in Excel Help in different posts as BOTH Dec 31, 1899 AND as Jan 1, 1900. Which is it?
Pivot table won't refresh.
I'm getting an error when I try to refresh PivotTable data - and it's not updating a field I've made changes to. The error message is: "The pivot table field name is not valid. To create a valid PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name of the field." Thing is, I am using tables with labeled columns, I've tried renaming the pivot table, the fields, everything I can think of. Nothing works. So I end up re-creating the PivotTable over and over, but it still isn't refreshing the data. Help!
