I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....
View 6 RepliesI am using excel 2007 - I need the extra available columns in Excel 2007 for my application.
I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.
Format painter and "paste special - format" copies any formats as well as the conditional format rules.
Is htere a method within Excel to do this or can someone suggest a workaround?
I cant seem to figure out how to concatenate data from two cells into one cell and retain trailing zeros. If Cell A1 has ".0000" and cell A2 has ".0005" then I want cell A3 to show:
".0000-.0005".
I get "0-.0005" on my attempts. Alos, how do I make the value an actual number and not a formula?
I asked this along time ago and received a worksheet formula which suited my needs then. http://www.excelforum.com/excel-work...trailing+zeros
Now I need this in a macro. Below is my current concatenating code.
Is it possible to retain the formula in particular cells within an excel spreadsheet whilst clearing the rest of the sheet, and if so how?
View 3 Replies View Related"Smith B" is text in C1.
My current formula in C3 is a simple reference to a cell in another workbook:
=+'Z:Time Records2007[Smith B.xls]Daily Time'!$C1352
I would the formula to refer to C1 to get the "Smith B" part.
This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.
To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?
=+'Z:Time Records2007["C1".xls]Daily Time'!$C1352
I need a solution so that each day I can get a value from a single cell but it appears in a new column each day.
eg, A1 contains a numerical value (based on a formula)
Each day there is a new column, say, Monday = B1, Tuesday = C1 etc.
On Monday B1 takes the value of A1 and 'freezes' the value in B1
On Tuesday C1 takes the value of A1 and 'freezes' the value in C1, but the value in B1 remains as it was when it was originally frozen.
I was wondering is there a way to use vlookup and then when the particular piece of data is found (using vlookup) to paste it as a value instead of the formula? I have a file that tracks the market value of certain portfolios and i have a source file that i update every month. I dont want the numbers to be overwritten each month, i want to keep a history of the market values each month. Do i need to program something in VBA or can i work something up in the formula bar?
View 3 Replies View RelatedI have on a sheet, on say cell "J10" a formula "=A1"
Now suppose I to delete row 1, Then J9, which was formally J10, will now say "=#REF!" instead of "=A1"
Is there a way to maintain J9 (which was J10) to say "=A1" without giving a reference error?
I have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".
View 4 Replies View RelatedI'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far.
The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this:
if cell is numeric
if column in (E, M, X, Y)
if (value <c1 or > c2)
highlight in blue
end
else ' columns not in (E, M, X, Y)
if (value > c1 or < c2)
highlight in red
end
end
end
My attempt to put together a formula for each cell appears to work, it looks like this for cell B3:
'=AND(ISNUMBER($B3),
OR(
AND(.....................
I have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc
I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.
I have a VBA macro that deletes duplicates rows, however I would like to retain the information from the duplicate row, just before deleting it. For instance, If I have these two columns: ....
View 9 Replies View RelatedI'm usually able to find my answer but I couldn't find anything to match what I need. Anyway... this is either really simple or impossible to do. I have two cells which have times in each. Say A1 has 5:00 PM and A2 has 10:00 PM. I'm just trying to make it so one cell will say:
From 5:00 PM to 10:00 PM... So I tried this ="From " & A1 & " to " & A2
But as I'm sure you already know that yields this result:
From 0.708333333333335 to 0.916666666666668
I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
is it possible to concatenate in such a way that formatting is retained? For example, my =CONCATENATE formula refers to a cell containing 0.065, but which is formatted as a % so that the cell shows 6.5%. Concatenating that with text, however, produces "0.065 [text]"
View 4 Replies View RelatedI am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx
I have managed to retain leading zeros in a cell by formatting the cell and selecting Number and Custom and adding the maximum number of zeros I want to have in front of the number in the cell i.e. 000002.
I want to have another cell in the spread sheet which contains the value 35394000002 using the formula =A24&B24.
When I use this formula I get 353942. How can I retain these zeros?
How can i break the links in a row of cells but still retian the cell comments thereafter.
At the moment, the comments dissapear after editing the links in excel 2007 that i am using.
Changing the display (foe cells with comments, show) under file-options-advanced has not really worked.
This error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).
I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?
My workbook has about 15 worksheets with each fits to 2 printing pages.
I'm trying to use a macro to write a formula within a column of data.
Here's the
How to copy the cell formats of the copied range in the vba.
Code:
Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
With Application
[code]......
In effect the code looks at sheet 'data' and creates new tabs depending on the contents of column f and then pastes data from the entire row into those tabs as appropriate - this is what I asked for and works wonderfully. What I would like it to do is to also copy the format of the row that it is copying. (most importantly the cell borders and colours)
I can (I think) identify the copy and paste elements of the code but have no idea what if anything I can change to make this work.
The code is as follows
HTML Code:
Sub CopyData2()
application.ScreenUpdating = False
Call Sample ' this inputs data into column f
Dim rCell As Range
Dim lastRow As Long
[Code]....
I have a relatively complicated spreadsheet 'process' which involves a number of different people filling in Excel based progress reports, in similar formats (created from the same template), and then invoking a common "publish" VBA macro which opens a common "master" workbook and copies their sheet into the master one ( deleting any previous one of the same name). The master sheet thus contains around 30 similar sheets published from different people, plus a couple of summary sheets. Note I also then copy cells in order to get around the 255 character limit on worksheet copy
All works fine except this week it decided to raise the "Too Many Cell Formats" error when trying to open the master workbook. I didnt know about this error, but apparently there's a limit of around 4000 cell formats per workbook. Given that the individual sheets are similar, I presumed that they wouldnt all have their own cell formats. I dont know how to check the number of different cell formats (it's not the same as checking the number of styles) or how to condense them to avoid any limit.
Looking at the actual styles defined in the master workbook, it seems that styles are duplicated when sheets are copied - I have multiple "normal" and "hyperlink" styles. I dont know why, or how to avoid this.
So - any ideas on how to avoid multiple cell formats (or even styles) when doing a worksheet copy into a different workbook? It needs to be an automated solution (i.e. macro) - I'm not able to go in and perform manual operations every time someone publishes (i.e. copies in) a new worksheet
Is there a way for your linked data to cary over formatting such as Bolds, borders, fonts and colors?
View 3 Replies View RelatedI would like to display the following number of hours as day hour minute format
48.0 hrs as 2d 00h 00m.
When I try any command I get a message such as "too many different cell formats." Is my 1mb workbook simply too large and/or complex?
View 3 Replies View RelatedI need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?
E2 is 134
=CONCATENATE("=VLOOKUP($B2,$C$", E2+1, ":$F$2571,3,FALSE)")
returns: =VLOOKUP($B2,!$C$135:$F$2571,3,FALSE) but not as a formula, as text and I need the formula...
I am using CONCATENATE formula to make summary of my data, but i m using long CONCATENATE formula. I want to use short formula instead of CONCATENATE
formula is here :
[Code]....
How to short this formula and accuracy is first ...