Sum a column within a named range
Hi All. If I have a named range in a worksheet, how do I sum a single
column of a multi column range? I know how to use vba to do this but what
would a logical excel formula be? I think I am on the right track.
I keep trying things like =sum(_NamedRange(B:B)) but that does not work. Is
this even possible? The desired result would have been to sum the 2nd column
of numbers in the _NameRange. Any help is appreciated. Thanks everyone.
Chris Kettenbach wrote:
> Hi All. If I have a named range in a worksheet, how do I sum a single
> column of a multi column range? I know how t...Align & Compare row with column
A1: USB 2.0, B1: USB, C1: Type A
A2: FW, B2: FW400
A3: Serial, B3: DB-9, C3: RS-232
I have another list.
G1: USB 2.0
What I want is to sort A1:C3 row wise, and it will compare with column
F,G & H.
if column F matched with row1 then a particular value should be
In other words no matter what data lies in A1:A4....its order should
be like F1:H1
Thanks in advance.
If I understand you correctly, HLOOKUP is the way to go.
In A1 type =F1 and copy this through to column C
In A2 type =IF(HLOOK...Column Wrap in Excel
I have a list of existing values on a spreadsheet. names and apartment numbers in 4 columns total. I would like to be able to insert a new row into the first two columns and have excel wrap the bottom row of values up to the 3 and 4 columns of values. This operation is comon in Word, 2 columns and when the text has reached the bottom the text wraps to the top of the document
Can this be done in excel? I'm pretty seasoned with Excel but the person asking me of this is very amature.
Thanks in advanc
Bryan M, MN
Word does a nice job when you use Format|columns. It'll wrap thin...show day in column B for Date in column A
I want the Excel to show the day of the week in column B for whatever
date is entered in column A.
NYBoy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8360
View this thread: http://www.excelforum.com/showthread.php?threadid=530201
Here are 2 options:
For a date in A1
Custom Format B1 to only show the day
DDD shows the 3 letter day (Mon...Remove columns with all zeros
How can I remove columns that contain all zeros? Is there a macro? I craeted
a sumif formula to flag columns subtotaling zero but I need a method to
remove these columns. Thanks
Dim nLastColumn As Long
Set r = ActiveSheet.UsedRange
nLastColumn = r.Columns.Count + r.Column - 1
For i = nLastColumn To 1 Step -1
If Application.WorksheetFunction.Sum(Columns(i)) = 0 Then
Gary''s Student - gsnu201003
> How can I remove columns that contain all zer...Help required for copy-pasting columns.
i want to prevent users from pasting more than one column at a time in
So I put in the following code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If InStr(1, Target.Address, ":", vbBinaryCompare) Then
If Sheet1.Range(Target.Address).Columns.Count > 1 Then
MsgBox "please copy paste one column at at time. our
developers are getting tired of this crap"
However the trouble with this is that it doesn't allow me to select
multiple columns from the she...Flowing columns
I'm wanting to do a booklet that has parallel versions of a text. I want
to import one text document into the left-hand column and another into
the right. Each document is multiple pages long.
I'm not finding how to do this an any documentation.
Do you mean you want to overflow of text to wrap into the right column or do
you want to copy (or move) the text from one column into the other? Your use
of the word "import" is what's confusing me.
MVP Microsoft [Publisher]
"Keith" <email@example.com> wrote in message
news:ShIIg.2130$o4...Set the serial number every 15th column
I would like to set the serial number every 15th column.
A B C ..................
Is there any formula for A column?
Thanks in advance
In A1 and then copy down
Copy Col A and paste special as values.
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
It'...How do I write in both columns when I format my word document?
I am typing a document in word and I need two columns formatted with text in
both. I am only able to write in the left column and cannot type in right
column. Please help.
With "newspaper" type columns, you need to insert a column break to move
from the first to the second column. (Page Layout tab>Breaks in Page Setup
section of Ribbon>Column)
If you want to switch backwards and forwards, maybe you should be using a
two column table instead. If you go this route, insert new rows every now
and then as exceptionally long rows (spanning pages) can be a bit of an..."TO" column missing from inbox
I am running windows XP and Outlook. I must have clicked
something by accident because when my messages are
listed, the 'to' column is gone and I can only see the
subject column and time. Have tried everything but can't
figure out how to get this back. Thanks for the help
What version of Outlook?
> I am running windows XP and Outlook. I must have clicked
> something by accident because when my messages are
> listed, the 'to' column is gone and I can only see the
> subject column and time. Have tried everything but can't
> figure ou...Determine Which Columns Are Hidden in VBA
I have a spreadsheet which will be used by individuals to update an
Access database. The columns are locked, but I do allow them to hide
columns to make the data more customized. Periodically they will
refresh their data from the database. In order to avoid forcing them
to reformat to their preference (and thus encourage them to refresh
more frequently) I would like to determine which columns they have
hidden so I can unhide, update/append the new data, then hide the
columns again. The same information would be advantageous, though
less so for the autofilter properties. I have tried in va...Drag a column header here to group by that colum
When we are in outlook and we are on the inbox view we
notice a greyed out field above the pane where the email
messages in our inbox reside and the pane has the
"Drag a column header here to group by that column".
How can we get rid of this field?
Thanking you in advance.
Right click the column headers and deselect Group By Box
Tips of the month:
-Create an Office XP CD slipstreamed with Service Pack 3
"Jack Kuzuian" <firstname.lastname@example.org> wrote in mes...Altered Column Width
In use one column of my spreadsheet to import a text file. Each day I run a
refresh on the data.
However, after refreshing this data, I notice that the width of the column
into which data is received gets altered.
* How can I prevent the width of the single column from being changed as
I import data from a text file ?
Refresh with a macro that has one line to reset the column width
<JoJo> wrote in message news:uPS8aqkjHHA.4248@TK2MSFTNGP06.phx.gbl...
> Folks...Hiding and unhiding columns "toolbar"
I have a worksheet with a "toolbar" that allows me to
toggle between hiding and unhiding pre-set columns. I do
not have access to the author so I am seeking help. Can
anyone tell me how to create and delete this bar?
The bar is located at the top of the sheet, just below
the customizable toolbars. It has two "checkboxes" on the
left, one containing the number one, the other the number
2. By clicking on the 1 or 2 I can toggle between hiding
all the hidden columns or unhiding them. But I cannot
delete or modify the bar, which I need to do.
Can anyone help?
You ca...Possible to select a sample rows based on a column value?
I want to write a query to select columns from 3 tables (T1=120 million
rows, t2=200 M and T3 = 9.5 M) grouped by a column in table T3.
select count(*), T3.C1
from T1 <..>
Join T2 <..>
Join T3 <..>
group by T3.C1
The question is it possible for me to select top 10 rows or a sample of rows
from each group or rows in the above group by query. Should I try using the
Over() clause with partition?
Is is possible?
NetNewbie (NetNewbie@discussions.microsoft.com) writes:
> I want to write a query to select columns from 3 tables (T1=120 mill...Font for column letters and row numbers
Without notice, Excel now displays my column letters and
row numbers in Arial Italic font. But worse is the fact
that every workbook I open now has most cells displayed in
Arial Italic font. If I select an entire worksheet and
press the Italic button in the formatting toolbar, nothing
happens. If I insert a new worksheet, data entry default
ti Arial Italic. The default font on the General tab in
the Tools Options dialog box indicates that Arial is the
default font. The list of fonts that appears when you
click the Font button on the Formatting toolbar includes
the word Arial, but it&...Outlook formatting of columns to wrap text in Task view
I have used multi-line layout to view tasks but it does
not show in print preview.
Is it possible to wrap text if the number of characters
exceed the column width? If so, can we print it in the
Would appreciate your reply.
...How can I combine a stacked column chart and line chart?
I want to combine a standard stacked column chart with a line that, for
example, presents the average value of stacks in each column. This is only
possible by drawing manually a line in the stacked column chart, or is there
a better trick?
Create your stacked column chart using all the data.
Select the series that contains the average data. Change the series chart
type to Line.
Andy Pope, Microsoft MVP - Excel
"Huib" <Huib@discussions.microsoft.com> wrote in message
news:73A53D69-E79D-42B6-BC22-63B1B3912DB0@microsoft.com......Excel +won't +merge cells in a column. Is my formatting blocking .
Blue Heeler1 to Outlook User Improvement community & wizards
Rather cryptic message in the body? More details on the cells to be merged
and messages returned if any.
"Blue Heeler1" wrote:
> Blue Heeler1 to Outlook User Improvement community & wizards
Ditto Blue Heeler's prob. I want to merge Col. A and Column B. (Last name =
Col. A; First name = col. B) I can select both, but where to I find a merge
command? The "merge and center" command is not lit up so that won't work.
"Blue Heeler...How do I import external data populate rows instead of columns.
I am trying to find a workaround to a previous problem that involves a column
limitation of 256. If I create a delimited text or csv file > 256 fields is
there any way I can import the values and populate the rows instead of the
columns? That way I could bring in my data.
...Calculating column and row totals
Can some tell me if there is a way to set excel to automatically calculate
column or row totals in a financial worksheet?
Provided the formula isn't in the column or row concerned
=SUM(A:A) will total all of column A
=SUM(1:1) will total the whole of row 1
If you want the formula at the top of column A in cell A1, you would
Adjust references for other rows / columns
"Richard Mahan" <email@example.com> wrote in message
> Can some tell me ...Multiple line in the column of List Control
I use VC++ 6.0 MFC CListCtrl. And, some data displayed in the control
contains multiple lines. By default, the control displays the multiple-line
text in a single line. In the multiple-line text, it uses "\r\n" (CR-LF).
How can I set the column of CListCtrl to display the column text in multiple
line like the data cell in Excel?
...Column Header 03-22-10
Is there a way format the column headers in the worksheet...ie Make the A or
B on the column header row bigger or smaller...For some reason I opened up a
worksheet and all the column header letters and row header numbers are bigger
than normal and I want to get them back to their standard size?
I'm not sure but it could be something as simple as View - Zoom -reset to 100%
Please hit "Yes" if this post was helpful.
> Is there a way format the column headers in the worksheet...ie Make the A or
> ...Moving date from 1 column to another based on a value and changing the sing.
I was wondering if anyone could help me. I have seen a few examples
that are fairly close to what I'm looking for.
I'm wanting to look at a range of data in a column and find all the
cells that have a value not equal to 0, then move it one column to the
left and change the sign to negative.
Below is the columns I have setup:
Column A B C D
Cash 15,343.00 0 147.34 0
Petty Cash 0 150.00 300.00 0
name 0 20,000.00 20,000.00 0
w...Fixed Column Width
I need to create, for want of a better word, a separator column in my
XLS. This column is supposed to be a fixed width, .3, and the user
shouldn't be able to make it bigger.
Any ideas on how I can get this to work?
Pick any column (or insert a column: Insert/Columns), then use
Format/Column/Width and make it .3. To prevent the user from changing the
width, you would need to protect the worksheet, using a password.
"Mark Keogh" <Mark.Keogh.firstname.lastname@example.org> wrote in message