244 reputation
210
bio website
location
age
visits member for 3 years, 3 months
seen Apr 15 at 6:43

Apr
1
comment How to add custom columns to Pivot Table (similar to Grand Total)?
@Jerry: I tried to add calculated field, but whatever I do, I get the same as the sum. Am I doing something wrong?
Apr
1
revised How to add custom columns to Pivot Table (similar to Grand Total)?
added 519 characters in body
Apr
1
asked How to add custom columns to Pivot Table (similar to Grand Total)?
Apr
1
accepted How does Excel handle the h:mm:ss format?
Apr
1
asked How does Excel handle the h:mm:ss format?
Feb
21
comment Excel busy CALCULATING for no reason
That would very much explain the problem. I rely intensively on INDIRECT to create formulas to manipulate data in other sheets, as those sheets get automatically removed and recreated by some VBA, and if I don't use INDIRECT, my formulas break with a #!REF error which I cannot undo. Do you know how to create formulas without volatile functions, while referencing sheets which can be removed/created, without getting #!REF errors?
Feb
21
comment Excel busy CALCULATING for no reason
But why would this happen when I edit a cell to do =2+2 on a brand new empty spreadsheet (just because the offending spreadsheet is opened in the background, makes no sense)?
Feb
21
asked Excel busy CALCULATING for no reason
Feb
17
awarded  Popular Question
Jan
17
awarded  Critic
Jan
13
comment Excel 2010 switching to formula view after every click
That was it, I must have somehow double-clicked the ribbon some how.
Jan
13
accepted Excel 2010 switching to formula view after every click
Jan
13
revised Excel 2010 switching to formula view after every click
edited tags
Jan
13
asked Excel 2010 switching to formula view after every click
Jan
13
accepted Data Source Credentials issue with Excel PowerPivot
Jan
7
answered Data Source Credentials issue with Excel PowerPivot
Jan
7
asked Data Source Credentials issue with Excel PowerPivot
Nov
20
comment Lookup range of words in range of sentences
I prefer this one over @bensheperd's solution as I don't have to use an array formula. To get rid of the static range I have =LOOKUP(2^15,SEARCH(INDIRECT("D$2:D$"&COUNTA($D:$D)+1),A2),E$2:E$6). What's interesting is that although I have E$2:E$6 static, it does work (i.e. it looks up for values on cells below E$6 :)
Nov
20
accepted Lookup range of words in range of sentences
Nov
20
comment Lookup range of words in range of sentences
To save me from having to update the formula every time I modify my range, I have replaced D$2:D$6 with INDIRECT and COUNTA which is working fine: =IFERROR(OFFSET($E$1,MATCH(1E+37,SEARCH(INDIRECT("D$2:D$"&COUNTA($D:$D)+1),A2),‌​1),0),"other")