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...Switching tabs
I have my preferences set to bring me to the "Customer Service" tab on the
bottom of the screen. If I click on the sales tab, I can't get back into the
customer service tab. No error comes up, and I can still work in the sales
tab. On the bottom left corner, I have a yellow exclamation point. If I
double click on it I get:
Error: Permission denied
Also, and this may or may not be related, when I try to import contacts, I
get an active x error.
I have already applied Microsoft CRM v1.2 Update: 840934. My PC is XP Pro SP2
This sounds like ...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...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...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)...Movement of cursor within a drop down list
When we type a letter in the cell, the cursor should move to the word
starting with that letter in the drop down list. How this can be done?
If you are using a Data Valadation drop down try this.
Sort your validation list and at the beginning of the A's enter a single A,
do the same for the beginning of the B's and all the way through to the Z's.
Click in the cell that has the drop down validation and type in the letter
of the list you desire... say you type in an O. DO NOT hit enter... leave
the cell in the edit mode (still selected) and now hit your down arrow.
Should take...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...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.email@example.com...
> 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.
...how to make outlook go to a different folder (then inbox) when opened or tabbed
Does any one know how to make outlook point to some personal folder
when it opens and when coming back from the calendar?
Tools->Options->Other->Advanced->When starting Outlook...
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. Due to
the (insert latest virus name here) virus, all mail sent to my personal
account will be deleted without reading.
After furious head scratching, firstname.lastname@example.org asked:
| Does any one know how to make outlook point to some personal folder
| when it opens and when coming back from...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 ...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...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...Splitting up text in a cell
I have a worksheet extracted from another program with financial data
in it. One of the columns contains information about what money has
been spent on. This consists of a category and a subcategory. For
example one cell might read "Bills : Electricity" or "Going out :
Restaurant". The category and the subcategory are always separated by
one space, a colon, then another space. What I'd like to do it
separate the category from the subcategory by using a formula that can
extract the text either before or after the space-colon-space bit.
Having looked at some of Excel'...excel 2000
I recently read a posting about being able to control the tab key t
jump to any specified cell by using a named range... this is great! I
it possible to do the same thing using text boxes
Message posted from http://www.ExcelForum.com
...formatting a cell
is there any way to format a cell but having it depend on
the value or entry in another cell?
Sounds like conditional formatting. For instance in B1, goto conditional
formatting, and use Condition1 of FormulaIs, and a formula of say =A1=27,
then format (via the format button) as required. Just use an appropriate
"frank" <email@example.com> wrote in message
> is there any way to format a cell but having it depend on
> the value or entry in another cell?