Are there any way to add text and a function in the same
cell? For example Hello =sum(XX:XX)
Try something like
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"danne" <firstname.lastname@example.org> wrote in message
> Are there any way to add text and a function in the same
> cell? For example Hello =sum(XX:XX)
Sure, for example: ="Hello = "&SUM(A1:B1)
For more info, try MVP Debra Dalgleish's &...Macro or Formula needed to search data in cells
I am looking for a way to create a formula/macro to do the following:
My worksheet setup:
01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27
02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32
03 04 05 06 07 08 09 10 11 12
04 05 06 07 08 09 10 11 12 13
Each cell contains a 10 number sequence. The range A1:A30000 is my
randomly generated sequence. And C1:5000 is my database of archived
If I wish to check if the combination in cell A1 is anywhere in the
range C1:5000. I use the formula.
In Receivables Management, Customer Maintenance, there is a popup for the
internet address for each customer's Address ID. In that popup is a field for
the Email Address which is contained inside the Internet Information box. Can
some please tell me where this email address is stored in the SQL database?
Hope this helps, rc.
"Bob B" wrote:
> In Receivables Management, Customer Maintenance, there is a popup for the
> internet address for each customer's Address ID. In that popup is a field for
> the Email...Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of
them. Based on the comparisons I'll need to update the original cell
with one of those adjoining cell values. After I finish with one
column then I need to repeat the procedure on the column to the LEFT
of the original column.
I know IF, THEN and ELSE statesments but I don't know VB for Microsoft
Range could be all 65,000+ rows on a workseet
Start on ColumnJ, Row2
If ColumnJ, Row2 is Null _
If ColumnK, Row 2 is Not Null _
If ColumnJ, Row 1 is Not Null _
ColumnJ, Row2 Value is ...If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today!
I've found the below formula in one of the posts here, but my brain has
completely failed on me and I'm not sure how to adapt it to my requirements.
I have a value which is entered by the user after a prompt, that populates
in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value
I have another worksheet, 'Data', which has a column populated with dates
(say column B). I'd like a macro that looks in column B on the data sheet,
and clears out any dates which are gr...Default Calendar Events
I have an Exchange 2003 server and would like to set default calendar events
for all users. Is there a way to add these events to the server so that they
show up on all calendars, even new users set up through the year
...changing default language
I have the French version of office x, but my French is not as good as
my English. I have a difficult time reading some of the options such
as file, edit, view, etc. I was hoping there was some way to change
the language to English. I don't know if this would mean changing some
setting or uninstalling and reinstalling. Please help me. Thanks.
In article email@example.com, Brian Blanchette
at firstname.lastname@example.org wrote on 10/20/04 16:26:
> I have the French version of office x, but my French is not as good as
> my English. I have a difficult time readi...Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that
is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I
want those cells to remain blank.
That's just the way it is ..
Instead of : =Sheet2!A1
you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1)
Alternatively, we could suppress the display of extraneous zeros
in the entire sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot&g...Set default application to open some kind of file
How can i set the system to open all .doc files with word?:
Get Info (Command-I) on a .doc file. Set the default to open with Microsoft
Word. Now all .doc files should open into Word when double-clicked.
"Rodolfo Fontes" <email@example.com> wrote in message
> Hi group,
> How can i set the system to open all .doc files with word?:
> Rodolfo Fontes
...Subtotal in a pivot table
I have a question on calculating something in a pivot table. What I am
trying to get is a % of the subtotal break in my table. I have a calculated
field but cannot figure out how to get it to calc. the % off the shop total
not the Total for the table.
See the example below. For Shop 246-Database I want the Sum of % of shop
total to be based off the Database DeliveryAmt (10575) / Shop 246 total
DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%. Retail
should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) =
39%, not the pivot table total (185852)...Multiple Cell Formats
Is it possible for a column to have more than one format, but for those
formats to be similar.
I would like to have a multi-format date column. The format can either be
Month-Year (Jan-99) or Year only (1999). (I would even like to have
something like Jan/Feb-99, but I can live without that.)
Any help would be appreciated.
With true Excel dates in column A e.g. 01 Feb 07, in column B
=A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07
=A1 Format>Cells>Number>Custom> yyyy will give 2007
or =TEXT(A1,"yy...Best way to add a row to a table with calculations?
Suppose I have a worksheet like this:
2 Date Days
3 5/25/08 35
4 4/20/08 45
5 3/06/08 46
6 1/20/08 41
The value in the Date column is calculated (=A3-A4).
I would like to add a row between rows 2 and 3 and have it acquire all
of the properties of row 3.
The best procedure I could come up with is:
1. Right click on row 3, select Insert. This inserts a row between 2
and 3, but it acquires the properties of row 2.
2. Select row 4 (old row 3) and press Ctrl-C (copy).
3. Select row 3 (new row) and press Ctrl-V (paste).
This seems to work. Is there a better wa...Import MS Word 2003 Table to Excel
I have a word document that has many tables. I need to take each table and
put them into one cell in excel if possible. I have several test documents
that I was able to transfer the information from Word to Excel in a row per
record. I need to keep the tables with their respective row(record). Any
help is greatly appreciated.
Looking for some macros or add-ins to assist with Amortization tables.
Any help would be appreciated
Message posted from http://www.ExcelForum.com
> Looking for some macros or add-ins to assist with Amortization tables.
One way to proceed is to start with an existing template. For example, you
and search the "Templates" section for "amort".
Here's one that may be useful to you
MVP - Excel
Excel Blog ...Pivot Table Query #5
I use Pivot Tables a lot in my stats and although I can
normally work with them quite well, theres a reoccuring
issue that I can't seem to resolve. I have one
particular workbook that has lots of different worksheets
and one at the end, with a variety of pivot tables on
it. At the end of each month, I update the data on the
worksheets, then on each pivot table, I use the wizard
option to attempt to go back and re-select the relevant
updated data. Sometimes this is successful, but on other
(random) occasions, it won't let me and gives me the
message 'A PivotTable...Powerpoint 2007 hyperlink menu item grayed out
I was successful in linking 2 shapes to custom shows in my document but can't
repeat my success because my hyperlink menu item is grayed out. I think it
has to do with a compatibility option box that showed up and I must have
clicked the wrong choice, disabling this function? How to fix? Thank you
On 1/20/10 1:38 PM, in article
> I was successful in linking 2 shapes to custom shows in my document but can't
> repeat my success because my hyperlink...Is there an add-in that will lock the cells like later versions of Excel?
I'm using 97 and for 99.9% of everything I do I works fine except I can't
lock cell format so there can only be data entry. I would be nice if I
could do that.
Can you be more specific about what you want and don't want.
"Marc" <mcnr(N_O-S_P_A_M)@mindspring.com> wrote in message news:QThPf.1161$sL2.firstname.lastname@example.org...
> I'm using 97 and for 99.9% of everything I do I works fine except I can't
> lock cell format so there can only be data entry. I would be nice if I
> could do that.
...Number of cells that have same values
Imagine there are two columns of cells, each row of two cells are
corresponding to each other. How to find out the number of rows where the two
corresponding cells have the same value?
Assume your Columns are A and B, and your rows go down to 20, Array enter
this formula (CTRL+SHIFT+ENTER):
Change the column and row references to fit your data.
"Lingyan Hu" wrote:
> Imagine there are two columns of cells, each row of two cells are
> corresponding to each other. How to find out the number of rows where the two
> corres...Subtotal a calculated field in a pivot table
I'm creating a pivot table listing Accounts Receivable transactions by
customer. It includes both payments and invoices in the results.
Unfortunately both payments and invoices are listed as positive
numbers, so I can't just subtotal the "amount" column.
I've created a formula field in the pivot called "calcamt" that
contains an if statement. The if statement examines the transaction
type code and if it indicates a payment, multiplies the amount column
by -1. Otherwise it just returns the amount column as a positive
When I drag the calcamt field int...How do I lock all cells in Excel except 2 which I need unlocked?
By default all cells in excel are protected or locked, select the cells you
want to unlock and go to format, cells, protection and uncheck locked, the
go to tools, protection, and protect sheet, enter a password if you want,
now only the cells that you unlocked can be edited. Be aware that this
protection is very easy to break, the code to do so can be found very easy,
but it will work for most people .
If you only need a few locked I would select them all first, Ctrl A, then
go to format, cells, protection and uncheck locked, then select the cells
you want to lock and go ...How do I change the default directory for Publisher 2003?
My c: drive was short on space after I upgraded to Office 2003 so I moved the
clip art to a separate drive. No linking problems with Word, XP, etc - just
Publisher where I really USE clip art! How can I tell Publisher that my clip
art - and ALL pictures - are on a different drive?
On Sun, 13 Nov 2005 21:25:03 +0000, The Kat wrote
(in article <93A63632-5792-4B19-A2F5-99EE5260C3FB@microsoft.com>):
> My c: drive was short on space after I upgraded to Office 2003 so I moved the
> clip art to a separate drive. No linking problems with Word, XP, etc - just
> Publisher where ...Data Tables
Is there any way to choose only part of my data to appear in a data table? I
have a line and bar chart on two axis and would like the bar data in a data
table and the line data with data labels.
Could you remove the data table from the chart, and make your own table in
the cells near the chart? Compared with the relative inflexibility of a
chart's data table, a worksheet table lets you put any data into it, format
it however you want, etc.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
...How to count the occurences of a text string within a cell...
I'm trying to count the number of text-strings, in this case commas,
within a cell.
If I could count 4 commas, I'd know there were (4)+1 objects in the
Thanks in advance. I always research as much as possible before asking
the forum. The forum has ALWAYS come through with the answer!
Message posted from http://www.ExcelForum.com/
or more generic:
&q...Change e-mail default paragraph setting
Trying to change e-mail default setting: from double to single space between
paragraphs. I'm using Word as my e-mail editor. Thanks.
I'd suggest posting your question in the Word newsgroup. This one is for
issues with Publisher.
MVP Microsoft [Publisher]
"wwinkler" <email@example.com> wrote in message
> Trying to change e-mail default setting: from double to single space
> paragraphs. I'm using Word as my e-mail editor. Thanks.
Outgoing ...Double Click Formula Cells
When you double click on a cell and there is a formula in the cell then the
precendents are selected. Is there a way to turn this off or automatically
go back to the cell you were on without any change to the window and its
positioning. What is the VBA for this?
Thanks in advance
Tools>Options>Edit Check "edit directly in cell" and you won't go to
Application.EditDirectlyInCell = True
Gord Dibben Excel MVP
On Thu, 16 Sep 2004 13:34:07 +0100, "JJ" <f@o> wrote:
>When you double click on a cell and there is a formula in the cell the...