Formula Is Being Replaced By The Data
Dec 29, 2008
I have a "data" sheet that holds information on it and another sheet that has a printable form that uses formulas to extract and calculate the info based on what is entered into the user form. The issue I have having is in a certain number of cells, the formula is being replaced with the result. There are other cells that have the exact same formula and are fine. I can’t figure out why these certain cells are doing this.
There are no control sources overriding the data and I can’t see anything in the code that relates to these cells?
View 9 Replies
ADVERTISEMENT
Oct 18, 2007
I am writing some VB code to change a comma separated text to individual columns
Selection.TextToColumns Destination:=Range("O14"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
When I run the macro, it asks me if I want to replace the contents of the destination cells. How do I get rid of this prompt? I have tried to clear the contents of the cells prior to running the macro, and have also coded:
Application.AlertBeforeOverwriting = False
but, it still prompts me.
View 2 Replies
View Related
Apr 2, 2008
Using an excel file sent to me to fill out. File is 97-2003. When I type in text it shows up in the cell. However when I click to another cell the text in the previous cell turns to ########. I can double click it and the text reappears. I am unable to print the spreadsheet as I can only see the text when that cell is active...when I move to another cell to enter more text...can't print.
View 9 Replies
View Related
Oct 3, 2006
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.
View 2 Replies
View Related
Jun 4, 2007
How I might be able to streamline the following formula so that the #N/A is omitted (replaced with a blank cell) but there isn't quite as much of a time lag?
-->
=IF(ISNA(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)),"",(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)))
-->
Here is the background information for what I am looking to achieve:
I have two spreadsheets I am working with. The first spreadsheet, the "What Computer has User Logged Onto Log" spreadhseet, dynamically imputs and displays data from the log file of a logon script: the date (column B), time (column C), computer name (column D), username (column E) and IP address (column F). The second spreadsheet, the "Quick Computer Jump Spreadsheet", has many pieces of information (including the computer name (column F) and computer description/room number (column P) -- which are the two important pieces of information there).
Yesterday I found a tip that allows for VLOOKUP via two different spreadsheets. Specifically to have the computer description/room number column (P) information from the "Quick Computer Jump Spreadsheet" filled in as column G in the "What Computer has User Logged Onto Log" spreadsheet. It was this formula here: =VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)
This was great because it meant not having to copy and paste the computer description and room number information into the spreadsheet I want to apply the VLOOKUP to and means I don't have to maintain and update the computer description and room number information in multiple locations, whenever that information changes. This formula worked beautifully, seems to be every bit as quick (with no visible lag) as when calling for the information from inside the same spreadsheet. In the case of dynamic data I could even use the Fill Formula all the way down to the bottom of the spreadsheet so that as other fields were filled, so was the computer description and room column. In short, it pretty much did everything I wanted it to
The next challenge I set for myself then was to see if I could get rid of the annoying #N/A when data wasn't found by the VLOOKUP function. Especially in those cases where I filled down below the last row in my spreadsheet in order to have the computer description/room number information automatically filled in as the other columns (A-F) are filled in.
I did some additional research and found this tip (here: http://www.mrexcel.com/archive2/47600/55248.htm). Using this information I was able to revise my original formula so that it now looks like this:
=IF(ISNA(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)),"",(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)))
This works but is unfortunately much slower than the first (I presume because of the double VLOOKUP required in order to compare and then execute?). Up to 30 to 45 second delays when the "What Computer has User Logged Onto Log" spreadsheet is refreshed.
I found the webpage info, here: http://www.mrexcel.com/td0110.html that had the following alternative ways to write the above formula that don't require the double lookup, thus making them much faster and more efficient to use. The problem here is the examples given are for absolute searches instead of non-absolute like my formula above and I have been unable to figure out how (or if) I can adapt any of these solutions to my own.
Does anybody know of any other way I might be able to write my formula above, to receive the same result, without having to do the double lookup, and thus decreasing the 30-45 second delay every time the spreadsheet goes out to update the computer description and room number information?
View 9 Replies
View Related
Jun 12, 2013
I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.
This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.
Is there any VBA code or something that I could run that will update the links to pick up the new files?
View 2 Replies
View Related
Dec 8, 2009
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
View 4 Replies
View Related
Jan 22, 2014
01. I want to Type "Al Raha Pharmacy" in a cell
02. When I type "Al Raha" and I press space, then the word automatically changed to "Al Rahma"
03. I tried this in other cells also but the result is same.
View 1 Replies
View Related
Dec 17, 2013
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
View 2 Replies
View Related
Jun 30, 2014
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
View 5 Replies
View Related
Jan 8, 2014
I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
View 2 Replies
View Related
Jun 1, 2011
I have a 5000 line table I am filtering by a few columns, and I'd like to calculate an exponential trendline value.
=INDEX(LINEST(LN(R1059:R1167),W1059:W1167),1)
But I actually don't want all the values from R1059 to R1167 - I want to select only the displayed values (R1059, R1068, R1077, etc). Is there a way to select only display values to use in a formula? The problem is it would be a lot of manual work to select them all - there are 50 or so instances I would have to select 13 manual values.
I am using Excel 2007 on XP.
View 8 Replies
View Related
Nov 14, 2008
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
View 9 Replies
View Related
May 12, 2013
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies
View Related
Jan 9, 2014
Essentially, I would like a user to be able to select a PUB from the Data Validation drop down in row 2 of the PUB RATES sheet so that the corresponding information in the DATA sheet autofills.
Currently, this works only for the first column under each PUB when selected and this fills across all 4 columns (rather than the respective information for each column filling).
Also, the Data Validation dropdown includes blanks which I would like to exclude.
I have used a Range Name for the Data Validation of each PUB so that these can be drawn from a separate sheet as I don't want all the DATA content on the same sheet as the PUB RATES content.
View 4 Replies
View Related
Jul 17, 2014
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
View 7 Replies
View Related
May 8, 2014
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112
Value 2345678910111213
And get it like:
Week Value
1 2
2 2
3 2
4 2
5 2
6 3
7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
View 11 Replies
View Related
May 1, 2007
=IF(E7=F7,0,IF(E7>F7,E7-F7-INT((WEEKDAY(F7,2)+E7-F7)/7),F7-E7-INT((WEEKDAY(E7,2)+F7-E7)/7))).
I have this formula in a cell. f7 is the control cell. if f7 has nothing in it i dont want to do the formula, and if f7 has data to do the formula, is this possible to do?
View 3 Replies
View Related
Dec 12, 2013
I need a formula to automatically transfer data in a column into another column, omitting cells in the 1st column that do not have data in them.
So, for example, transfer the data in column "A" below to column "C" below omitting any blanks when the formula automatically copies data over:
Example Spreadsheet.xlsx
View 3 Replies
View Related
Oct 1, 2006
Note: I know the syntax below is not how you would enter forumlas into Excel but I am using it to quickly illustrate what I'm trying to do and need help with. A cell not in column D, E, or F contains a formula of the form: F=IF(D>0, D/E, "blank") .
Note: If a cell in column D>0 (eg. D5>0) then the cell to the right of the selected D cell (eg. E5) will also be >0 ; otherwise, both cells will be empty.
This is what I want to do: If cell D?>0 , then F?=D?/E? ; otherwise, F?=empty .
Example 1: If D5>0, then F5=D5/E5 ; otherwise F5=empty .
Example 2: If D7>0, then F7=D7/E7 ; otherwise F7=empty .
Etc. I want this to apply to all rows.
I cannot enter the formula directly into the F? cell because sometimes I will need to enter data into cell F? manually. When I need to enter data manually into F? this results in the formula being overwritten by the new data and this means that if I ever needed to have data calulated based on the formula F=IF(D>0, D/E, "blank") I would have to re-enter the formula from scratch over again; obviously this is a nusance.
View 2 Replies
View Related
Mar 6, 2014
I have bunch of column Data in Column D:H
My data starts in row 5. I need a excel formula to add data every 63rd row for column D:H.
So for example for column D, i would have D5+D67+D129 and so on until D687.
View 8 Replies
View Related
Jul 5, 2012
formula to add up certain data.
In the example below, in Column A there are "T"s and "C"s. In column B there are distances.
In Column C, I need to sum the distance travelled since the last "C" on each row with a "T".
Sheet1 Â ABC1TypeDistanceTotal travel since last "C"2CÂ Â 3T334T475CÂ Â 6T227T468T289T72410T41911T52412CÂ Â 13T2214T46
View 4 Replies
View Related
Nov 22, 2009
Is there a formula I can use in data validation that will only allow a six digit number and not allow other characters such as a /?
I tried =LEN(A1)
View 9 Replies
View Related
Apr 11, 2007
The data from the Data sheet is copied over to the Report sheet with formula.
Basically, what I need to do is If statement:
-when there is no any data in csv file (so the Data sheet will be empty);
-the range (A7:N1000) to be hidden (on the Report Sheet)
-the Report sheet to be saved
and also...
I would like to ask, how to select from the Data sheet, the rows with Data only. I copy them over with formula instead of macro- but would like to hide all the empty rows on the report.
Option Explicit
Sub Update_Report()
'
' Macro1
Dim extract1 As String
Dim dReport As String
Dim rSheet As String
Dim dSheet As String
extract1 = "LeadSheetAll_0001.csv"
dReport = "Appointments.xls"
rSheet = "Report"
dSheet = "Data1"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
View 9 Replies
View Related
Nov 15, 2012
I have travel data for hundreds of cars. Col A has the car ID number. Col B has the date. Col C has the distance of a journey. Column D, has the total distance traveled on a given date for a given car.
In Column E, I'm trying to calculate the remaining distance to travel "prior" to the journey being made.
I a have attached an example. [URL] ....
So in column E, prior to the first journey (9km) being made there is 19 km still to travel in the data. And for the second journey the is 10 km left to travel.
View 1 Replies
View Related
Jul 26, 2014
I've got a table containing different codes and their values:
AAA 1
AAA 1
AAA 5
XCC 2
XCC 1
WTY 7
WTY 2
...
I'd like to group them so the code shows up just once whith its' value summed up:
AAA 7
XCC 3
WTY 9
...
See the file attached.
View 3 Replies
View Related
Dec 7, 2013
I have lots of data.What is the best way (formula) to add I10+M10+Q10+.....DY10, (Every 4th column)
View 9 Replies
View Related
Jan 12, 2014
Please refer to attached sheet.
I have number 10000 to 10200 in column D.
I need to use IF Statement and add corresponding number in Column A as follows.
IF D2 = 10000,10006,10012,10018,10024 and so on then A2=20002
IF D2 = 10002,10008,10014,10020,10026 and so on then A2=20003
IF D2 = 10004,10010,10016,10022,10028 and so on then A2=20004
(Basically there is increment of 6)
Once i have formula for A2 then i can copy down the column.
Book1.xlsx‎
View 8 Replies
View Related
Sep 30, 2008
Which formula do you recommend I can use to rank the below fields in yellow. I need to find the following:
1 - Which sales advisor (their name) has got the highest MPPI conversion and place it in cell K32.
2 - Which sales advisor (their name) has got the highest HC conversion and place it in cell K34.
3 - Which sales advisor (their name) has got the highest Highest Apps conversion and place it in cell K36.
View 5 Replies
View Related
Jul 28, 2009
I have a spreadsheet and I'm comparing data from a room booking system and a spot check on the room to see whether it's actually used. I have a column which I want to show whether the information agrees.
column G - I have a group size recorded that should be in a room.
column H - I have "yes" or "no" as to whether there was anyone in the room when it was checked
column I - I have the size of the actual group in the room.
Is it possible to create a formula that will fill in column J with the answer yes or no depending on whether it agrees.
It doesn't matter whether the groups sizes match, it's just a case of yes, someone was booked into the room and there was someone in when it was checked
OR
No, there was a booking supposed to be in the room but there was no one in it when checked
OR
There was no booking but there was someone in the room when checked.
View 4 Replies
View Related
