I have a long column with town state and zip code appearing like this
Farmington, CT 06032
can a macro be made, or is there a command that will take the last five
characters from each of these cells and put them somewhere else?
If you have Farmington, CT 06032 in cell A1
Put this formula in B1 to get the rightmost 5 characters
Vaya con Dios,
"Zip Codes" wrote:
> I have a long column with town state and zip code appearing like this
> Farmington, CT 06032
> can a macro be made, or is there a command that will take the last five ...defining unique range of cells for different sheets as the same n.
I want to use the same name, month9, to refer the same range of cells, but on
different sheets, how do i turn off the global define?
When you define the name (Insert|name|Define), include the sheet name in the
"names in workbook" box:
You can select your range and type this in the name box (to the left of the
formula bar), too.
> I want to use the same name, month9, to refer the same range of cells, but on
> different sheets, how do i turn off the global define?
...Beginner: Is HWND unique?
When I start an application twice and ask each application
for the handle of its main window (calling an inbuild COM
function), are the returned dwords guaranteed to be
Thanks a lot for your help!
For email replies, please substitute the obvious.
It is not guaranteed to be the same or different. You might get the same
HWND towice in a row, or might get different ones between two instances of
"Volker Hetzer" <firstname.lastname@example.org> wrote in message
> When I start an ...pivottable does not sort date values?
Anyone know why excel 2000 does not sort date values?
it lists in the following way:
Yes. Your dates have been input as text. It is simply=20
sorting them by alphabetical order.
Go back to your original data and find the column with the=20
dates. Create a new column and use =3Ddatevalue(a1) where=20
a1 is the reference to the cells with the misinterpreted=20
dates. Display the cells in the new column as dates.
Now recreate your pivot table including this new column=20
and you will find you will be able to sort i...How can I define unique font per each Outlook profile?
We have several Outlook profiles on one computer and each user wants
their own font. I noticed changing to one type of font in one profile
affects all the other user profiles in Outlook.
Is there a way to assign unique fonts to each Outlook profile?
Many thanks experts...
Each user would need their own Windows login.=20
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
and Microsoft Outlook Programming - Jumpstart for=20
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstar...unique record numbers
I am trying to create a unique PIN for members in a table (not the primary
key) that will be 4 digits long, and would like each new entry to be
incremental. I am very new, and would appreciate a detailed instruction
(though I can get around access fair enough). Any help would be appreciated.
On Fri, 5 Mar 2010 19:19:44 -0800, Rev David Bissas <Rev David
The "DMax + 1" technique should work for you. Google or Bing for it.
Microsoft Access MVP
>I am trying to create a unique PIN for members in a table (not t...CString value
I want to know how to assign the CString value into
How to convert?
vcteam <email@example.com> wrote in message
> I want to know how to assign the CString value into
> VARIANT type.
> How to convert?
> VARIANT vt;
> CString st;
Thanks a lot.
>vcteam <firstname.lastname@example.org> wrote in
>news:05d501c3c0cc$7e644df0$a001...printing zero values
I have a number of reports within a single spread sheet. Of these reports
only those relating to fields with poistive values need to be printed. How
can I avoid printing those fileds relating to zero vlaues?
Try - Tools, Options, View, and uncheck zero values in the Windows Options
"dave glynn" wrote:
> I have a number of reports within a single spread sheet. Of these reports
> only those relating to fields with poistive values need to be printed. How
> can I avoid printing those fileds relating to zero vlaues?
...Cell Values in Headers & Footers
Is it possible to insert a cell value in a header or footer?
Right_Click on a Tab, choose View Code, then double-click ThisWorkbook of
Click on the Left-Box down-arrow select Workbook in the right_box down-arrow
select Before Print
Type in line 2 below:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = Range("A1").Value 'LeftFooter for
example with Cell A1 Value
"Micah J" <email@example.com> wrote in message
news:821ADBF8-B2CA-4115-8164-EF1460FFB1BC@microsoft...how to lookup a value within a range and return a label
Wrecking my head this one:
For example if the Label
A is from 1 to 200
B from 201 to 1000
C from 1001 and above
How can I lookup and return the correct label for a value.
Is there a function for this, or does it require programming
Put these values in cells A1:B3
put the value to look up in C1, and use this formula
> Wrecking my head this one:
> For example if the Label
> A is from 1 to 200
> B from 201 to 1000
> C from 1001 and above
> How can I lookup and return the correct label for a...Is there any a "unique" function performs on CStringArray or CStringList?
Is there any a "unique" function performs on CStringArray or CStringList?
I know there is an algorithm function "unique" performing on containers in
Standard C++ library.
Is there a similar function in MFC?
What am I doing now to avoid adding same strings to container is:
if (strList.Find("somestring") == NULL)
But I realized it may be an inefficient way.
Any good sugestion?
I would have used a CMap for this. Then, when all insertions were done, I might consider
converting it to a CArray...how do i list values within a given range?
i am trying to list the values that my original data does not provide. E.g I
have a range beginning with "ABC0001" and ends at "ABC0043". How do I write a
query or to list the values not shown i.e "ABC0002" all the way to "ABC0042"
as individual values in a seperate table?
I would use VBA to loop through a recordset, and record the 'missing' values.
Use Left$(), Mid$(), Right$() and/or InStr() functions to help seperate the
numbers from the text.
Former Access MVP
"Ify O.&...Help with SUMPRODUCT & converting date to value
I have the following Excel calculation, shown below. It is searching cells
A6:A1000 on various sheets for data which fall between a certain date/period
range, and is then adding the values in cells L6:L1000 together to display on
The problem I have is that the data in ranges L6:L1000 are supposed to be
Dates, but the only way i can return a value without an error is to use a
number (e.g. of 1) instead. How can I use Dates within cells L6:L1000, and
still return the same totals?
Many thanks in advance for any tips you can offer!
=SUMPRODUCT((Office!L6:L1000)*(O...How do I format a row depending on the value of a cell in the row
I have a table set up and would like any row of the table where a particular
cell in the same row is blank to be highlighted. In other words, for any cell
in column J that is blank, I want the corresponding row to be highlighted.
I'm trying the conditional formating 'Formula Is' but so far it's not
working for me. Any ideas?
Select the whole table, assume the table is A1:D10, so formula is should be
replace A1 with the left uppermost cell in your table that you want to test
for and D1 with the right uppermost cell, notice the mixed relative a...Investment re-valuing
My 401(k) company uses fractional shares and fractional values. The accounts
they use are not directly referenced on any particular exchange. I had to
manually create the accounts, and I have to manually update the prices.
recently, two of the investments were "re-valued". They reference it as a
"Transfer" but all that really happens is all my shares for a given
investment were listed as negative transactions with the current unit value
(so that the dollar value of the transaction was negative). Then they
immediately followed that with a positive transaction for the ...Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell
multiple items, but I only need to show the vendor once so I can pull other
information. There are also may be blanks in the rows if the vendor is
Let me add one more twist to this problem:
The unique values also need to filter out where it may be 'VendorA - West'
and 'VendorA - East' and combine them into one record. I only need it to
show VendorA and not both records East and West (same company, just
different drop ship location).
"Karl Burrows" <kfb1@spa...How to search and add a value from sheet1 to sheet2
Let's say you have one sheet with a list of products.
Column1 = product name
Column2 = product number
I would like to have a cell on Sheet2, where I can start typing something,
and it would let me see a list based on what I typed so far.
E.g when I type "Volks" it should show a list of all Volkswagen Cars.
If I then choose the value, it should use this value and add the product
number in the cell next to it.
What function should I use, or how can I do this?
You could use Data Validation drop-down list for the selecting the product
name and VLOOKUP ...m_pDocument Doc/View unique id for each view
I'm using the standard mfc mainform with a doc/view. While I'm in the
view portion I need to track the actions taken individually and
separately for each view. So when when I click on view one I track
that in a separate place than view two.
I there a unique id or index for each view? Something like
<firstname.lastname@example.org> wrote in message
> I'm using the standard mfc mainform with a doc/view. While I'm in the
> view portion I need to track the actions taken...Copy Cell.Value
I have 2 workbooks:
# 1 Workbook.("TBD.xls").worksheet("Sheet1")
#2 is a workbook that I open with this VBA macro:
fName = Application.GetOpenFilename
I also have this VBA Macro
blastrow = Cells(Rows.Count, "B").End(xlUp).Row
isum = Application.WorksheetFunction.Sum(Range("b1:b10000"))
Cells(blastrow + 2, "B").Value = isum
My Question is: How do I Copy the value of "Isum" in the
Workbook.("TBD.xls").worksheet("Sheet1") in cell "F34"
I'm kind of confuse...Unique Values, not Unique Records
My data looks like the following:
ID Type Amt
1234 A $10.00
1234 B $10.00
1234 C $10.00
I would like to find a way to get only one of the items on to a new
spreadsheet, because they are duplicate records in my eyes. The Advanced
filter does not work because each record is unique. Can anyone help?
in the dialog for the advanced filter, make sure only the ID column is
> My data looks like the following:
> ID Type Amt
> 1234 ...How can SUM a column based on values of other columns?
I need help Suming up Amount column based on values of three different
Let's say I have the following Detail Data:
Month Institute Account Type Amount Memo
1/1/2008 Bank of America Checking 500 Deposit
1/1/2008 Bank of America Checking -200 Check #111
1/1/2008 Bank of America Saving 400 Deposit
1/1/2008 Bank of America Saving 150 Deposit
1/1/2008 CitiBank Checking 50 Deposit
1/1/2008 CitiBank Saving 100 Deposit
How can I SUM t...Auto-delete cells to avoid duplication of values
I enter names in a row of cells (eg: A5:A7...Tom, Dick, Harry)
I then want to later enter Tom in A2 or A3...and enter Dick in
A4.....if I do this, I have to manually go thru A5 to A7 to erase Tom &
Dick (to avoid a double entry)............is there a non-VBA formula
(usable, perhaps, in Data Validation) that, as I go along entering the
names in cells A2 or A3, that will will look at he other cells (A5
through A7) and erase Tom from A5 or A7, and erase Dick from A4 .....so
that I don't have to do it manually?
Message posted from http://www.ExcelForum.com/
Try MVP Debra D's ...Unique Constraints
I have a Users table with 2 fields, UserName and Visible. The same UserName
can exist many times with Visible = 0 but just one record can exist for the
same UserName with visible = 1.
In my Stored Procedure before inserting a new visible user I check the user
doesn't exist in the table with visible = 1, but that is not enough, there
are cases when the Stored Procedure is called in the same moment with the
same parameters and there is duplicated data.
I can't define a Unique constraint because UserName with visible = 0 is not
unique, what would be the best way...how can I check the value 1 cell against the values of entire row
I would like to see if one cell is equal to any other cell in an entire row.
The row contains over 10,000 cells of data how can I insert a formula to do
Use the MATCH() function. It will tell you both if its there and where it is.
> I would like to see if one cell is equal to any other cell in an entire row.
> The row contains over 10,000 cells of data how can I insert a formula to do
thanks, will this also work if the target cell is not in the same row as the
list that I am comparing it to?
"Gary'...testing a cell for a date value
Is there a way I can test the contents of a cell to see if it is a date?
You can use this:
Dates are really just formatted numbers. So, a cell that contains a number
will also evaluate the same as a date entry:
Using ISNUMBER, both will evaluate to TRUE. (provided that the DATE entry is
a real Excal date and not TEXT)
"Karen53" <Karen53@discussions.microsoft.com> wrote in message
> Is there a way I can test the contents of a cell to see if it ...