I have two cells A1 and B1 both text:
I want the resulting C1 value to the A1&B1 but initial Cap
Eg
A1=george
B1-smith
C1 would = George Smith
What formaula dow i need to put in C1 and thus be able to drag down the page to convert over 1000 rows
I have two formulas which I'm using: =180*(120.5-1)/119.5 and =90*(60.5-1)/59.5
In the first example, I want to replicate the formula across row 1, but with the value '1' incrementing by 1 each time, upto 240.
In the second example I want the '1' value to also increase by 1 for each cell down column B, up to 120. when I try dragging the formula down or across, I am only able to replicate the formula exactly as in the initial cell (ie. the '1' doesn't change). I've tried using the $ symbol, but this doesn't work. I don't fancy doing this manually for a total of 360 cells!
Im trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level.
The way the forumla would need to work would be something like this:
(E10) Enter Current Level:
(F10) 37 (which is my current level)
F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11)
Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.
I've created a combobox. It is assigned a certain array of Long Values based on various criteria. I want to set the value of the ComboBox to empty before the criteria are assessed. What is the initial value of a ComboBox? zero, null, or ""
View 3 Replies View RelatedI have declared arrays for the variables in the code segment below. There is however a problem with the way I've initialized C and F as I the compiler stops C(1) and displays the message: "Compile Error: Expected Array." For the calculation to proceed correctly, the first values of the C(i) and F(i) arrays need to be one and zero respectively.
Is there a way to initialize these variables to these values?
Art
C(1) = 1
F(1) = 0
G(1) = Y(1)
B(i) = C(i - 1) + Q
C(i) = (1 + B(i) ^ (-1)) ^ (-1)
F(i) = 1 / (1 + B(i))
G(i) = F(i) * G(i - 1) + (1 - F(i)) * Y(i)
arrKAL1(i)= G(i)
how can I do this?
Mike A Johnson
Tomas P. Carbone
Samuel L Jackson
Peter B. Lacrone
some have the period some dont.
im thinking I could just get rid of the periods first with a simple find/replace
so getting rid of just the middle initial ?
I HAVE ON EXCEL SHEET IN COLUMN A NO OF CUSTOMER NAME THEIR SOME ROW NAME WITH ANICIALS WITHOUT SPACE TYPING for example (G.K.KHAN).
I WANT FORMULA AND AUTOMATICALLY SPACE AFTER ANICIAL for example (G.K. KHAN) LIKE THIS.
When inserting a hyperlink in an Excel cell, the default pathname for the new link is always the current pathname of the workbook. After navigating to a distant pathname and inserting a hyperlink, inserting the next hyperlink in the next cell starts all over again at the current path.
Is there a method of telling Excel to start looking in z:\ABC instead of the current path? This would eliminate navigating through the entire path for each link.
A macro that handles the hyperlink and puts up a navigation window or something like that would be ok.
i need code to check if number is "Initial Number" (3, 7 etc.)
View 9 Replies View RelatedI am trying to extract just the first initial from a list of last names into another column. How can I accomplish this?
View 5 Replies View RelatedI have a date on the top left hand corner of my excel sheet. I want to add exactly one month to that date in a vertical list (without using micros)
Example:
1/4/2005
1/5/2005
1/6/2005
1/7/2005
1/8/2005
.
.
etc
PS: I tried adding 30 (or a similar number like 29). It works but not if I want a large list of dates, which is what I want.
Are Boolean variables ALWAYS initialised as FALSE when they are created?
I am wanting to leave a 'marker' in a function so that I know whether it is the first time it has been used or not. Something similar to this:
We currently use Excel to make reports of data obtained in field surveys. General data is repetitive throughout report. We have a title page, general particulars (GP) page, table of contents then data table pages. Each data page has info from general particulars page. i.e project name, date, inspector etc. Right now some cells of data table pagess have formulars pointing to applicble cells on GP page.
Presently I am designing a userform to input title and GP page data. So far OK.
Question: Can I take input on userform and add to footer. Info to be added after or inserted within text already there? i.e. Surveyors Name: ?(left footer) something center and something right. What type of code needs to be added and where do I place it .
I have inserted a set of numbers from the web, and some are appearing with $, and others with commas, and also some with spaces before the numbers. I can remove the $ and commas fine using crtl replace but cant get rid of the spaces before the numbers? I seem to remember in word there is a way to do this using crt+replace (using $ or ^ sign perhaps?) but need my memory jogged.
View 5 Replies View RelatedI need to create a drop down menu where the contents are based on a separate table that has been filtered dependent on a choice made in the initial table.
In Sheet "Bookings" I have a list of lesson times for a riding school.
One of the columns is labelled "Customer experience" and contains either "Advanced","Intermediate" or "Novice"
A second column is designed to assign the name of the horse used by that rider during the lesson.
Horse details can be found a second sheet called Horses.
The horses are rated "Advanced" can only be ridden by Advanced Customers, whilst Intermediate ones can be ridden by both "Intermediate" and "Advanced" customer. Novice horses can be ridden by anyone.
Ideally I would like to be able to include a drop down in the Bookings table that contains the name of available horses.
I have files saved as a code, then a customer name. I'm trying to write some more code to open the file only based on the initial code.
For example the file name is X090000 TEST
This doesn't work;
Open("V:\\\" & oldFileName & "*" _
& ".doc")
After copying certain data into an excel worksheet, all entries in a column begin at 2nd space of each cell which I don't want.
I can manually backspace each entry to the first space of each cell. But I cannot backspace all entries at once even if I try Find then put in a space for find and then in replace leave blank.
I am looking for a simple loss/gain formula for my students. They are trying to find the loss/gain difference between an initial value, and the current value. They are using a monetary value, not date/time.
View 5 Replies View RelatedI've a workbook with 3 tabs with names "tab1, tab2, difference" and I've a macro that calculates tab1-tab2 and puts the results in tab "difference" This macro runs every time I open the workbook or every time dataset is refreshed on any tab.
everything works great but i've following issue.
let's say my cursor is in tab2 & i refresh/update dataset on tab2 which will trigger macro to update values in tab "difference".. this is good. but, soon after macro runs & updates tab "difference", cursor is no more in tab2 but in tab "difference" this is OK if I've 1 or 2 or few tabs but i've 25 tabs & the user has to go back to the initial tab everytime dataset is updated on any tab. Is there a way for the macro to recognize where the cursor initiallly was & run the macro & place the cursor back to the initial tab?
I'm trying to capture if any changes are made to a UserForms selections.
I initially load all the data from the database when the first user form is initialized, see code below
[Code]....
Once I have selected the corresponing record from the combobox , in this case Planning Number, then select the command button which runs the code below
[Code] .....
Now the user form frmProcessEngineeringTemp is opened, it now populates all the comboboxes, textboxes and option buttons in this form based on the planning number choosen from initial user form
[Code] ....
Once the user has finished with the user form the information from the form is passed to a worksheet via a command button. If changes to the user form selections were made i need to capture what has changed (if anything), from what was initially loaded into the second user form (frmProcessEngineeringTemp).
I have seen numerous posts from various sites that discuss Change and Exit events from a Class module and many instances where each combobox, textbox and option button is hard coded to capture changes.
Being that I have captured my initial inputs from the database thru the myVar 1 thru 40, can this be put into a string array and the current inputs/selections in the frmProcessEngineeringTemp put into a second array then compare 1 to another looking for difference, then output the differences to a worksheet for tracking purposes. At the same time if changes were indeed made i would increment the Revison record by 1.
I have a listing with Middle Initials in column D. D also contains dates and Names. I want to remove the Middle Initials only. I need to do this without moving around cells. So a Find:="A", Replacement:="" type of situation. Right now I have 26 two line entries to take care of this, but I know it has to be easier and use less lines. (Trying to consolidate code for a better look).
Here is some of what I have (that works, but is long):
Code:
'
' Replace Middle Initial from Prod Sheet
'
Cells.Replace What:="A", Replacement:="", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
[Code]...
I thought that I might be able to do something like this, but I can't get it to work.
Code:
Dim Alpha as Variant
Alpha = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For ABet = 1 to Cells(Rows.Count, "D").End(xlUP).Row
If Range(ABet) = Alpha Then Range(ABet) = ""
Next ABet
' or I thought maybe this would work.
Cells.Replace What:=("A", "L", "R", "M") Replacement:="", LookAt:=xlWhole, SearchOrder _ :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
' or I thought maybe this would work with the variables.
If Range("B" & ABet) = Alpha Then Range("A" & ABet) = "" None of them worked though.
information about users selecting a folder at this link. However, how do I change the starting point to a network drive, as opposed to "My Computer"?
For example, we reference our network drives like this:
\OurSite.comSharedFoldersDivisionName
Instead of coding it (like below), can you set it in the properties window for labels, buttons.. etc.
[Code] ....
I am trying to add a vertical line dynamically to a chart. What I mean is I have a chart that will have a line type chart on it and I want to add a vertical line to it based on the output of one cell in that same workbook. I am making this workbook for use by end-users so I can't ask the end-users to make a bunch of modifications to the sheet or or chart. What I do has to just work when they use the workbook.
The line chart has an X axis of time and a Y axis of items sorted. (This is the part of the chart I have done already.) I want to add a vertical line fed from a cell which will have a time fed from a cell on the workbook. For example the line chart goes from 9:00 to 16:00 and the vertical line might be at 13:00. The cell feeding the vertical line is subject to change based on the other inputs on the workbook so that's why I say the vertical line has to be dynamic as the vertical line could be anywhere between 9:00 to 16:00 or it might even be that I can't place the vertical line because the feeding cell is outside the range of 9:00 to 16:00.
I have seen a number of ways to do this but none that are dynamic and automatic and don't require end user to adjust the workbook to make the vertical line at the correct on the time scale.
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies View RelatedAdd a column next to the first name. The in that column use the formula
=Proper(A1), A1 being the cell that contains the first name. Then copy that
cell down. Do the same for Last Names. Then keep the cell values, and delete
the original set.
Should be an easier way I think (selecting the entire column and using
conditional formatting) but MS hasn't seen fit to do this yet.
--
John C. Harris, MPA
JCZ Consulting Services, LLC
"Robert Judge" <RobertJudge@discussions.microsoft.com> wrote in message
news:56F8D0DC-3D88-441F-9B47-A5CCDFD133A4@microsoft.com...
>I have an EXCEL worksheet with columns including first name and last name,
>in
> all capital letters:
>
> Column A is JOHN
> Column B is SMITH
>
> How can I change all the names so that, for example, JOHN becomes John and
> SMITH becomes Smith?
i would like to use the formula "now()" to establish a date in a document. is it possible to lock this date to the initial date? (so when the document is created the date won't change the next day)
View 2 Replies View RelatedI was wondering if there is a formla that will delete a formula when it has done its calculation, or stop the formula from constantly updating.
I've got a formula
=IF(COUNTA(A1)=1,TODAY(),"")
When something is typed in Cell A1 the cell with the formula will input todays date.
Is there a formula that will stop this formula from updating, as when you go into the file on a different day the date would have been updated.
I have a situation where I have to curve fit data, this can lead to different formulas being used with varying constants.
Is it possible to pickup a TEXT based formula and related constants from other cells, and then place this into another cell as a functioning formula. For Example
Cell A1 contains the formula as a text string whether it be y=a+bx+cx^2, or y=a+b/x, etc
Cells A2:A6 contain the individual constants, a, b, c, etc
I would then want the VBA to read the text based formula and put it into an output cell as a functioning excel formulae.g
In cell B10: =a+b*A10+c*A10^2
I understand picking the constants up and putting the formula should not be too much of an issue, however trying to insert the variable form of the curve fit is the part that I am struggling with, and am unsure if possible.
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
