Conditional format that higlights differing data on two worksheets
I have a workbook that contains a worksheet for a single week of any
given month and in the sheet I have an individual's time reported for
each day of the week. I have a second workbook that contains an
individual's time reported by each day for the entire month. I would
like to compare the two to determine if there is a mismatch and
highlight those cells.
The logic goes something like this:
(1) I need to match person A in column C of workbook1 to the same name
in column C in workbook2.
(2) I then need to match the date of the month on workbook 1 & 2 for
person A in step #1.
(3)...Excel Formulae #4
I've been trying hard to find a formula which would
enable me (from one cell) to Add several cells and
Subtract the answer from another cell (subtracting items
of expenditure from a starting total). I could manage
this with Lotus - but Excel does not seem to behave the
same. Advice would be much appreciated. Thanks,
Say you wanted to subtract A1, A2, A3, J4 and N5 from L6:
In article <email@example.com>,
"KenS." <firstname.lastname@example.org> wrote:
> I've been trying hard ...Viewing / Printing more than two pages at once
I'm working on a publication, and I'd like to get a proper overview of it
(balance of pictures etc) However, I can only view two pages at a time. Is it
possible to view lots of little pages - say 8-10 at a time? Like you can in
If not, can I print lots of pages on one sheet? In the Print setup I can
only get two pages at a time.
Only in print preview. On the toolbar there is an icon that you can select to
show you multiple pages.
Mary Sauer MSFT MVP
"KMcA" <KMcA@discus...ODBC Failure In JOIN Query ??? (BUT....)
I have a query that is simply a combination of 3 other queries (joined by 1
common field in each).
Each of the 3 "sub queries" will execute fine, Individually....
But....that final query which simply combines them all will display an ODBC
I'd always assumed that if each individual query ran okay....the query which
combines them should not get such a data-access related error (since the core
data is already being successfully accessed).
Has anyone else experienced this and found a solution?
Thanks very much
Message posted via AccessMonster.com
http://www.a...How To Allocating Rent Between Two Categories?
[The part I need help with is that the amount in column G has to be
allocated to the following accounts in this order
Gas, Hydro, Promo
I'm just working on allocating the last two, so I'm assuming that
there is rent remaining to be allocated from column G after allocating
to Gas, Hydro, Promo, FC CAM and Mkt CAM.
The remaining balance has to be allocated first to base rent than to %
rent on a monthly basis. If some rent is allocated to % rent, and the
next months sales are so low that the base rent is fully filled we
need to first reduc...Shorter Formula
Can anyone shorten this formula please. Basically all it
does is gives me an average of the figures in Column "W"
depending on the number of times that product appears
in "R" column
I didn't try too hard to analyze your formula, just noted that your ranges
and sum_ ranges...Formula counts incorrectly
Column K contains dates and blank cells. I would like to count how many
of these dates fall within a given date range. For example, in the
first week in May. Column K contains these dates. 5/2/2005, 5/3/2005,
5/5/2005, 5/5/2005. I would like to count this as 4 dates within the
first week of May. I created this formula but the output is 3.
I assume that it is only counting 5/5/2005 one time?
I changed the dates in the formula to check a second 2nd date range
(5/9 to ...Mail Delivery Locally Between Two Exchange servers Locally
1) I have two Exchange servers hosting Exchange 2000, Exchange 2003 in
the same subnet.
2) two domains are different from each other (DmainA.com.sg,
3) When these exchange servers send mails, it will go through the SMTP
of its own.
4) when DomainA send mail to DomainB or viseversa it will go through
the SMTP which results slow delivery of mail.
Q:- 1) is there any way i can configure these servers to deliver mail to
each other directly.
2) on Exchange 2000 any mail for DomainB must go through any
conector,deliver directly or smar...Protect formatting & formula
I need to protect the formatting & formula in each cell to avoid accidental
changes during update by different users. However, if I protect them, any
new row creation and deletion are disable. Is there any way resolve it?
Especially, when create a row, how can all formatting and formula to create
automatically for the new row?
What version of Excel?
"Scott" <NoSpam-Scott.Xe@GMail.com> wrote in message
>I need to protect the formatting & formula in each cell to avoid accidental
>changes d...Is there a way to automatically put the $ sign into the formula?
I want to turn this =A1 into =$A$1.
How is the best way of doing this. Is there a shortcut....keyboard stroke to
Hit [F4] several times and watch the results.
> I want to turn this =A1 into =$A$1.
> How is the best way of doing this. Is there a shortcut....keyboard stroke to
> do this??
Enter =A1 then tap F4
Repeated taps of F4 scroll through the referencing options
When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently an...two smtp address
I have a client who has an exchange server and uses
outlook 2000 for mail messaging. She has her main email
address and an additional alias smtp address. She
recieves mail from both aliases and when she sends
messages it only sends from her main address. Is there a
way to change so she can send emails from her other smtp
Well, if she used Ol2002/2003, she could set up a pop or imap (cam use fake
pop server name) with the second SMTP address and choose the account to send
on... but not with 2000. There is a 3rd party app that she can install that
will allo...Help writing a formula
I have a huge database that I need to create numbering for each line like
this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the
number doesn't change each line - sometimes the number will be duplicated on
multiple lines because those lines have the same title, I also need to create
another cell extending that letter/number combo that will differentiate the
So the first cell stays the same if the title is the same. The second cell
adds another layer of numbering to differentiate the first cell.
(Row1, Col 1-4) AK-1, AK-1-1, Title1...Re: Could someone please help me with formulas
"No" <email@example.com> wrote in message news:...
> I created a workbook and tried to explain the best I could how everything
> should be. you can download the workbook at,
> I really appreciate everyones help on this.....
> "CLR" <firstname.lastname@example.org> wrote in message
> > Maybe in cell J2 you could put the formula =H2+I2, which would give you
> > sum of the C...turning off formula bar
I can't remember or find how to turn off the formula bar in Excel 2003? Can
someone help please. This is the bar that displays cell contents up top.
Tools>Options>View tab, uncheck Formula bar
Microsoft MVP - Excel
"Randy Starkey" <randy.starkeyNOSPAM@NOSPAMvictorychurch.com> wrote in message news:email@example.com...
|I can't remember or find how to turn off the formula bar in Excel 2003? Can
| someone help please. This is the bar that displays cell contents up top.
| --...Formulas in a criteria area
Can a formula be used in a criteria area when extracting data from a table in
...Deleting multiple rows through a formula
So does anyone know a formula that will allow me to delete multiple rows of
re-occuring data in a spreadsheet.
For example, I have a spreadsheet of 10,000 lines, with multiple mobile
numbers on it. I need to only have one of each mobile number and delete the
remaining rows where the mobile has been repeated?
you may be able to use the advance filter where can select unique records
select data filter advanced filter select copy to another location then in
list range box enter range to filter in the copy to box enter a free column
select unique records only then ok
then just ...How do I clear a column of data without clearing the formulas?
I want to zero out a column of data, but not the subtotal formulas. How can
I do that easily without going to every cell?
Select your column
then choose edit / goto / special - constants - ok
this will just select the non-formula cells then press the delete key
"EllenSwarts" <EllenSwarts@discussions.microsoft.com> wrote in message
>I want to zero out a column of data, but not the subtotal formulas. How
> I do that easily without going to every cell?
qlso look at:
http://www...Lookup formula question #2
I have a Lookup formula setup to validate what the user enters against
a list of stock codes, returning the item costs. Stock codes are
A001, A002 etc, B001, B002, etc, D001, D002 etc If I then enter a
stockcode starting with letter C, I get the item cost returned from the
last letter B stockcode. How can I get it to return P.O.A.
This is what I have at present.
C4 - Parent stock item description
F4 - Stock code
AL4 - Quantity
Any help - greatly appreciated.
I'm entering a formula into a cell. As I enter the formula, Excel is
providing the normal cues, i.e., telling me which argument I should be
entering (all well and fine). However, once I'm done, it appears that Excel
is interpreting what I entered as text. If I edit the cell, all looks fine.
If I check the Format of the cell, it says it's a text. What am I doing
.. . .and the formula is . . . .?
> I'm entering a formula into a cell. As I enter the formula, Excel is
> providing the...Need help with max and if formula
I need to create a cell entry that displays the name of the salesperson who
sold the most cars in the quarter. Use a combination of the “IF” and "MAX"
functions to do this. Hint: you will need to use these functions function
multiple times in the same formula to do this.
Hint: you do not need to "use these functions function multiple times in the
same formula to do this."
Hint: use a combination of INDEX, MATCH and MAX.
"jeremiahw23" <firstname.lastname@example.org> wrote in message
I have a cell with this formula:
I don't remember how it was made up (I got help on this forum), but now I
need to update it by changing the 316 summed value in the last expression to
681. When I do that, the starting and ending braces disappear, and the cell
containing this formula returns a #VALUE! message.
How to fix?
enter this formula with CTRL+SHIFT+ENTER (to create an array formula)
"Bill H.&quo...ADO, Recordset and two Access mdbs?
I have two Access database,
and I want copy records from DB1 to DB2.
If have records from DB1mdb in ADODB.recordset,
could I insert records to DB2.mdb (Same table structure/name)
directly (whitout creating insert -sql statement)?
I have two ADODB.connections, ofcourse.
I'm using VB6 and ADO...
"Major" <email@example.com.HALOOOOOOOO> wrote in message <news:eN9basLuDHA.firstname.lastname@example.org>...
> I have two Access database,
> and I want copy records from DB1 to DB2.
> If have records from DB1mdb in ADODB.recordset,
> could I insert record...Expanded formula bar
Often, I lose track of brackets in long formulas. If I could expand the view
in the formula bar it may make it easier to create and edit long formulas:
C30/( )+( )^3
Is there an automatic way to show the formula bar like this?
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 t...Difference between two times in seconds
hi i need a favour regarding finding out the time spent between two times in
I need the current time like 12:35:45 PM in a cell A1 and after some time
put time in cell A2 as 12:37:50 PM.
I need the shortcut formula for putting in a cells (A1&A2) so that it should
come in Hr: Min: Sec format.
And after that I need the difference of this two times in Seconds (125
seconds above) in cell A3.
use a custom format in A3.
this will keep the time from rolling over to minutes and total the seconds.
"Radhakant Panigrahi" wrote:
...time formula #2
I am trying to calculate in and out time for a time sheet spreadsheet.
Example: 9:00 am (in time), 5:00 pm (out time). I devised a formula:
It works for all senarios except if you begin with PM and end with AM.
5:00 pm (in time), 1:00 am (out time)
Can anyone help me!!
I've done something similar for Surgery in and out time for my
hospital. Our times never cross a day though.
Understand that time is saved as a faction of a day: a date/time raw
looks like 1000.0001
everything to the left of the decimal is date info, e...