Cell A1 could contain either % value (eg 50.0%) or could contain a numeric value (eg 50.0). (It's input is written by a macro that performs a sorting function, hence the mix of % and absolutes).
I need to copy the contents of A1 to cell B1 with a simple formula "=A1"
The problem is that Cell B1 will either show 0.5 or 50.0 depending on whether A1 is a % or number, respectively.
Doesn anyone know how I can output in B1 with the format from A1?
I have a worksheet that gets autofiltered by the user. I need take the unhidden data and copy it to a new worksheet.
Range("H18").Select Dim sh As Worksheet Dim Cell As Range Dim Txt As String For Each Cell In Sheets("Panel Check List").Range("H18:H5000") If Cell.EntireRow.Offset(1, 0).Hidden = False Then Cell.Copy Sheets("Query Results").Range("A6").Select If IsEmpty(ActiveCell.Offset(1, 0)) = True Then ActiveCell.Offset(1, 0).PasteSpecial End If End If Next Cell
I have a matrix of coordinates in sheet ("layout") (eastings - V4:BR4, northings - U5:U100). I'm trying to run through each northing (row value U5:U100), for every easting (V4:BR4), by writing the coordinate value to sheet("ISO_model"), cell K18. Within the sheet (ISO_model) there is a model which gives an output in cell HA500. I'd like to write this output (for the specific easting and northing) back into the sheet ("layout"), so that I then have the x,y, z values to create a contour plot.
I've tried to start the look through the row of eastings, but it is not working.
Sub noise_contour() For Each Cell In Range("V4:BR4") 'write coordinate into the model
I'm trying to copy a vlookup to an entire column, I want to look up a value in the cell to the left and compare it to a given table; but when I copy it down it up dates the cell value, but it moves the table down by one row at every row so it's not finding most of the values the further down I go.... what am I doing wrong. I doubt I'd have to re write the formulas in all the 2500 cells I need to look up.
I have a table where alternate rows are formatted with interiror colorindex 15. Macros will result in 1 or more of the rows in the table being hidden (this can be consequtive rows).
Obviously, when a row is hidden (unless that results in 2 consequtive rows hidden) the alternating row format no longer works (ie either 2 grey rows or 2 nofill rows together)
I am struggling with the code to set the format when a row is hidden. I loop through the table to set format, fine if no row hidden. But when row is hidden everything I try to do to take account of the hidden row results in an endless loop when the hidden row is encountered.
I have a spreadsheet that has tons of entries for revenues and expenses. Some cells on the rows with revenues have a different format than these same cells on the rows with expenses.
The problem is that, when I sort the rows to organize them by date, Excel seems to only organize the values in the cells, screwing up all the formats, mixing the revenues format with the expenses format.
Is there any way to organize the information maintaining each cell's formats?
I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76% B1 = formula resulting in 1.90% C1 = formula resulting in 1.69%
I have an Excel 2007 file that is a work of art. Users manually copy several rows and columns from a table on a web page and paste it into my Excel file. This turns my beautiful Rembrandt into a Jackson Pollack mess. The formating of my cells is replaced with the formating from the web page. Is it possible to make the Excel file ignore the formatting of the data being copied? As a default?
I'm trying to use a non-modal userform as an application option board. Using API, the initial (Activate event) form transparency is set to 10%, and the Userform_MouseMove event turns it to 100%. The problem is that I need the transparency back to 10% (hiding it would be enough) whilst mouse remains outside the userform, and I've no idea of how I can get it.
I wish to protect a worksheet to prevent loose fingered people from altering formulas, but I want the "text box" that is below these formulas (within the same worksheet) to be able to be typed in.
But when I protect the worksheet, the text box cannot be typed in.
Is there a way around this to allow typing in the text box whilst the sheet is protected?
I would like to reorder a list whilst ignoring any 0 values. I've attached (I hope correctly!) a spreadsheet of what I mean. It's pretty simple, but I've been banging my head over it for a while now!
As part of a working spreadsheet there is a section which records faults by way of a numerical list in a dropdown menu. When a number is chosen the fault description appears in the adjacent cell. Is it possible when the mouse hovers over a number in the list the first 20 characters of the fault description could be shown before the fault number is chosen? The attached file is a simple example of the fault numbers and descriptions, in reality some of the faults have longer descriptions with more technical detail.
i have data in worksheet 2, a table with formulas using worksheet 2 in worksheet 1, i need to move the data in worksheet 2 to another worksheet, but if i use cut or copy and paste the formulas do not track its movement, so how do i move the data to another worksheet so the formulas know where it went? i thought if you could select the data and drag the data straight into another worksheet but how?
eg say you call (ie insert a range name) cell A1 "firstcell", B1 "secondcell", then A2 "divisor1" and B2 "divisor2". if you put a formula in A3 which is "=A1/A2", how do you copy and paste this formula into B3 but getting the formula to reference B1/B2 rather than firstcell/divisor1, as it does by default?
I have a consolidation workbook and source files.I would like to convert the data from the source files into a list format in the consolidation workbook.
I have attached a sample of the sheet format of the source files in the attached file, called 'Page 5'. The other 2 sheets are 'Template' and 'Instructions'. The 'Template' sheet is what I imagined would be the list format of the data copied from the 'Page 5' sheet. Instructions is where the lookup table for currency is.
So basically starting from row 8 in 'Template' sheet, I would like to copy and paste from 'Page 5' sheet to 'Template' sheet: - H2 to A8 & B8 - B2 to C8 - According to the list of currency in 'Instructions' sheet, lookup the currency according to operating unit in C8 and paste to D8 - D5-I5 to E8 - row A8-A23 to column F-N
I actually have had a similar problem before, which Derk has helped me here - link: Use Access or XL? (I've decided to start a new thread because this is a more relevant forum).
I have tried to modify the code but I am rather lost as to which part I am supposed to modify.. Note that in this code, the source data is in separate file instead.
Sub add()
Dim wb As Workbook, f As Worksheet, t As Worksheet, j As Integer, k As Integer, n As Integer Dim mty As String, yr As Integer, d As Date, bu As String, cur As String, sTodo As Variant
sTodo = Array("Page 5") 'finish adding the names Application. ScreenUpdating = False Set t = Workbooks("Example1.xls").Worksheets("Template") i = t.Cells(65536, 2).End(xlUp).Row
if i am copying cell A and pasting it into cell B, and cell B is shaded gray and cell A was plain white, how do I make sure the color of cell B wont change?
I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.
I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:
I'm making a dashboard and currently using a camera tool to show a sheet in a different workbook.
However the camera shot should show the sheet for the current month. (the workbook contains 12 sheets one for each month)
I was trying to reference a cell within the dashboard workbook in the formula bar to select the correct month.
[Code] .....
This is the workbook being referenced and
[Code] ....
This is what I have to try select the sheet with the right month. W2 contains whatever month it currently is.
I get that this formula is wrong because I'm referencing a sheet/cell in the dashboard workbook right after calling the tracking workbook, I just don't know how to write it correctly.
I have some data coming in from another source system which has the date format as "DD-MM-YYYY" but while pasting it to Excel (2007) sheet some date fields has just DD-MM-YY format which disrupts my macro. I did changing the language settings to "English(UK)" ,which i wanted, and changed the same in Excel options as well. But unfortunately it doesn't work.
I'm a 2007 user. I am trying to conditional format a range of F1:J10, where F1 will be conditionally formatted relationally to A1. G1 is relational to B1. H2 relational to C2, etc., all the way down to J10 relational to E10. My conditional format formula in cell F1 is >A1*2 (will format F1 as orange). I want to be able to copy the conditional formatting over so the formula in J10 for example would be: >E10*2 (will format it orange).
I omit the "$" in the conditional format formula in an attempt to prevent it from being absolute. But every time I copy the conditional formatting over to the other cells, the formula within those cells remains identical to the formula in the original cell.
I have an Excel sheet with a list of data, which is likely to change frequently and has to remain in sorted order.
Requiring that users of the spreadsheet maintain the data in this way (i.e. sort it every time it changes) is a really bad solution and I'd rather not use a macro if I can avoid it (too much stuff can go wrong).
Is there a good way of doing this? At the moment, I've implemented merge-sort within a sheet, which works, but is a heavyweight solution, taking 6 columns per merge-sort iteration (so 60 columns to sort 1024 rows, 96 columns to sort 65536 rows). I could try to compact it, but my head was starting to implode programming it even with as few as 6 columns.
There must be a better way of getting Excel to maintain an automatically sorted list.
I need to round a number of values to 2 decimal places. Problem is this sometimes results in the total changing as the values after the 2 decimal places make up the remainder. I need to maintain the total. See attached..
Has anyone done this before? Probably something very simple but for some reason it's not coming to me. Well not without vba anyway..
I have data arranged in columns A-F. I am wanting to set it up so those groups are all based on the name in column A:
Name Location Quantity Notes Etc.
Joe Likes bread Hates butter Jane
Julio
Column A only takes up one space, while the other columns take up 3-4 spaces on average. I am looking to sort column A alphabetically while maintaing the spaces between so that the info doesn't become jumbled.
I have a spreadsheet with nested subtotals. i need to sort it based on one of the nested subtotals, but maintain the rows that comprise the subtotals, together with the subtotal.
is there a way to do this? I don't want a macro because then the whole project will be done as a macro. this is just a small part of what i am doing.
sample included. my goal is to sort by column F (ABS value) high to low so that rows 8-13 are together and maintain the subtotal and on top, then rows 34-36 are together and maintain the subtotal and are next, etc.
I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line 2) when a new line is copied and pasted I need the contents to be cleared 3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
[Code].....
The code I'm using for my "Delete" button is:
[Code] ..... The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).
I have a protected sheet with merged cells. I would like the user to be able to insert a row and have the formatting (including merged cells) duplicated on the new row. I've searched and seen some different options using VB but I'm clueless as to how to use those. Is there a simple way to do this?
Copying the row and using "insert copied cells" won't work because they get an error because of the sheet protection.
