take out comma ID, 10000 to ID 100000 in column
I have a long column and I need to take out the commar between the State and
the zip code - anyone know how to do this without going into each cell??
Select your data
Edit>Replace, Find what is a comma and Replace with nothing
Press Replace all
"Annette" <Annette@discussions.microsoft.com> wrote in message
>I have a long column and I need to take out the commar between the State
> the zip code - anyone know how to do this without going into each cell??
...Help with copying dynamic column selected based on remote cell value and dynamic formula fill
Before posting my nightmare I would like to direct my thanks to all of
you who posted answers before. Browsing trough the response I complete
maybe 80% of my task in a very neat way. The remaining 20% of the task
it seems that will require more than 80% of the time!
There are 3 worksheets named S1, S2, and S3.
I use a macro to first fill the no value NA() a range sayA1:C700 in S3!
And second to open a text file and format data in it (get rid of empty
rows via sorting since data in first column is ascending, etc). At the
end of this macro there is another worksheet named P1.txt which has 12
How do I use VBA code to lookup the last entry in a column.
Select row 65536 in the column of interest and then control-up. You can
record this and then look at the VBA:
> How do I use VBA code to lookup the last entry in a column.
...Loop through columns and sum them at bottom
Hi, first time poster, so be kind.
I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1...copy rows with values
First step is no problem:
in Sheet1 col AI this formula is inserted with VB to add age groups
7-15";OM(OCH(Age>15;Age<26);"c. 16-25";OM(OCH(Age>25);"d. 26-";""))))
Col is autofilled to last used row.
In next step I loop thru these rows to find certain rows and copy them to
Sheet2. Problem is that formula on each row is then copied. Instead I need it
to be values only!
Needs to work in Excel 2003 and 2007. Sheet1 can have >20 000 rows
Any s...overcoming zero values in line graph
I have a 12-month line graph where some of the earliest and most recent data
is still zero.
Is there a way to have Excel not plot zero values - to avoid the big rises
and drop offs they cause - and, rather, just have the graph start and end
wherever there is positive data?
(NOTE: for multiple reasons, I can't erase the formulae in the cells
yielding the zero values, or have them display the N/A message....)
There is no reason why you cannot have a 2nd data set that has the N/A
values. Plot this second set and use the first for whatever you are
using it ...returning the value in Colunm C using Colunm A as Ref
I am trying to format a spread sheet and need to return the value of colunm
C but the problem is Colunm A is blank as it runs in sequence (see below)
It looks like the following
A B C
1, P100AA Info 300
2, P100AB Info 150
3, "Blank" "" 100
4, "Blank" "" 50
5, P100AC Info 300
How do i return the last figure for the item in Row 2 (colunm A) i.e. 50?
If i do a logical test how do ...Expand and Shrink Rows and columns to hide information.
I have a spreadsheet with rows 1 & 2 and columns numbered 1 & 2. In column 1
I have the ability to click a plus sign (+) to expand information in hidden
rows. Along the top I also have the ability to click a plus (+) sign in
column 1 to expand hidden columns. It appears that column 2 denotes those
rows to be hidden by a "."
Ok how do I set this up?
Data>Group and Outline
Gord Dibben MS Excel MVP
On Tue, 5 Jan 2010 07:29:02 -0800, jf1847 <firstname.lastname@example.org>
>I have a spreadsheet with rows 1 & 2 and columns number...Recalculate a field when another field's value changes
I have the following fields I'm working with; they are all on the same Form
which comes from a query.
TotalTuitions is calculated by the sum of (4) other Tuition fields. I have
a check box to trigger the calculation of the MultiClassDisc field which
works off of the following code.
If MultiClass.Value = vbTrue Then
MultiClassDisc.Value = TotalTuitions * 0.1
MultiClassDisc.Value = 0
It works Ok.....when I check the box it calculates MultiClassDisc to be 10%
of the TotalTuitions and when I click the box off it chan...Budget Question- Income disappears after i entered it
I set up the Essential budget by entering my income and expense. When I go
back to check the budget it shows that I have 0$ income for both Actual &
Budgeted. This happend twice within the same month. Any ideas why this
...How can I add values from several worksheets in a line chart seri.
I have an Excel document with 12 (monthly) worksheets. I want to make a line
chart pulling data in one cell (same cell on each sheet) from each of the
twelve sheets. In other words, Jan F2 + Feb F2 + Mar F2, etc.
Can I do that on the chart's Source Data menu in the Series/VALUES field? If
so, what is the proper language/formula. If not, what are my options? Do I
have to make a total page and pull from it?
Also, is it possible to make the Category (X) axis label be the Worksheet
Name (Jan, Feb, Mar)
Lastly, is there a place that gives sample formulas for someone trying to
brush...Cells that "appear" empty plot as zero. Do not want cells to plot.
I read thru some of the posts about plotting empty cells
and can't seem to find the right answer to my problem. I
have a bar chart (Jan thru Dec) which I currently show
data running through June. All cells contain the same
formula (therefore none are "really" empty). My formula
uses the " " to make the cells for July thru Dec "appear"
empty, therefore the graph is plotting these cells as
zero. I know that I could use 'NA()' and force a #NA
error, but I don't want that error message to appear in my
cells. Is there a way to not get the ...Sorting 7 columns
How do I sort more then 4 columns? I've set up a worksheet that sort
columns in a base 1000 number system. I want to use the sheet t
organize Ideas that I get for creative writing, so to compile the Idea
in chronological order. I need to sort more then 4 columns to make it
usable worksheet. How Do I sort more then 4 columns, or get around th
+-----------------------------...When refreshed, some Pivot Table columns retain formatting, some d
I have not had trouble with retaining column widths and column heading
formats (font size, word wrap, alignment) in Pivot Tabl. When refreshed,
they retain formatting.
Here's the weird part, when I add or insert a new data column into the Pivot
Table by checking from field list, that new data column does not retain
column heading formats (font size, word wrap and alignment settign) like the
original columns do. It does however preserver its column width and number
formatting. Why is that?
How do I tell Excel to preserve column heading formats for newly added ones?
I...Hiding a column of hyperlinks behind a column of numbers
I have two adjacent columns in Excel 2007: one with numbers, one with
hyperlinks. I wish to "place" the column of hyperlinks behind the column of
numbers, so if one clicks on the "number" the hyperlink will open. I know how
to do this to each individual cell one at a time, but the columns have a
couple thousand numbers and hyperlinks. Is there a way I can combine or merge
the columns all at once to do what I want? Thank you for any response.
Are your hyperlinks the result of the
function or are they Inserted hyperlinks??
Gary''s Stud...Copying values between worksheets
This seems a simple problem, but I can't find the answer: I am setting
up multiple worksheets (one per month); the closing balance on Jan
becomes the opening balance on Feb; if, for example, the opening
balance is in cell F38 and the closing one in F42, then in cell F38 in
Feb I enter =, then click on F42 in the Jan sheet. However copying
across to the other sheets results in all sheets referencing Jan!F42.
I understand why sheets can't be treated in a relative way, but is
there an easy way of copying this without resorting to VBA?
-----------------...include string or text value in cell reference
does anyone know how/if i can include a cell value within a path in a
function/formula so i can update it by simply changing cell value,
SUM($A$1, '[E:\folder\stats June.xls]'!sheet1$A$1)
so that the 'June' part can change according to a cell value, say F1.
So if I change F1 from "June" to "July" then the SUM formula would
change to show ....'\stats July' .
many thanks in advance, Nelly
Use the INDIRECT() function
> does anyone know how/if i can include a ce...Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count
the number of cells in this one column that contains dates. How can I do
If the only cells that are in that column that are numeric (like dates), you can
(Dates are just numbers formatted nicely in excel.)
> Column J contains dates in some cells, and text in others. I need to count
> the number of cells in this one column that contains dates. How can I do
...datagridview Combobox column
I need help with the following, I have a grid that i have put on there 2
textbox's and a combobox column which works ok.
When i then add the data by using the following code below, it works, my
problem is with the combobox "YesNo" it put the complete contents of the
datatable into the combobox for each row. My question is how can i limit
the drop down to 1 yes and 1 no. But at the same time display the correct
value according to the datatable?
DataGridView1.Columns(0).Visible = False
DataGridView1.Columns(1).Visibl...Find 2 values closest that are higher
Got an equation nightmare on my hands.
So i have 3 columns of data
A: State Abbreviate
B: Benchmark Number
C: New Number
I am trying to get an equation that in column D will return the 2
closest values in column B for a value in a cell in Column C.
So if column C1 is 100
And column D1:D5 is 101, 115, 98, 5,92
It will find values 101 and 115 and put them in column D. The trick
being was that i was hoping it would also return the State Abbreviate
for this record. So if Fl is 101 it would return Fl: 101
Any ideas on how to do this???
> Got an equation nightm...Add Value of Cells by Color
How do I sum the values of cells with a specific color??
> How do I sum the values of cells with a specific color??
I have a master Excel worksheet, which has six columns:- C:D:E:F:G:H and
there are approximately one thousand rows. On a daily basis I import data
into Column A. of this master worksheet. The data consists of lots of
different 10 digit numbers, so I have a different 10 digit number in each row
of Column A., an example of which follows:-
The amount of rows that I import also varies daily, it can be as low as five
rows of 10 digit numbers imported, or approximately 999 rows of 10 digit
The rest of th...Setting up Simple Budget in money 2003 = Impossible!
Here's my criteria...up until this point I've been doing this by hand in a
basic ledger book, and the only part that really sucks is the Deposit Entry.
I was hoping MONEY 2003 would be the solution to all my problems by
AUTOMATING this process, but alas....
I must be a financial retard because I just can't get this thing to work the
way I'd like it to.
My Scenario is quite simple...all I want money to do is track a simple
budget for me.
Example: I have four monthly bills:
ON average, my MONTHLY expenses for these categories are:
Telephon...When I click on a column, that column and one to its right is sele
For some reason, on my first spreadsheet, when I click on a column, the one
to its right is also selected. It doesn;t happen on all the columns. What did
I do to cause that? Thank you for any assistance.
I bet you merged some cells.
Select those two columns and then
Rightclick on any of the selected cells
Format cells|Alignment tab
Look for merged cells.
You'll see that it's a mixture of merged cells (either a green square or a grey
checkbox -- depends on the version).
You can remove the merged cells by clearing that checkbox.
But you may want to look to see if you removed som...From Row to Column?
Hello all, I have a large spreadsheet of data, unfortunately when I set it
up originally I put the data into rows (I.E. left to right) I'm trying to
correct that now, Is there any way I can highlight a row of data and move it
into a colum, in the same order it is in the rows?
Yep, select all the cells in your row, and copy then select the first cell
in your destination column and select "Paste Special" then transpose in the
Paste Special dialog and ... job done!!
"confused man" <email@example.com> wrote in message