Formula To Change Ie 1995 - 1997 To 1995, 1996, 1997
Feb 19, 2010
I have 4000 lines with car make, model, year as the column headings in MS Excel. The years are formatted as 1995 - 1997 (for example). But my website CMS requires the years to be formatted as 1995, 1996, 1997 in order to properly upload. Each of the lines has a different year period.
Is there a function that I can apply to these 4000 lines to change the format? I have a feeling concatenate is involved but can't figure out how to create a formula that knows when to stop after it has reached the final year.
View 11 Replies
ADVERTISEMENT
May 4, 2006
I have a need to import 1600 Lotus files in .123 format (not WK*) into Excel. Either, where can I get the Excel drivers to import .123 files, or where can I get the object model objects, properties, methods, and the scripting syntax used for Lotus's version of VBA, so I can automate the conversion of .123 files to .wk3 files within Lotus.
To automate the Lotus part, I have the 1600 file names (the files all being in the same folder) in a column in a .123 file. I want to:
IF the active cell is not blank
Read the file name
Import the file as .123 from an input folder
Do a saveas to .wk3 format to an output folder
Close the file
Move down a cell
OTHERWISE done
Already tried the Lotus macro recorder. It uses hard cell addresses. I need soft references to the value in the current cell so I can process the list. In Excel, it would be simple for me, as I know the object model and scripting syntax. I just burned two hours proving that Lotus has a model and syntax sufficiently different from VBA that I need a programming reference.
View 4 Replies
View Related
Jul 11, 2009
i am trying to determine how to use the workday function to generate only workdays for 1996 and forward. Each year is listed in a separate column.
View 9 Replies
View Related
May 9, 2006
=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
B1 = Jan
C1 = 2006
View 7 Replies
View Related
Jan 29, 2010
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
View 3 Replies
View Related
Dec 6, 2011
Basically the situation I have is Sheet2 has many references to cells in Sheet1. Sheet2 is for all intents and purposes a kind of nicely formatted report form, and Sheet1 is the input form.
My ultimate goal is to automatically resize row heights on Sheet2 when cell contents change on Sheet2.
Using a worksheet_change event isn't working I presume because it doesn't see the formula output change as a worksheet change, the worksheet_change is firing only when the input is changed in Sheet1.
how can I capture these formula output changes on Sheet2 (triggered from input on Sheet1) OR is there a way of making a particular sheets rows always adjust in height to best fit?
View 5 Replies
View Related
Jan 27, 2007
I want to edit a cell's value without changing the formula the cell contains.
View 10 Replies
View Related
Mar 25, 2013
I have below formula.i need to change the cell address AK in the below formula on the value change in the A3.
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AK$6:$AK$3000)
For example if A3 is having value AZ i need the formula changed automatically as below
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AZ$6:$AZ$3000)
View 4 Replies
View Related
Mar 14, 2014
I need to add change to hundred of formula like below
I want to change the below formula
=SPY!$X$22
=QQQ!$X$22
New Change
=INDIRECT("SPY!$X$22")
=INDIRECT("QQQ!$X$22")
I can do it manually but there are few hundred cells to change and will take me forever.
View 11 Replies
View Related
Mar 20, 2009
I am making several thousand wire tags for a water treatment plant which need to be installed at each end of the cable runs. What I have made so far is tags for one end of the run not the other. I am looking to create a formula to change the tag on the other end so that instead of it saying
1234
to 5678
it would now say
5678
to1234
View 9 Replies
View Related
Jan 16, 2013
I have a cell value. For example. A1= 1-2010. Is there a formula that will make A2,A3,A4 become 2-2010,3-2010,4-2010. If A1 changes to 2-2012, A2,A3, and A4 will be supposed to change to 3-2012,4-2012,5-2012.
View 7 Replies
View Related
Mar 28, 2014
AS per the attchement, I add a date in the cell H2 and when I select in the cell I2 the date in the column K changes as per the =IF formula..
My question is the following: Would it be possible, once I select the option in I2 to have the formulas in the column K changed for value? I put a example recording a macro!
HTML Code:Â
Range("K2:K4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I really wish to have that automaticaly done once I select the option in I2 without running manually a button.
View 10 Replies
View Related
Mar 28, 2014
I want to change a cell value to a calculation based on if "X" value input to a cell.
In attached file explained all detail.
Cell_Change_to_formula.xlsx‎
View 2 Replies
View Related
Aug 18, 2009
I have the following code that places a formula into the cell.
View 5 Replies
View Related
Feb 16, 2006
I have a spreadsheet that monitors the opening time of some stocks. In the
morning before trading starts it looks like this:
StockTIME
QQQQ-O2/15/06
AAPL-O2/15/06
GOOG-O2/15/06
CSCO-O2/15/06
SPY-A2/15/06
When trading starts, the TIME value records the time of the last trade, thus
the first time will represent the time of the first trade - I am trying to
capture this time value. The problem is that if another trade occurs, the
TIME value updates to the time of that last trade. The TIME value is fed by a
DDE link from a data source.
Is there a way to capture the "first" change in the TIME value ?
View 9 Replies
View Related
Jul 10, 2009
=SUMPRODUCT(--('Trial Balance'!A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.01.01"},0))),'Trial Balance'!G1:G100)
Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.
Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.
View 2 Replies
View Related
Dec 21, 2011
I need to know that can we change some value is our formula, For example i was using some bunch of formulas in sheet and then i move that sheet to a new workbook,then i remembers formula as per last workbook. for example.
=VLOOKUP($A1,'[Smithdata.xls]01'!B6:E20,3,0)
i need to change file name in all my formula into B/M shape.
=VLOOKUP($A1,'[Smithnew.xls]01'!B6:E20,3,0)
is it possible through some easy way? e.g as we use find and replace for value then what can we do same thing for formula or something else.
View 2 Replies
View Related
Aug 24, 2007
I'm working through a huge file and trying to compact it down and make it more workable. I've already managed to get it down to what i need but i now need to add a few things so others can work it more easily also.
One of the ideas i had was the following, but for the life of me i dont know where to start.
The spreadsheet will be updated on a daily basis and at the end of each week we will need to submit any changes we've made to the marketing team.
However the change tracker thats built into excel isn't really what i want.
What i though was i should set up an IF formula that would return a True value if any data in that row was changed.
Then i could set up a macro to copy those rows only to a new sheet to be sent to the marketing team, this way the structure of the spreadsheet would be kept to make it easier for them.
View 9 Replies
View Related
Mar 3, 2008
is there a way to change the case of a cell/column without having to use a formula, i.e. just like you would in Word?
The formula seems to be a huge pain and I have to do lots of quick case changes in a large document.
View 9 Replies
View Related
Jun 22, 2009
I am trying to write a formula in code that uses a changing variable, "X".
here is what i have thus far:
.Value = (("G" & X) + 1) - (("G" & X) + ("B" & X))
I am trying to say: ((GX)+1)-((GX+BX))
X is a variable that changes in a loop. it will be the row number. so for example:
(G18+1)-(G18+B18)....I AM APPEARNTLY NOT WRITING THE SYNTAX CORRECTLY.
View 9 Replies
View Related
Feb 20, 2010
it sounds simple but, all i need to do is change a cell value dependant on another cell value
i need say A1 with the formula
i need C1 to be say "Yes"
i need a formula to show "It Works" in D1 if C1 says "Yes"
But the formula for this needs to be in A1
View 9 Replies
View Related
Aug 3, 2006
I need a formula that will update A1 to what ever the sheet name is. Is that possible? It sounds so simple but I can't seem to find anything so far.
View 5 Replies
View Related
Apr 30, 2007
I want to change the date on my spreadsheet which runs from May 07 to Apr 08 by just inserting one cell, how do I complete this action. The months are in seperate cells.
May-07Jun-07Jul-07Aug-07Sep-07Oct-07Nov-07Dec-07Jan-08Feb-08Mar-08Apr-08
View 8 Replies
View Related
Jul 11, 2007
I am trying to understand VBA codes. Actually I wrote a formula with IF and I want to change it to VBA because it is so long and creates some problems. It is too important for me =IF(AND(0<C6;C6<9);"0";((IF(AND(8<C6;C6<14);(C6-8)*(8,4);((IF(AND(13<C6;C6<10000);(5*8,4+(C6-13)*(26,45));" ")))))))
The logic is like that, this program will calculate an amount.
btwn 0 and 8 days, it is free
btwn 9 and 13 days, 8.4 euros per day
after 13 to infinity, 26.45 euros per day
Therefore for example for 10 days, you have to pay 2*8.4 = 16.8 euros
for 14 days, 5*8.4 + 2*26.45 = 92.9 euros
View 3 Replies
View Related
Apr 9, 2013
I have the below code that works fine. However I want to use a countif function to update Column I instead of manually having to type in the numbers each time they change. I know it would probably be a Worksheet_Calculate Sub but cannot seem to find one that works. The time stamp would be put into column H and the first row of data is row 3.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("I3:I12"), .Cells) Is Nothing Then
Application.EnableEvents = False
[Code] .....
View 2 Replies
View Related
Dec 30, 2013
I am attempting to solve an issue where for example you have a set simple formula of :
=C18+C19+C20+C21+C22+C23+C24
Is there a way to make it so this formula will adapt and change if you add a new row to the column?
View 4 Replies
View Related
Apr 26, 2014
=IF(D8=9999,"",IF(D8<>"",0.02,""))
I am currently using this formula and rather than 9999 I want any number from 9000 to 9999 to result in 0
View 1 Replies
View Related
Dec 26, 2006
This may be more of a math question rather than an Excel one.
If I have a spreadsheet with a cell that produces different negative number each time it is used, is there a way i can add a formula to make it a positive number.
So if Cell A1 showed -£244.22, I want to add an equation that changes if to £244.22
Cell A1 changes so it would need to do the same for any negative number within that cell.
View 10 Replies
View Related
Nov 29, 2008
i have this formula in cell L30. change formula so it doesnt retun #value!
View 2 Replies
View Related
Jan 9, 2009
I have a formula that basically copies a cell into another workbook if 'other' is entered into the column to the right. i,e cell A1 has 'other' B1 has '100', the figure in B1 is placed into another workbook cell. The results are only shown if a month is selected from a drop down on the 1st workbook with the meta data.
What I want to do is to add a command to the formula below so that it will recognise that after the first item is entered it will look for the next data item and not just repeat the first….
{=IF($L$92:$L$580="","",INDEX($L$92:$L$580,MATCH(1,(TEXT($C$92:$C$580,"mmmm yyyy")=TEXT($E$5,"mmmm yyyy"))*($L$92:$L$580<>""),0)))}
In the attached worksheet is an example of what I want (table 2) and what I'm currently getting (table1)…
View 4 Replies
View Related
