Sum in another cell with reference to another
I need to add cells which only have reference to another eg:
PNS Kilbirnie 2.0
NW Newtown 1.0
NW Island Bay 1.5
NW Metro 1.0
PNS Kilbirnie 1.0
eg: Need to add only the "PNS Kilbirnie" totals but these are also in other
columns similar to above. At the moment these two columns are A & B, then I
need to also add the Kilbirnie from column C & D as per above
One way to get this delivered in 10 secs. Do a 5 sec copy/paste stack up of
the data in cols C & D below that in col A & B, then create a pivot on the
combined data, placing the ...Outlook 2000 getting day of week with today date
A while ago my Outlook displayed the day of week. I cant find a
reference to this in the manuals, help, MS pages, and I'm not even going
to try Google
Check your Long Date format in Control Panel | Regional and Language Options
(the actual Control Panel command for this may differ depending on your
version of Windows).
MVP - Outlook
*** Replies sent to my e-mail address will probably not be answered --
please reply only to the newsgroup to preserve the message thread. ***
Steve Lenaghan wrote:
> A while ago m...convert 13.11.2009 general to 13/11/2009 date, how to
Office 2003, Windows XP Pro SP3
I have a column of dates in the following form 13.11.2009 (i.e. dd/mm/yyyy)
where the cells' format are 'General'. I wanted to sort in ascending date
order from 01/01/2009 to 31/12/2009. I tried formatting the cells to date and
use the sort but what that did was to sort in this manner: 01.01.2009,
01.02.2009 - - - 01.12.2009 then 02.01.2009, 02.02.2009 i.e. by months first
then by dates. Even when I have chosen dd/mm/yyyy formatting, not only the
content won't change the sort still perform as I have just described. I h...Concatenate including a date so that the date appears as a date
When I try to join the contents of several cells, one of which is a date and
time obtained from =now(), the date and time part appears as a number. How
can I make it appear as a date and time? I have tried changing the cell
formats - probably there is a combination that works but I haven't found it.
I also tried copying the original date cell to another cell and changing the
latter cell's format and concatenating using that latter cell, but still no
Use the TEXT function. For example, =TEXT(NOW(),"mmmm d, yyyy") would give
January 6, 2010
"dd...Copy empty cells from one workbook to another
Sorry for posting 2 times. Date & time in my computer were not correctly
In addition to what I've written before, here is the code I wrote:
My problem that empty cells are not copied as empty and
because of this I get an error messages in other worksheets (like #VALUE).
How Can I solve this problem ?
...Excel merged cell
I have merged two cells and want it to expand (get bigger) as more text is
typed or added.
I'm able to do this with just a single cell (row autofit, wrap text), but
when I merge two or more cells, these options no longer work.
I don't think you can and-in my opinion-it's just another reason to stay away from merged
"Joanne (JAS)" <Joanne (JAS)@discussions.microsoft.com> wrote in message
> I have merged tw...Create named range of cells with values in row
I need help please to create a Named Range of all the cells with values
therein starting at cell B1 and continuing through all contigunious cells
with values to the right in the same row only. If there be a broken column,
then all cells to the right of that break need not be included.
Vaya con Dios,
This might be more than you need, but it assigns the name and then tests to
make sure that it took.
Dim lc As Long, rng As Range
lc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ActiveSheet.Range("B1&qu...Using Validation to force entry into cells?
Hope my topic isn't too confusing.
What I've done is create a drop-down validation list for a row in a
worksheet. What I would like to do is force the entry of the corresponding
value into a particular cell (in that row).
For instance, If a user chooses "61" from the drop down box, they should
only be allowed to type an amount in one of 2 particular cells in the next 2
columns. This is all related to banking. So say someone chooses "61", I
want the dollar amount to force them to input in the "Credit" column and skip
right over the "Debit&qu...Click events on Ecel Cells
i would like to click on a cell in a worksheet and have the string value "X"
either inserted or removed from that cell upon clicking. Is this possible?
A selection change would be the closest match for that, I would suggest
using a double-click event.
More information in
To install right click on the sheet tab, View Code, plop the following code in.
This is setup to toggle value in Column 1 on a double-click, but you can modify code.
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Canc...changing a month by entering a number in a cell
I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly.
Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get...Centering data over a grop of cells
I have 6 columns and want to give the group a name by centering data over
them. How do I do that
format>cells>alignment and center across selection
"charles" <firstname.lastname@example.org> wrote in message
>I have 6 columns and want to give the group a name by centering data over
>them. How do I do that
> charles j
Use "Center across selection", it's on the Alignment tab of Format Cells
> I have 6 columns and want t...international absulute cell reference
To make an absulute cell reference you use the dollar sign. How would you
make an absulute cell reference on a keyboard with no dollar sign? Example:
russian keyboard, japanese keyboard, ect...
AFAIK it is always the $ sign in all language versions (not sure though
about Japanese versions). Maybe ask this is the Japanese Excel newsgroup
> To make an absulute cell reference you use the dollar sign. How
> would you make an absulute cell reference on a keyboard with no
> dollar sign? Example: russian keyboard, japanes...transform text dates into Excel dates
I currently have a spreadsheet with hundreds of entries wheremany of the date entries were done textually ("21 Nov. 2002"
But I need them to be date format Excel won't let me change the format/ won't recognize the text entries as a valid date entry. How can I convert these entries so the Excel will permit me to change the format and recognize the entries as valid dates?
Try doing data - text to columns to separate the day, month and year. Then concatenate the values in order with a "/" in between so that it'll be a valid entry.
Daniel...Semi-Monthly Pay Dates
Is there a way to manually plug in all of my future paydates from now until
My company pays twice a month (the 15th & the last day of the month). With
the stipulation that if the 15th or last day of the month falls on a weekend
or holiday, payday will be on the preceding business day. Right now I have
my wages & salary frequency set at twice a month. I wouldnt mind going
through every month from now until next yr and selecting the pay days
manually. What would be the best way to go about this?
I know, I bet there isnt another company out there that pays like
this...Don&...Hightlight date range
I have a worksheet that has dates in cells B2 through B171. I want to
highlight cells from a date that is in cell F4 + 180 days. I want to be able
to change the date in F4 and the dates in B2:B171 to highlight accordingly.
Please help. Thanks
Select the range B2:B171 (with B2 active),
apply CF using Formula Is:
Fine tune the ">=" bit to suit, if necessary ..
Voila? Click the YES button below
Downloads:25,000 Files:300 Subscribers:70
"Derek" wro...how to automatically update a cell value?
I have a data table in Excel as follows:
I would like to have a separate cell (i.e. Cell X) that will
automatically update with the latest value from the data column. In the
above example, the Cell X value would be 4%, but if I add additional
data to the column, such as 9% in April, the Cell X value would show 9%.
How do I create a formula for this?
One way, in cell x put
where column B holds the percentage, format x as percentage
(No private emails please, for e...How to display date of last edit of the database on a report
Is there a way to display the date of the last edit of the database on a
Thanks in advance for your help.
What do you mean by "last edit of the database"? Do you mean the last time a
value in a field in any table was changed? Do you mean the last time any
structural change was made to forms, reports, vba modules,or data structure?
In either case there is nothing stored that will tell you, unless you store
the information in some manner. It can be done, but the utility of doing so
(in most cases) is far less valuable than the effort to do so.
...CRM 4.0 customize Activity Created Date
cannot select date and time, format is greyed out and displays date
only. How can I get it to take both? I've even tried exporting to
XML, and re-importing but to no success.
Can you please elaborate on you questions, are you trying to set value
to the Created On Date on the activity? If you, you can't set the
created on time, this is a system field set by the CRM system.
Darren Liu$B!$(BMicrosoft CRM MVP
Crowe Chizek and Company
On May 22, 10:15 am, Magic23 <carlroyk...@gmail.com> wrote:
> cannot select date and time, format is greyed out ...Excluding a cell from an average
I'm working on creating a grade book, but I've encountered a problem
with excluding an assignment for individual students when averaging the
I have several different worksheets for different subjects. On each
sheet the students are listed on the left (A2:A26), the
assignments/quizzes are at the top (B1:Z1), and the value of each
assignment is at the bottom (B27:Z27).
I also have a "Results" page with the average mark (percent) for each
student, for each subject. The formula I've used on the "Results" page
is as follows (as an example):
=SUM(She...If function using cell format as the condition
I would like to create an "If" logical function formula, where the "true"
condition is based on the format of the cell.
For instance, if cell b3 is formatting with a fill color, then add that
number; if not, then add zero.
Can anyone help?
That will need VBA, unless the fill colour is derived from conditional
formatting (in which case you could impose an equivalent condition).
"Janie" <Janie@discussions.microsoft.com> wrote in message
>I would like to create...Set the text color of particular cell of list control
I want to set the background color of particular cell in a multicolumn list
control. How can this be achieved.
Thanks in Advance,
Check out codeguru/codeproject. One way would be to handle
>I want to set the background color of particular cell in a multicolumn list
>control. How can this be achieved.
Have a look at using custom draw (NM_CUSTOMDRAW).
...Date format in Lotus 1-2-3 files transferred to Excel 2000 (XP)
Cannot enter any usable dates in any Lotus 1-2-3 V5 file
imported into Excell. Text, formulas, and values seem OK,
but I have not bothered to check carefully since without
usable dates these files are useless.
Any help appreciated.
If Transition Formula Entry is selected under Tools, Options, Transition
uncheck it. "Transition" is a euphemism for "1-2-3 version 2.x".
"Willystu" <email@example.com> wrote in message
| Cannot enter any usable dates in any Lotus 1-2-3...Showing a cell that corosponds with the highest number
I am making a results table for pool games.
I've got it to calculate the points (3 for a win) and (take 1 for
loss) but I want it to show the players name that has the most points
It's layed out like this
I would want it to show Player2 in the cell.
I have used sumif to find all the winning games, then i've times tha
cell by 3 to get the points, ive also used sumif to find all the usin
games, and then i've taken it away. This shows the number in the point
box. I need to find the row with the biggest numb...Last letter in Excel cell is truncated when printed.
Excel 2000, column and height are set so all lines in a cell are viewable.
Everything looks fine in Excel and print preview. But when I print, the very
last letter in every cell is chopped in half. (e.g. so an 'n' looks like an
'r'. I can not find a difference between other text cells that are working
Are you in the default font and fontsize that you set up in
tools, options, general. though it probably shouldn't make a
difference. What you see on the screen is based on your printer
drivers. I guess all you can do is adjust to cell border. You mi...Select dates in range
Good day to all,
I've a table with some records of events for subjects and on certain dates,
now I need to get a query that returns me the Subject, Subject creation date,
3 months after creation date, Value at 3 months, 6 months after creation
date, Value at 6 months:
This is for example:
ID Name Born
1 Jon 6/6/95
2 Betty 8/12/95
3 Rudolf 15/01/96
Date subjectID Wheight
6/7/95 1 10
7/8/95 1 18
5/9/95 1 25