Creating a field to search and enter non duplicate data
I have a team of 15 pople that all work with reference numbers, on occasion
a number has to be sent to a different work group. I've created a spreadsheet
for them to enter the numbers into, but we have been duplicating numbers. I
want to create a field to enter th number into, have the field cross
reference the exsting numbers, and if it does not find a match, enter the
number and sort the list.
I know how to create a custom macro that will accomplish what I want to do,
but I'd prefer a static formula if it's possible.
A formula can not do waht y...record a date a cell was changed
I need to record the date a particular cell is filled or changed. I was
thinking I could create a formula that would enter the current date whenever
anything is entered into a cell. For instance when cell A1 has something
entered or changed in it A2 gets the date the cell was changed placed in it.
Any good ideas?
Ian, here is one way,
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If .Count > 1 Then Exit Sub
If .Column = 1 And .Row = 1 Then
'change Now to Date if you don't want the time
.Offset(0, 1).Value = N...non
...How do I prevent cell resizing in pivot table.
Hi Folks, quick question:
Is there a method to prevent Excel from automatically resizing cell
when it refreshes data ?
For example: I have a pivot table that uses an access query; sometime
it returns no values, which is what I want. The problem is that whe
no values are returned, it resizes all the pivot table fields and thi
messes up my headings and the like.
Could someone help?
Thanks in advance
Message posted from http://www.ExcelForum.com
Nothing you can do about it.
Nowadays if I need formatting I link another sheet to the PT and us
VLOOKUP values etc
Message posted fr...hiding 0 from a cell
I can't figure out why this doesn't work, I keep getting circular references.
i have 3 workbooks. 1 is a summary linked to other 2 which are stored in a
different drive. The summary is I:\abc\etc\[summary.xls]sheet 1'!$d$6).
At the moment the cell inwhich this reference sits has 00:00 which is
mm:ss. I want this cell to remain empty until the cell from which it is
linked is populated with a figure.
The response is "the formula you typed contains an error etc etc.
I followed what I thought I had to but...Query a non-MS/non-Active Directory LDAP Server
Does anyone know how to query an LDAP server from Access VBA that is not
a Microsoft / Active Directory (AD) server? I have found a lot of stuff
about how to query AD and have succeeded in doing so. But that does not seem
to transfer to the non-MS LDAP server. It gives the error "Automation error"
"There is no such object on the server."
Message posted via AccessMonster.com
Is there a chance that there...Non Delivery Strangeness
I rebooted Exchange 2003 SP2 and received the following NonDelivery email on
1/3/2006 for an email sent on12/15/2005. I and other users received a few of
these and the server seems to perform normally for the 100's of other emails
that are sent and received daily.
Your message did not reach some or all of the intended recipients.
Subject: Purchasing From Your Site
Sent: 12/19/2005 2:05 PM
The following recipient(s) could not be reached:
firstname.lastname@example.org on 1/3/2006 10:17 AM
The e-mail system was unable to deliver the message, but d...Find first previous cell with data
Hi, I am not "fluent" in Excel, so please make any answers "dummy" level.
I have a running balance column that spans multiple pages and I am trying to
get the last balance from the previous page onto a cell in the following page.
Additionally, there are column headings at the beginning of each page and I
have all cells (except column heading cells) in this balance column
conditionally formatted to not display duplicate balances, so there are cells
that appear blank but technically are not.
I read a post in General Questions dated 1/16/2006 by Derby Jim and answered
...Maximum number of characters in merged cells
Is there a maximum number of characters a merged cells will hold? I have a text field of merged cells however, after about 11 rows all of the text does not show even though it looks like there is room for the additional text.
Do a search in help with "Specifications" and you will find:
Length of cell contents (text): 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar
"Bill" <email@example.com> wrote in message
> Is there a maximu...Indirect Cell Reference
I want to find the maximum value in a variable-length array
Currently, I use folloiwng to find max value of 1000 - element array
However, only the first 1500 of these cells are populated, and I wish to
exclude the last two populated cells from the MAX expression
Cell E1 contains the count (1500)
E1 = 1500
Cell E2 contains the # cells to exclude
E2 = 2
How do I use E1 and E2 references in my MAX expression to obtain an
equivalent to the desired expression
try this where you are trying to match a number larger than possible in your...Referring to cells by range names #7
no change. but i appreciate the continued interest
rllane47's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1647
View this thread: http://www.excelforum.com/showthread.php?threadid=27830
It is not clear to me what you tried, what did work and what didn't.
I tested my answer:
Space after (Q5)
Microsoft MVP - Excel
"rllane47" <firstname.lastname@example.org> w...VLOOKUP returns formula instead of result
When I do a VLOOKUP formula, the cell returns the text of the formula instead
of the result. I get the correct result when looking the in Formula Wizard
grey box, but when I get back on the worksheet only the formula is displayed.
This is not a case of Ctrl+~ to look at formulas. Any suggestions???
I would say your cell was pre-formatted as Text.
Format to General then F2 and ENTER.
Gord Dibben MS Excel MVP
On Tue, 16 Mar 2010 14:50:01 -0700, slavvy11
>When I do a VLOOKUP formula, the cell returns the text of the f...Combining 2 cells to be put into 1 cell with Vlookup
Basically, what I want to do is combine firstname and surname which ar
located in different columns - side by side though -
the function I'm using is -
Which posts the firstname into the cell I want, is it possible to ge
the surname AND the firstname into the same cell with a variation o
Thanks for any hel
Message posted from http://www.ExcelForum.com
Microsoft MVP - Excel
"Souljah &...Non-cash, Non-regular Transactions
Money 2003 SE V11
I have a need to set up transactions, such as Mileage, to
keep track of non-cash activities. I currently handle it
by setting up Bills & Deposits split into two
transactions totalling $0.00. The first is the
Category/Subcategory I want to track and the second is a
Category/(Non-Cash Offset Subcategory) that I filter out
of all reports. This works ok for me.
Example: (meaning 23 miles traveled)
Mileage:Offset Non-cash Transaction -23.00
However, the transaction is not regularly occurring. Yet
we are required to ...Re-post:Formula Finesse Needed: COUNTIF or DCOUNT or Something bet
I might have posted my original question in the wrong area initially. The
answer i recieved from the General Excel forum was way beyond my ken.
Please see the post below and the answer I recieved. If someone could
explain how the answer works or send an alternative answer that would be
"Sean Timmons" wrote:
> OK, make sure your months are actual numbers.. Jan would be, ay, 1/1
> formatted as mmm.
> in A2,
> should get it
&g...NON TAXABLE Item Setup, What TAX CODE to assign
What is the proper way to set up a non taxable item like a coupon or labor?
I was assigning the <not assigned> (RMS dbase Tax ID 0)
(But I just read said to delete the <not assigned> tax code. to prevent a
runtime error (we have not had yet))
Do I need to setup a Non tax tax code? and add all non tax items to it?
PS I have a < > (blank)(RMS Dbase tax id 3) tax code that seems to
need to be deleted, RMS assigned it tax code 3. There are currently no items
assigned to the < > blank tax id
I can not find any guidance in the RMS Help. It seems MS ex...Help with an IF formula please.
I have the following formula in a spreadsheet. Any value I use in the M
column works EXCEPT F10. For some unknown reason, the formula still
appears to be good, but does not return a true answer.
I will greatly appreciate any help!
rbp's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20456
View this thread: http://www.excelforum.com/showthread.php?threadid=385401
rbp,...Run-Time Error on Cell Range Select
I get a run-time error 1004 on the .Select line. Can someone suggest a fix?
Dim rn As Long, offset As Long
Dim stdate As Date, actdate As Date
actdate = Range("Z2").Value
rn = IsoWeekNumber(actdate)
If rn / 2 <> Int(rn / 2) Then rn = rn – 1
stdate = Range("A" & rn + 6).Value
offset = actdate – stdate
Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select
My best guess is your use of the offset statement, but here goes a longer
response just in case that isn't it.
What are your variable values...NDR from non-existent email address
Any user that sends a meeting request to the user in question gets the
Your message did not reach some or all of the intended recipients. I changed
the info to the innocent.
Sent: 8/16/2005 9:01 AM
The following recipient(s) could not be reached:
Gay, Esse M. on 8/16/2005 9:02 AM
The recipient name is not recognized
The MTS-ID of the original message is: c=US;a= ;p=XXX;l=XXXXXXX-XXXXXXX
This only happens with meeting invites, regular email goes through just
fine. It is jus...mail from non exsiting user accounts
I have one exchange 2003 cluster server. and the email address
email@example.com. We are not using it for external mails. one of our
IBM AIX user is able to send mails to anybody in my exchange server
eventhough he is not having mail account in the windows 2003 domain. if
u check the ID of the sender u will see that he is also using the same
domain name ie @corp.ho.com...
How can i stop others who do not having account in exchange to send
mails to my domain.
Uncheck "Anonymous access" from SMTP virtual server properties | Access |
Authentication and r...i need to add more options to a formula but i don't know how
on one of my worksheets i use this formula
this tells excel to add up all our unpaid invoices and tell me how muc
we are owed.
I now want the formula to add up all the boxes with unpaid next t
them, plus all boxes with these phrases next to them:
received-not banked yet
banked-not cleared yet
so i have tried things like thi
=SUMIF(G126:G242,"UNPAID(or)received-not banked yet(or)banked-no
but it does not work. as you will have guessed i am not too great wit
incidentally, unpaid, re...Find duplicate cells
Is there a way to find all the cells with identical entry? I'm trying to
find duplicate information but I don't know what the duplicate entries are.
Find and replace does not have an option to find something like that.
have a look at
"cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag
> Is there a way to fi...Cell comments
I have already created the code using VBA to pull the data from the M
Access table. Is there a way to set it up so that when you pass ove
the cell it will automatically run my routine
LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965
View this thread: http://www.excelforum.com/showthread.php?threadid=26920
not possible AFAIK as there's no event which got triggered by just
moving over a comment field
> I have alr...getting numbers in cells into formula
I have the following question:
Imagine that I have numers in column A, range A2:A30.
With a formula I found the row number from which I want to start calculating
and placed in cell C1 (let's say 7).
Another formula in C2 finds the stop value (let's say 20).
Is there a way to totalize values in the range found by the formulas in
In other words, I'm looking for a formula that totalizes the following:
sum(A&value in C1:A&value in C2), so that excel would interprete this as
Has anyone tackled this problem before?
I'm very intereste...Cursor position
I have written a small VB script to collect data from the user. At the
end of data entry a YesNo response pops up. When the user hits Yes I
would like the cursor default position should be the first text box in
the new window. How can I do that.
Appreciate your help.
saziz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6350
View this thread: http://www.excelforum.com/showthread.php?threadid=394290
> Hi All,
> I have written ...