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" <email@example.com> 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.
=IF(COU...page numbering #2
I'm working on a landscape Excel document...the document
will become part of a booklet,and I need the page numbers
at the bottom of the page, but in portrait. Any help?
...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...On mouse right click, choice to go to the next/previous page
In MS Publishers, when you click on right bouton, have the choice to go to
the prévious or the next page.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/office/community/en-us/default.mspx?mid=8f3055f6-24c6-478c-9ab9-08ef04ffc050&dg=m...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...Changing Front End Back End Connection Through VBA
In a form in the front-end the location of the backend is mentioned.
(this is the result of a dlookup from a table that holds the locations of
all backends; user chooses which backend to connect to)
How can I define that when the user clicks a connect button, all the tables
in the front end are connected to the back end which is chosen?
Thnx for your help!
On 04.02.2010 12:12, Rishi wrote:
> How can I define that when the user clicks a connect button, all the tables
> in the front end are connected to the back end which is chosen?
http://www.mvps.o...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...Cannot send message thru POP3 connection
My email works fine when I connect using my exchange
server account, but when I out of office and connect my
exchange server using POP3 connection, I can receive
email without any problem but I keep receiving undelivery
note as follows when I try to send mail to account not on
the Global address book. Please help.
Your message did not reach some or all of the intended
Sent: 07/01/2004 10:02 AM
The following recipient(s) could not be reached:
firstname.lastname@example.org on 07/01/2004 9:58 AM
The recipient name is not recognized
The MTS-I...Specific file path in ODBC connection string
Does anybody know if it is possible to specify a specific
file path for a .DBF database file in an ODBC connection
string used in the CDatabase or CRecordset classes? I mean
something that would override the default path for the
ODBC Data Source like
- or -
rec.open (CRecordset::snapshot, "Path=..\\Data\\[MyData]",
You can if you use the Jet engine. Use CDaoRecordset instead of CRecordset
"Doug Dean&...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.
...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...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 ...Connecting internal ide HDD to Vista PC using caddy
I've just tried connecting an old HDD from a XP PC to my Vista PC via a
caddy but it says USB device not recognised. Tried it with another XP
machine and it is recognised. can I get the Vista PC to recognise it?
Also with the various user folders one of them ( I guess it's because it had
a password on the original machine) won't allow access. Is there a way of
accessing these files?
> I've just tried connecting an old HDD from a XP PC to my Vista PC
> via a caddy but it says USB device not recognised. Tried it with
> another XP machine ...help with page breaks
I have adapted a spreadsheet and I want to `freeze` the top 5 rows with the
heading on it, when I do this it actually freezes the top 20 rows, and I
notice there is a black line to the right of this (at the 20 row mark) as if
it is maybe a page break or something similar has previousy been put in..
Anyhow how do I delete all page breaks and/or freeze panes so I can insert
the one I want.
Its ok now got it sorted.
"S" <firstname.lastname@example.org> wrote in message
>I have adapted a spreadsheet and I want to `freeze` ...Page Breaks
I've been following the instructions for adding a page break but the option
to insert a page break doesn't even show unless I first reset page breaks. I
reset, then try to insert and get.....nothing.
What am I doing wrong?
Two methods for making manual page breaks.
1. Select a cell and Insert>Page Break
2. View>Page Break Preview. Drag the dotted blue line to where you wish.
Do neither of these work for you?
Gord Dibben MS Excel MVP
On Sun, 1 Jul 2007 06:04:00 -0700, Deborah <Deborah@discussions.microsoft.com>
>I've been following the ...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:
I am considering Money 2004 Small Business. I cannot tell
if the software will accommodate two businesses. If so,
can reports be generated collectively and separately.
You could use it for two businesses if you keep the information in separate
files. This will not allow you to run reports for both businesses
"M Newsome" <email@example.com> wrote in message
> I am considering Money 2004 Small Business. I cannot tell
> if the software will accommodate two busine...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...Need to merge two credit cards
I lost my credit card, so the bank replaced it with one that has a new
account number. I tried to "merge accounts manually" from the old account to
the new one, but now my annual budget either double counts individual
transactions, or if I close my old card account in Money, all of my transfers
from checking to the credit card show up as an expense, instead of just a
transfer between accounts.
In microsoft.public.money, Steve wrote:
>I lost my credit card, so the bank replaced it with one that has a new
>account number. I tried to "merge accounts manually"...Remote Exchange Server Connectivity
We currently have multiple Exchange 2003 servers in our data center and are
in the process of deploying remote Exchange servers & DC's to remote offices
that are connected back to the main data center via a site to site VPN
tunnel. The problem with this setup is a limitation on our current VPN
server does not allow the remote offices to talk to each other. If one
office wanted to share data with another they would have to place the data
at the data center and then both offices could access it that way. This
does not bode well for the default install of Exchange as mailbox use...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'...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" <firstname.lastname@example.org> wrote in message
> is there any way to format a cell but having it depend on
> the value or entry in another cell?
>-----Original Message-...A cell copied onto one worksheet from another worksheet does display same formatting.
I have 2 worksheets in my workbook. On worksheet "A" i have a number in red font with black background in cell B1. On worksheet "B" the contents of cell A1 is =A!B1. This copies the value located in cell B1 of worksheet "A" but not the color formatting. How can that get copied as well?
A formula has one single job: to return a value into its own cell. No
formats, no copying, no dancing, no fun, only that.
HTH. Best wishes Harald
Followup to newsgroup only please
"Mark" <email@example.com> skrev i melding