I need to include over 70 data fields in a pivot table that I want to add/remove with a pivot. They need to have an alternative caption and be formatted. I am currently using the following piece of With ActiveSheet.PivotTables("PivotTable1").PivotFields("tytwprods_v_lytwprods_(abs)") .Orientation = xlDataField .NumberFormat = "#,##0" .Function = xlSum .Caption = "Products 2007-08"
However, I need to do this many times and it is a bit painful. I have created a spreadsheet with concatenated fields to create the above, although on one line which isn't liked by VB.
How can I make my code work but with it all on one line like:
not compatible with older versions of Excel? When I try and open my workbook on an older version (2000 and below) I get a runtime error and it highlights this. What's weird is that even when I open the it at home I get this and I have 2003 at home.
I have recently discovered the concatenate formula. Is it possible to include 2 variables with 1 constant entry. i.e. I wish to establish file names with 2 variables, e.g. 0051 and 1025 followed by a file extension, .EC0 - the file name being 00511025.EC0. The variables are in Excel columns. I can achieve the wanted result by establishing an extra column and copying .EC0 into every cell of that column; but I would prefer not to have that extra column in the completed Excel file.
In Excel 2003, I remember being able to tab across a row of cell entering data and when I got to the end, I'd hit enter and it would take me to the beginning of the next row. But in 2007, I can't...is there something I need to do in Excel Options or is this function gone?
I am working with a data sheet that holds a cell containing a number of position entries with each entry separated by a carriage return within the cell. I need to determine the entry associated with Accountant. The formula below determines if the entry Accountant exists and if so, displays 14 characters of information starting after the 12th character. The entry after "Accountant" is variable, but will have a carriage return at the end of the line. How can I identify the placement of the carriage return after the Accountant: entry?
Selection.Replace What:="GD", Replacement:="BLACK", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:= _ False, ReplaceFormat:=False Selection.Replace What:="WD", Replacement:="WHITE", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:= _ False, ReplaceFormat:=False GD and WD are in the same cell, I would like the end result to go from GD WD to:
A cell contains the name, tel number and address of a user. Those 3 fields are separated by a carriage return (alt+enter) in the cell. I need a macro to keep only the name, which is on the first line of the cell. The macro should be generic and should work for any particular name.
I am looking for some assistance in creating a macro that would allow me with one key stroke to enter a cell in edit mode, add a carriage return to the end of text in the cell, then move the cursor to the next cell.
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
I've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?
On the "Bill Dates" sheet, you can see where I'm kind of going with this...
On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.
I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.
and excel is saying I used too many characters and to use the concatenate function to do so, but I have no idea how.
I wanted to return text rather than a '0' in the case that all the original IF statements were false, I threw another IF statement in there and said if the original is equal to '0', then return "-". If not, then run through the original again (I pasted the original again after the "-").
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC ______Loc__PN 1,2____1___A _______2___A _______3___B 4,5____4___C _______5___C
When I save my file as text and upload into a program, I get a response saying that my file can't be processed due to carriage returns. What are these things and how do I get rid of them? My excel file has about 6000 rows and 50 columns of data.
I have 4 cells with text in them that I am trying to combine into one cell and not have any blank lines between the text. The cells are arranged like this:
A1="One"B1=Cell where the text is combined A2="Two" A3="Three" A4="Four"
I set the cell alignment in B1 to wrap text and use the following formula in B1: =IF(A1=,,A1)&IF(A2=,,CHAR(10)&A2)&IF(A3=,,CHAR(10)&A3)&IF(A4=,,CHAR(10)&A4)
The problem is if there isnt anything in one of the cells in column A it makes a blank line between the text in column B. I am trying to get the text to the top of B1 and not have any blank lines between the lines of text. Is there a way to change the formula so that it will do the following three things:
1. If the cell A1 doesnt have anything in it then dont put the value in B1. 2. If A1 has something in it and A2 has something in it then put the text of A1 in B1 and HAVE a carriage return after the A1 text. 3. If A1 has something in it and A2 does not have anything in it then put the text of A1 in B1 but DO NOT have a carriage return after it.
I have done a search on carriage returns/characters and what has been suggested is not working. I have BOTH carriage returns in the cell and also the boxes (that I assume are also carriage returns. I tried the substitute formula (=substitute(cellref,char(10),)) in excel but it only removes the alt+enter carriage return. I tried the various suggestions of find & replace but the chr$..etc did not work! Is there a formula to clear both in one (not a macro!)..?