I have an excel file with 2 sheets. Sheet 1 has a column that contains formulas (ie (18299*11151)/20067 ) Those numbers are IDs referencing questions stored in Sheet 2. What I would like to do is find a way to look up those questions and place them into the formula instead of the ID numbers.
View 6 RepliesI am working in Excel 2010.
I am looking over spreadsheets in a new job, and I am coming across formulas I am unfamiliar with?
For example, =(G16*G17/C13-G24)*hrs and =I25*elec.
hrs and elec??
After using the "Trace Precedents" feature, the "hrs" and "elec" are still a mystery to me. Are these variables that the creator of the spreadsheet has defined? They don't appear to be named data sets because they exist in just a few cells.
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
I have a database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).
in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.
Example of Table.JPG
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies View RelatedI have a text box that is set to "=B1". B1 is a cell from an Access table import with memo format. The text memo is long, probably on average between 1000-2000 characters. I have set up the text box to automatically resize for the text, but the text stops at what I assume is 255 characters, even though cell B1 displays the entire memo.
How can I set it up so that this text box (or any other similar shape) displays the text and retains its resize/wrap property?
This is done in Excel 2010.
I am copying the value in a cell to another sheet's cell. I want to convert the formulas on the second sheet to reflect their value. I do this all the time within cells by using Past Special and Values however it doesn't give me that option within the Text Box
View 2 Replies View RelatedAny way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)
I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.
I have just upgraded Excel from 2003 to 2010 and cannot locate where I would convert a spreadsheet to a .pdf document.
View 2 Replies View RelatedI have imports with several values listed as :00. I am unable to adjust the import or export to make these values 0:00 as they should for calculating. How can I convert the :00 values to 0:00? Its literally just like I need to add a 0 zero in front of the colon ":". This is the case with anything imported that is less than 1:00 minute. I have :55 that should be 0:55 and so on. I have tried re-formatting for different time values, tried various formulas that were listed in the forums.
View 4 Replies View RelatedI need to know if it is possible to combine these 2 formulas (1. (RIGHT(C23,0)&RIGHT(C23,4)) and 2. LEFT(E23,2)&":"&RIGHT(E23,2)
into a single cell. I have a date time group that is in text format. I need to take the last 4 digits (the time) and pull it out into another. Then take that cell and convert it into an actual time ie 2:30.
example: 01may2014 0831 first formula pulls the 0831 into another cell. then the 2nd formula converts it into a time 8:31.
After this occurs, I can then subtract times from different categories in my spreadsheet.
My, problem, is that I cannot combine the two formulas. They work great by themselves. I am just trying to make formulas more compact and hopefully more efficient.
I Need to Understand Running Total Sum Formulas. I'm using Excel 2010. and I'm typing in the basic correct formula to arrive at a running total for each ajacent cell to the left of it.
I put in the correct formula in Cell (B1) of Sum=(A1)
I then put in the correct formula in Cell (B2) Sum=(A1:A2) I then highlight the A1 part of the formula with an F4 Key to lock it in.
I then drag the B2 Cell all the way down the excel page to capture all of my running total coming from the (A) Column just to the left of my formula.
Why even though I'm getting the answers I want in the running total does it put an error message in each of the correct answers in each of the cells in the (B) column where I put my formulas.
I tryed to delete the error message spot but I don't know how to delete all of the error spots without going into each cell one by one.
How can I get rid of all the error message out of each cell without having to click on each cell one by one to do it when I might have over 500 or more cells to click on.
I'm using Excel 2007 and I'm a VBA novice.
Problem: The macro will be assigned to a command button and will be used by laypersons when they finish filling in data on a worksheet in Workbook 1. The sheet contains maybe 30 columns and 50 rows with a mix of fixed values and values generated by Vlookup and Indirect formulas. I need to copy the sheet from Workbook 1 to Workbook 2. Workbook 2 will be for archival purposes so I want to convert all formulas to fixed values. The catch is dealing with some cells that contain hyperlinks to PDF files...
Current Solution: I currently do this with a macro that moves/copies the sheet from Workbook 1 to Workbook 2, it then selects all cells in the new sheet in Workbook 2, copies all cells, then pastes-special "as values" to the exact same cell locations. This works great for me since the cell formatting and data in the sheet are VERY irregular and I have merged cells all over the place. This method keeps the exact formatting I need maintain:
ActiveSheet.Copy After:=Workbooks("Workbook2.xlsx").Sheets(1)
ActiveSheet.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
The problem is that a few of the cells have hyperlinks with "friendly names" and I lose the hyperlinks when I convert to values. The hyperlinks are not inserted directly, they are created by a formula, =HYPERLINK("N:Filepath"&C16&".PDF", "Click_For_PDF"), and the row and column that contains the hyper link will vary for each sheet I want to migrate from Workbook 1 to Workbook 2 using this macro. I want to keep the hyperlink active with the clickable friendly name in Workbook 2.
Possible Solution: I'm open to all types of solutions, but is there a way to essentially use my existing macro but AFTER converting to values with paste-special, go back to the original sheet in Workbook 1 that still contains formulas (or maybe a temporary duplicate sheet I migrate to Workbook 2?), search for all cells with a "value" of "Click_For_PDF", copy ONLY those cells and paste (normal) into the corresponding cell locations in the sheet in Workbook 2 that now contains fixed values? ALL of my hyperlinks have the friendly name "Click_For_PDF" so it should be an easy way to identify the hyperlink cells. The cell location of the hyperlink copied in Workbook 1 needs to carry over to Workbook 2 and I said before, the row and col vary with every sheet I want to archive with this macro.
I am relatively new to VBA and am trying to convert a range of data to a table in the same sheet. I receive the following message when I try to run the code as shown below:
"The worksheet for the table data must be the same sheet as the table being created." The code stops on the third line of the code.
Sheets("Data Forwards").Select
ActiveSheet.Range("$A$1:$U$1000").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$U$1000"), , xlYes).Name = _
"Table1"
ActiveSheet.Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
I can see that the range is highlighted in the sheet before the code breaks.
I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.
The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.
I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.
I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.
There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.
I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.
Formula:
=IF(AND(N3>1,ROWS($1:1)<>MATCH(M3,$M$3:$M$7979,0)),"Exact Duplicate","")
is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.
Still adjusting to 2010 Excel...How do you use the automatic fill in feature of formulas; e.g. when typing =convert....a fx function appears....not sure how to use it
View 2 Replies View RelatedI am trying to find the frequency of lottery numbers that occur within 10-day periods (see row 7). Instead of having to manually write each formula for each column, is there a way I can automate it, so that each column will "advance" 10 days?
Excel 2010
A
B
C
D
E
F
G
H
I
J
K
1
ct
0
1
2
3
4
5
6
7
8
9
[Code] .........
Array FormulasCell
Formula
B8:B17
{=FREQUENCY(B3:HNR3,ball)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
Worksheet Defined NamesName
Refers To
'am (2)'!ball
='am (2)'!$A$8:$A$17
Excel 2010
Conditional Formatting 1 cell with 3 different formulas
I am monitoring tank levels using a program called "PI". I need to know if the tank is rising, lowering or staying the same.
I am using conditional formatting to turn red if high, blueif low and yellow if stays the same. Column B, F and J are tank volumns, Column C is in feet and D is in inches.
A B C D E F G H I J K L
1 TIME LEVEL FT IN TIME LEVEL FT IN TIME LEVEL FT IN
2 7:00 3628 18 11 9:00 3456 18 0 11:00 3321 17 3
AB AC AD
1305 5600 3600
LOW HIGH CURRENT
I started using formals =$B$2=$AD$2 COLOR YELLOW
=$B$2>$AC$2 COLOR RED
=$B$2
I have been using Excel to record the routine daily issue of items to different groups in a matrix layout, I use a different workbook for each month with worksheets for each group. The matrix takes the form of the item issued being the left hand column and the date issued the top row of the matrix, the quantity issued is recorded at the intersection. Each item can have a different quantity issued on different days. I'm using Excel 2011 for Mac but could use PC Excel 2010. Is there a way to convert the data held in this way to a list? What I'd like to achieve is a list showing the Item, the Quantities Issued and the the Issue dates
View 9 Replies View RelatedI am trying to create an inventory list that automatically updates the supply quantity when items are taken away from inventory. I have created entry cells where the quantity of items taken out of inventory can be entered (example, cell F2 of the attachment), and the new overall inventory count is adjusted accordingly for each stock of items (example, cell B3). These automated adjustments are repeated for each subsequent row for every time items have been taken from inventory and recorded.
I managed to get it to do what I originally intended with exception of the fact that it still present values on rows where there were no activity - or quantity of items taken from out of inventory (row #6 and beyond of the attachment). Is there a way of "hiding" these values without removing the formula, or possibly set conditional formatting where no values are displayed if no changes in inventory has been entered?
Example.xlsx
Using Excel 2010
Is there a way in Excel 2010 that the administrator of a excel document can not only hide some columns but lock the columns by some kind of password so it remains hidden? I know user can hide/unhide columns but can some columns be hiddens and locked with passwords?
Secondly can formulas like IF and vlookups statements be hidden and locked with some kind of password?
In maybe via VBA or by some other means, is this possible?
The end user should only be able to type and select certain cells only.
Excel 2010
Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:
=COUNTA(B12:B353)
Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn't make sense in any way (still scratching my head at this return. It should have returned a value of 3.
I am looking for a macro that will convert the active sheet in an Excel 2010 file to a PDF, attach the PDF to an Outlook 2010 email message, and fill in the subject line and recipient address which are the same each time.
View 3 Replies View Related1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
I'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies View RelatedI have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.
the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.
I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"