I'm working on a massive Excel spreadsheet- perhaps as large as 25 million cells.
About 1/3 of the cells contain the error: #VALUE!
In the hopes of speeding things up, I would like to convert these to blank cells. I.e. remove the error message and underlying formula.
Is it possible to make a simple formula that I can just drag across the affected cells that will identify each "#VALUE!" cell and then replace it with blank cell?
I posted a thread some while back which I didn't manage to resolve.
Imagine the following example:
Column A contains Names of tasks
Column B contains Progress regularly entered by the user
Column C contains Remaining Task Duration returned by a formula based on progress entered in Column B
I want the user to be able to override the value returned by the formula in Column C, but if they subsequently delete the value they entered or revise the progress in Column B, I want the original formula to return into Column C and recalculate its value.
In other words, Column C should contain a "dormant" underlying formula that is re-activated whenever a user deletes the value in the cell or updates the progess in Column C.
I'm looking for an answer about how I can show the numbers underlying a
nested IF function. The formula works fine, but I need to be able to show
how I arrived at the numbers that I did. Here is an example of one of the formulas:
=IF(B9=1,(B4*Data!G5*Data!F5),IF(Calculations!B10=2,(Calculations!B5*Data!G5*Data!F5),IF(Calculations!B11=3,(Calculations!B6*Data!G5*Data!F5),0)))
Instead of the reference to the cell, I would like the formula that the cell represents. I know that I could go through individually and pull each piece apart with control F9, but since there are numerous cells and because the data changes every time I create a new budget, it would help if I could have some way to display it all with one action.
i want to be able to display the underlying data in a pivot table by clicking on the cell within the pivot table
ie, normally if you double click a cell within a pivot table, it will generate a new tab, and the data will be displayed in the newly created tab,
i want to be able to click a cell within my pivot table, and have the underlying data display beneath the pivot table..
I have a file with lots of pivot tables which works fine. But if I save and reopen the file then I get the following message when I try to change a filter on any of the pivot tables:
"The pivottable report was saved without the underlying data"
(ideally without refreshing all pivot tables using a workbook open event as this will cause a 10-20 second time-lag)
p.s. the file used to work fine before I added an extra pivot table (there are roughly 10 pivot tables in the file)
Within my spreadsheet all the formulae are sums of cell references from 7 other documents. I now don't need one of the documents and therefore want to remove just that one link only from the formula. I would do it manually but I have hundreds to change! I have tried breaking the link to that one document but that removes the entire formula.
View 2 Replies View Relatedhow to return a blank or null value when the vlookup formula would normally return a #N/A? I am using the False range_lookup argument to get an exact match.
View 2 Replies View RelatedI am creating a formula. The formula needs to be something like this:
[Code]...
Basically, I need the formula to read the value in cell A and if it is a "1" then the value in Cell G will remain, all other values in cell A will erase the values in cell G.
VBA method to remove HMML tags from the contents of a cell?
It needs to remove all occurrences of the "" characters, plus anything that is found between a "< >" pair.
Any formula for removing text within multiple sets of parentheses?
I would like to take something like this: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N Engl J Med. 2012; 367:1098-1107) and CONFIRM (Fox R et al. N Engl J Med. 2012;367:1087-1097) trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
and have it read: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE and CONFIRM trials. Common adverse effects associated with dimethyl fumarate that were observed in these trials included flushing and GI events, as well as decreased lymphocyte counts and elevated liver aminotransferase levels.
I wish to automate this task so formulas (if possible) are preferred. Totally cool with a multi-formula, many-columned solution - as long as this task is automated.
NB: Each cell will contain different text, so the sets of parentheses will appear in different places. The number of parenthetical sets may also vary from 1-5.
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.
ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...
I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.
so the new column would be:
--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007
I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.
=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))
I have a list A2...A11
In cell B1 is the following formula: =SUM(IF(FREQUENCY(IF($A$2:$A$11"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),1))
Rvec is defined in NAME MANAGER as: =ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1
In cells B2...B11 is this formula: =IF(ROWS($B$2:B2)
I have a formula in a workbook that keeps giving me #DIV/0! no matter what I do. I am trying to total up a column of numbers. If I enter into B28 the following formula : =SUM(D4*E4)+(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)+(D10*E10)+(D11*E11)+(D12*E12)+(D13*E13)+(D14*E14 )+(D15*E15)+(D16*E16)+(D17*E17)+(D18*E18)+(D19*E19)+(D20*E20)+(D21*E21)+(D22*E22)+(D23*E23)+(D24*E24). it does not give me the #DIV/0! but the result is incorrect.
what I need is a formula that totals rows 4 through 24 but the formula for each row individually would be =(d4/b4*e4) but I am trying to avoid having to add multiple extra columns (& clutter) for each item in each row that I need to calculate. I have attached the workbook so that you can see what I am trying to do.
So I created a custom Add-In, but the add-in isn't removing itself when I uninstall it or when I close the program (this creates an error in saving files, so all files are getting corrupted)
This is all the code I have running the menu bar and I can't figure out the proper closing code
I am trying to organize some meteorological data for a project and I ran into a wall, basically I have 3 columns in one there is the date, in one the hour and in the third one the temperature the issue is that in the hour section i have the hour 12:00 that repeats its self , and this goes for the hole year , pretty much every day i have the hour 12:00 that repeats twice , so select for every Monday , Tuesday etc only certain hours.
View 5 Replies View RelatedI have the following forumla....
=SUM(B11+E11)/F11
in those three cells there is a value of 0
the following message appears in the formula cell - #DIV/0!
is there a way to change my formula so it doesn't display this message and just return 0
how i would remove telephone numbers in a particular spreadsheet which are preceded with 44 which need to be removed and then replaced with a zero.
eg. 441234567890
I have list of data references about 60000 of them but some are duplicated. I have used advanced filted then unique records only. So now i have just the unique records showing now. How do i copy the accounts that is just unique into a new worksheet? I tried copying it but its copying everything. I even tried using paste value but still copying everything?
View 4 Replies View RelatedI need to remove/trim in column B the last three characters in each cell so that I am only left with the first 12 characters.
View 3 Replies View RelatedI have some cells full of user entered information that has returns in it, so that it's multiple lines long. What's the best way to remove returns from a string?
View 3 Replies View RelatedI am Facing is I get this error as #DIV/0! when i use the formula where fields are blank or 0.
The Formula is: -
=IF(E4>=L4,(E4/L4)-100%,(E4/L4)-100%)
What changes can i do in this formula that it calculates exactly as it is doin now but instead of #DIV/0! error it returns the value 0.
It seems like I am asking a question at least once a day. I searched the net and forums and could not find an answer to this question. Thanks to everyone for the help I am making a lot of progress because all of you. Basically, I have a read-only workbook which the user gets a prompt to save as a new workbook for editing purposes. I have a autorun macro so now when the user opens their workbook, it contains my autorun macro. I do not want this. Is there a way to remove modules so that the users copy has no macro information?
View 9 Replies View Relatedi would like to take the first letter of a cell and add 1000 to it.
At the minute, ive only been able to take away the first letter which isnt what i want to do
so far i had
=RIGHT(B1,LEN(B1)-1)
i have columns of prices in 2 decimal format that I'd like to manipulate, some prices are whole dollars(no cents & no decimals ) and most are dollars and cents in decimal format-- but to do it successfully, i need the decimal removed. I looked at the "format" function but it doesn't seem to allow for that.
is there any way I can have these prices converted to "cent" format( ie removing the decimal, where applicable), showing them not as "dollars & fractions" but as "cents" i'm sure that would solve the problem for me last time I did it by hand and vowed there had to be an easier way!
I was wondering if anyone had a formula or Macro suggestion to remove rows with no information. I made a pivot table that feeds to another sheet in order to make it more user friendly. The only thing is when there is no information I have formula to returns a zero. I would rather the row be hidden.
View 9 Replies View RelatedI have a spreadsheet which contains 2 columns of data, most of which are duplicates.
I'm looking for a macro which will check all of colum A (A2:A138)
against
Column B (B2:B163)
I would like the macro to remove duplicate entries (from column A) in column B so that all that is left in column B are entries which don't match any in column A
I have lists of phone numbers with parenthases and hyphens. like (555) 333-4444
I would like to remove all symbols and leave just the numbers.
I would alsolike to add the number 1 in the first position.
so (555)333-4444 would end up 15553334444
I have a string in each cell in range D32:J55.
The second, third or fourth character at random is a npsb 'ALT(0160).
how I can keep everything left of the npsb only.
Data looks like this.
5 (1)
23 (4)
10 (5)
101 (3)
etc,etc.
Sheets("Group Template").Select
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir "Y:steevesDesktop"
ActiveWorkbook.SaveAs Filename:= _
"Y:steffenDesktopOfferings.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
Can someone tell me how to remove the select from this code?
My data base downloads with signs $,#, etc
How can I remove these from a cell and leave the numbers
example
A1 102,462#
A2 83#
A3 4,265#