Cell searching and ReplacingHi Could use some help here please have a movie list
someone sent to me and it has cells like
Age of Innocence, The
Bone Snatcher, The
Etc Etc
I would like to do a Next procedure and Replace the text
, The
and put the The in Front of the text
So it is like this
The Age Of Innocence
The Bone Snatcher
Etc Etc
Hope someone can be kind enough to help me
Regards
Graham
Try
=IF(RIGHT(A1,5)=", The",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),A1)
Hope it helps.
Graham Feeley Wrote:
> Hi Cou...
copy cells from one worksheet to anotherHow do I make the "value" or content of a cell on 'worksheet 1' mirror or
copy the font color (and strike through) of a cell on a seperate worksheet?
- 'worksheet 2'?
thanks.
Copy and paste special|values
followed by
Paste special|formats
If you were using a formula, then that formula can only return the value--it
can't change the format.
Depending on how that other cell changed, you could have an event macro do the
work for you.
Garrett wrote:
>
> How do I make the "value" or content of a cell on 'worksheet 1' mirror or
> copy ...
Contents of cell copied to another sheet in case combo box value is choosenI have a value range in a row (worksheet 1).
That range is listed via Data > Name and Validation on another
worksheet (2)
How to have the name of cell A1 of Worksheet 2 mentioned in Worksheet 1
if the particular value is choosen?
So:
Worksheet 1:
10
20
30 X
40
Worksheet 2 (cell A1 = "Table"):
I choose via combo box value 30.
Neigboring cell X changed into "Table".
Bart
Excel 2003
...
Vlookup based on Active cellIs there a way to have a vlookup formual based on the data in the
active cell. I have data on one sheet that I what to pull in to a cell
on another worksheet based on the data in the active cell I click on.
I looking for the reference of the vlookup to be the active cell.
thanks in advance for any help on this
Thomp <williamth...@gmail.com> wrote...
>Is there a way to have a vlookup formual based on the data in the
>active cell. I have data on one sheet that I what to pull in to a cell
>on another worksheet based on the data in the active cell I click on.
>I looking for the...
Copying worksheet but changing formatIs it possible to copy one worksheet into a new one but switch the headings
to be on the left vertical and vice versa? Or must I manually recreate the
whole worksheet in this new set-up?
Thanks
I think you probably need Paste Special and Transpose which takes headings
from Row and puts in column or vice versa. HTH
Sheila
"Glissader" wrote:
> Is it possible to copy one worksheet into a new one but switch the headings
> to be on the left vertical and vice versa? Or must I manually recreate the
> whole worksheet in this new set-up?
>
> Thanks
...
comparing dates cellsI'm comparing a date from one cell to a date within my
formula. I've tried using datevalue but that doesn't work
either. How do you do this?
My formula reads as:
=IF(A2>1/1/2003, "yes", "no")
Where A2 is an entered date ie: 5/1/2003
Thanks,
Ben
=IF(A2>"1/1/2003","yes","no")
--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
"ben" <ben@2ndsource.com> wrote in message
news:01d301c368e3$fed01320$a501280a@phx.gbl...
> I'm comparing a date from one cell to a date within my
> form...
Finding a string in Excel cellsNot sure if this is an Excel problem or a general Microsoft products
"feature", but here goes:-
I've inherited a HUGE spreadsheet with 6 figures of cells in total(!)
and I'm told that some have been highlighted for query or error with
"???" in the cell (in addition to other text in that cell).
Trouble is, as soon as I do "find" with the "?" character, it seems to
ignore it, as if ? is some reserved character.
Does anyone have any idea how I can get round this?
Ta!
The ? character is used as a wild card character (any single character).
...
Conditional Format Negative to Postiive Numbers w/3 Symbol Icon SeHi! I am using Excel 2007 and would like to use conditional formatting (icon
sets-3 symbols) for a range of various negative through positive numbers. I
would like to see a result of a green symbol for any value between -10% to
10%, yellow symbol for any value between -11% to -50% and 11% to 50%, and a
red symbol for any value between -51% to -100% and 51% to 100%. How can I do
this?
This is a problem I encountered when I tried to add a new rule for the
negative numbers range through the conditional formatting rules manager, I
received this message: "One or more of th...
How to apply budget for earned value analysis
I'm having huge problems finishing a plan I'm working on. Maybe someone
here has some clever ideas.
To take it from the beginning: This is a part of some school work, and I
have a number of tasks, and for each of the tasks I have details on what
the budget is, and the planned duration. At a specific point in time I
have details on the completion rate of each task, as well as the money
spent, and the actual duration. From these figures, I want MS Project to
calculate figures such as BCWS, ACWP, BCWP, EAC, SPI and CPI.
My problem is that I have not found a place to enter th...
Maintaining Number Formatting when merging from AccessHi - I am merging from a pre-existing Access database into Word (both v2007)
and noted that the currency formatting applied in the Access dbase is not
being carried over into the Word document (currency sign, # of decimal
places, etc.). Any ideas on how to solve this problem? Thanks,
Chris
Hi Chris,
To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
.. select the field;
.. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
.. edit the field so that you get {MERGEFIELD MyD...
Default value for Combo box on a SubformI am having difficulty using VBA to set a value for a combo box
control that resides on a subform based on the value in a text box
control on a parent form. I know how to do this for textbox controls
but Access 2003 does not seem to allow me to do this with a combo box
using the same method. It returns the error "You can't assign a value
to this object". So how do I assign a value to a combo box then? Below
is my code:
Private Sub Form_Open(Cancel As Integer)
Dim strtable As String
Dim strtable2 As String
Dim getvenueid As Integer
Dim sql As String
Dim sql2 As String
st...
default value with phone numbersHi,
I would like to know if its possible to set a default value in the standard
phone number field(s). To have access to numbers when calling from abroad,
one needs to put +31 in front of the phone numbers. So the number 045 xxx xx
xx would become +31 45 xxx xx xx.
My question, is it possible to set this +31 as default value, so only the 45
xxx xx xx has to be types during input of phone numbers?
Thanks
Hi.
Yes, there is the way. =)
Take a look into SDK. There is are good examples of client-side
customization.
For Your question:
in form constructor You must enter properties of main ...
go to cell with date equaling TODAY()Hello! I have a spreadsheet with January 01, 2006 thru December 31, 2006 in
ascending order in column A, each date in a different cell (A1, A2, A3,
etc.). I don't enter data into this sheet everyday..in fact, months could go
by before having to enter an occurance for say, March 31. Is there a way to
have excel, upon opening the spreadsheet, advance the cursor to the cell with
that day's date in it?
--
Thank you all for your help!
Using function Date rather than Today()
Works for me.
Private Sub Workbook_Open()
Dim r As Long
Dim T As Long
T = Date
r = Application.Matc...
This works except when both cells are blank.Every once in a blue moon I get to work on a personal project. This
is for track of some scores. I modified the formula from some code in
the ng archives but haven't figured out how to take care of when both
cells are blank.
In E2:
=IF($C2>$D2,"YOU WON!!",IF($C2<$D2,"You Lost ... :o(","Tied!"))
this works great unless both C2 and D2 are blank when I get "Tied!"
whereas should be blank. How do we accommodate for blank cells in the
above formula?
Thanks! :oD
=if(count($c2:$d2)<2,"not enough scores", ...
You could use =count...
How do you use "Block Formatting" for column headings?I want to use it to Left Justify/set up on large spreadsheet, so that the
second word falls right under the first.
I am having trouble.
Try this:
Enter the first word, hold down the ALT key and press ENTER. Th
cursor will move to the next line in the cell. Release the ALT key.
Now enter the second word. Simply press ENTER. One cell, two lines.
Use Format>Cells to make sure it is Left Aligned.
HTH
Bruc
--
swatsp0
-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510
View...
I need to shift cells down only to a point w/o disturbing the bot.I have a spreadsheet in which the cells need to be moved down, however only
to a point. If I move everything down it disturbs the bottom row of totals.
Can anyone help me?
I think you need to provide a few more details about what exactly
you are trying to accomplish.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"wade2753" <wade2753@discussions.microsoft.com> wrote in message
news:D905FEE5-2EA7-47A1-A532-4D0CA743121C@microsoft.com...
>I have a spreadsheet in which the cells need to be moved down,
>however o...
Time format 1500 become 15:00Pls help me.
I want to tape 1500 on A1 and A1 edit 15:00
Thanks for your answer
Oh Yes, thanks so much.
"מיכאל (מיקי) אבידן" wrote:
> Use 'Custom Format': ##\:##
> Micky
>
>
> "tran1728" wrote:
>
> > Pls help me.
> > I want to tape 1500 on A1 and A1 edit 15:00
> >
> > Thanks for your answer
> >
Dear Micky, thanks again,
I have a other question, si possible for Macro,
The cursor go directy to the last row+1 of column A
thanks for your reply
"מיכאל (מיקי) אבידן" wro...
Money SDK for QIF format converter?The brokerage that administers our company 401K downloads transaction data in
the QIF format only. They only support downloading all transactions without
specfying a date. So being an angry developer I started writing a conversion
program in C# to convert the QIF into OFX and to automatically track and
eliminate duplicate data. I have the QIF format and the OFX spec but I was
wondering if Money has an SDK anywhere that would speed up the process for
me. I have an MSDN Universal subscription but did not see anything in there.
Thanks
See http://umpmfaq.info/faqdb.php?q=10.
"A...
Convert Positive Value to Negative ValueHow can I get the "Amount" to be a negative number when the "Code" = "G6"?
Maybe I could multiply by -1 and use the CASE statement, but I don't know how.
Table Name = EncounterData
Amount Code
$15.00 G2
$13.00 G2
$18.00 G6
$16.00 G2
$25.00 G6
$30.00 G6
--
Tyro from Missouri
You're right, use a CASE, for example
Select Case When Code = 'G6' Then -1 * Amount Else Amount End As Amount,
Code
From EncounterData;
Tom
"Tyro" <Tyro@discussions.microsoft.com> wrote in message
news:20959474-72BF-454D-8820-...
Error: You tried to assign a null value...Hi Folks!
I'm having trouble trying to figure out the error "You tried to assign a
null value to a variable that is not a variant type."
I have a NotInList event in a combobox (LastName_cbo) of a subform
(sfrAttendee) on a form (frmAttendance). A message box pops open when a
new name is entered into the combobox. Clicking "yes" opens a new form;
clicking "no" generates the error. What I want to happen when I click
"no" is for the field to be returned to its "clean" state with nothing
in it, so that my user can start fresh...
Adding a rectangle to a table cellI would like to add a rectangle to a table cell. However, when I try to do so
I receive an error message stating the "Object does not support this action".
Could someone please provide me with an example of:
Adding a rectangle to a table cell
Adding some text to the rectangle
Setting the Back and Fore color of the rectangle
Thanks to all in advance and have a Happy New Year!
Brian
For all intents and purposes, a table cell IS a rectangle. You can add text
directly to it, set its color and so on.
Depending on what you're after, you could add a rectangle atop...
Time formatsI am creating a template which requires the user enter a time (not
present time, flight times), for example the user will be listing a
number of flight arrivals and departures and what I want to do is have
the user simply enter something like 804p and have the cell display it
as 8:04 pm. I have looked at some custom formats and nothing seems to
be able to do this. Any help would be appreciated.
Thanks!
Tracey
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
See
http:...
Allow any Certain chr within a cellHi
My question is in two parts:-
1. How do I restrict the contents of a cell a single chr namely "A to F"
or 1-9"
2. Since the cell is only allowed to be 1 digit long when I have keyed
and checked it moves on to the next cell automatically
Thanks in advance
Trev
Hi Trever,
Use cell validation: -- Part 1 of question
Data (menu), validation
allow: Text length
Data: between
Minimum: 1
Maximum 1
[ x] ignore blanks
After you have tested with the above to see the error produced, would
refine the error message by continuing with "Input Mess...
Looking for Datmax label formatHi. Has anyone had success printing price labels with a Datamax
printer?
I have tried using the RMS Label Designer, but have not had much
success. I'm able to query data and send it to the Bar Tender program
via access, but that is very labor-intensive.
Anyone have a working template I could look at? Failing that, any
advice on how to develop my own template or find another way to print
labels directly from RMS would be appreciated.
I'm using a Datamax E-4203.
Thanks
Since no one answered my post, taking the bull by the horns, I opened up the
label designer. Since I need priceta...
Changing valuesI have a many-to-many relationship between Outings (with a primary key
of EventID) and Members (with a primary key of MemberID). There is a
table that ties the 2 together, tblAttendees that holds the EventID
and MemberID.
Recently, the MemberID of the tblAttendees changed on 2 records in the
table. I can see the before in an earlier version of the database and
can see what changed in the current database. It appears to be the
first event in the tblAttendee which is changing when a new member is
added. Am I doing something wrong or ??
Jeffrey Marks wrote:
>I have a many-to-many ...