Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










How Do I Create Multiple Formulas In One Cell


I have a list of numbers that I want to identify the highest and second highest numbers and create a mathematic result.

For example.
I lets say I have 15 stocks "B19:P19" I have X amount of money to invest "Q21" I want to invest 85% of Q21 in the highest of B19:P19 and 15% in the second highest. If there are 2 highest that are tied I want to split the 85% of Q21. SO far I have been able to identify the Highest and show it at 85% of Q21 with this =IF(B19=(MAX(B19:P19)),(Q21*0.85)) but I am not sure how to get the rest of it to do what I want.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Data Validation: Multiple Formulas Required In 1 Cell?
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:

DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”

I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?

So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?

E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”

The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))

I would be so incredibly grateful if somebody could help me (in laymen’s terms)?

View Replies!   View Related
Delete Only Cell Values (not Formulas) In Multiple Sheets
I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following

View Replies!   View Related
Create List From Same Cell On Multiple Worksheets
I'm simply trying to take for example cell A1 from multiple sheets and list them vertically on a master sheet. When you drag it down it does not alter the sheet number, that just stays static.

View Replies!   View Related
Create Multiple Text Files From Cells & Save As Name In Adjacent Cell
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.

View Replies!   View Related
Create A Timesheet With Time Formulas
I am trying to create a timeline spreadsheet for a weekly radio show I produce. We have 3 segments and the total time of the 3 must add to 59 minutes. Within each segments there are numerous variables with a certain time value. I am trying to figure out how to have time reduced correctly. For Example

Segment 1
Story A 5:00 minutes
Story B 4:30 minutes
Story C 3:00 minutes

What I need is Segment Total (A+B+C) in one cell and Remaining time (from total 59 minutes) in another. I know this may seem silly for most of you, but I cannot get the cells to format properly at all.

View Replies!   View Related
Create Multiple Sheet Tabs From Multiple Cells
Is it possible to make multple worksheets from a selection of multiple cells?

This would mean a selection of 10 cells would generate 10 sheets titled with the cell conent.

View Replies!   View Related
Create Multiple Files For Multiple Groups Of Data
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.

View Replies!   View Related
How To Add Multiple Formulas
I'm trying to add the sum of two seperate columns on two seperate criteria.

The formulas needs to add all cells in range B9:B149 that contain the word "OPEN" and that also contain thw word "MF" in the cell range of C9:C149 to give me a numerical total.

I tried using the IF, COUNTIF, SUMPRODUCT but the mutliple criteria is beyond me.

View Replies!   View Related
Using Multiple COUNTIF + AND Formulas
Is it possible to use multiple COUNTIF combined with AND formulas in a single cell?

The current cell equation is =COUNTIF(C14:C83,"Alpha Full")+COUNTIF(C14:C83,"Beta Full")+COUNTIF(C14:C83,"Final Full")

But I need to to only add those cells if another parameter, namely if another cell continas a certain month.

For example something like this =COUNTIF(C14:C83,"Alpha Full")AND(b14,"November")


View Replies!   View Related
Multiple If Formulas Plus Sum Formula
I am not sure what the best way to describe this is but here I go.

Year Account Net Amount

1 2006 Travel Expenses $5,000.00
2 2007 In Kind Time $2,500.00
3 2008 HR Time $3,000.00
4 2006 Travel Expenses $1,500.00
5 2006 In Kind Time $2,500.00
6 2007 Travel Expenses $6,000.00

I need to create a formula that will say: If column A1:A6 has a ,2006, and column B1:B6 has "Travel Expenses", than Sum C1:C6. The answers should be:

2006 Travel expenses = $6,500
2006 In Kind Time = $2,500
2007 In Kind Time = $2,500
2007 Travel Expenses = $6,000
2008 HR Time = $3,000

View Replies!   View Related
IF Formulas With Multiple Ranges
I am trying to combine three IF formulas that depend on ranges that vary. I think the attached sheet does a much better job of explaining what I am looking for than I can do.

View Replies!   View Related
Changing Multiple Formulas To =IF(,) At Once
I have a page of formulas, comprising of about 12 colums and 250 rows. Each row has a different formula (although there is a recurring pattern).

I will demonstrate what I'd like to do with a simple example:

Currenty, one formula is:

=E6/E15

I'd like to make it say this : =IF('Sheet1!'A1=1,E6/E15,0)

I can't Ctrl-H and replace, because each formula is different.

Is there any way to change an entire sheet of formulas at once (or a selection) to incorporate an IF statement? The formula itself that was originally there becomes part of the IF statement, so I think there may be a way.

View Replies!   View Related
Combine Multiple If Formulas
i have 2 worksheet function IF statements that of course look for certain conditions, but in some instances i need to combine the IF statements in one cell, the 2 i need to combine are below:

=If(D3="S","Sick",If(D3="SW","Swapped",Sheet2!B3))

=If(Or(C1="Line On",G1="Line On"),Sheet2!B3,"")


so what i need is for the cell to show either Sick, Swapped or the contents of Sheet2!B3 however if both C1 and G1 show Line Off then cell must be blank, which is what i achieve with the second if statement.

View Replies!   View Related
Activating Multiple Array Formulas At Once...
Does anyone know how to activate a block of different array formulas at once??

Example:

N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.

In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.

btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.

View Replies!   View Related
Copy Formulas From First Row To Multiple Rows With Some Changes
I have 100 excel file in a folder such as File1, File2, File3, File4 etc.There are some figures and reports on these files. format of all the excel files are same except values. And I have a master file.

As is:
On my Master file, I have n number of formulas in Row 10,which gets data from the above excel files.
eg. Master file Sheet1 Row 10,
I have formula like this from Column B
='[File1.xls]Sheet1'!$A$1 [File1.xls]Sheet1'!$D$1, ='[File 1.xls]Sheet1'!$A$1 [File 1.xls]Sheet1'!$M$1 etc.

In Row 11,

='[File2.xls]Sheet1'!$A$1 [File2.xls]Sheet1'!$D$1, ='[File2.xls]Sheet1'!$A$1 [File2.xls]Sheet1'!$M$1 etc.
If there are 1 or two files then I can type manually in each rows, But I have more than 100 files. So i need a macro which copies the formula from Row 10 to rest of the rows, If there are 100 files with different names in the folder, the formulas have to be copied to 100 rows in the master sheet.

if the file name changes on the formulas I will get the data correctly.

View Replies!   View Related
Add Multiple Formulas In Row & Auto Fill Down
I am trying to do can’t really be recorded, at least i don’t think it can. I have a report that I run off and after I loaded it in Excel I then have to add in five columns at the end of the sheet. These are always in the same columns: R,S,T,U,V.

These are the formulas I have to then input
R =IF(G2="no invoices",A2,"")
S=IF(I2="Match",A2,"")
T=IF(I2="Sent to AP",A2,"")
U=IF(I2="Force Settled",A2,"")
V=IF( COUNTIF($R$2:$U$10054,A2),A2,0)

Note for column V, the range R2:U10054 would depend on how many rows is in the report, its just in this example the report does have 10054 rows. The columns would always stay the same. I would then copy the formulae down for the number of rows that is in the report and filter column V by entries that equal zero to give me the rows I am looking to analyse. Is this the type of thing that can be written as a macro so that everytime I have a new report open in Excel I can just run the macro and it will do all of this for me?

View Replies!   View Related
Multiple Nested IF & SUM Functions/Formulas
I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))

View Replies!   View Related
Can Conditional Formatting Formulas Have Multiple Statements
Can you have IF and AND statements in Conditional formatting formulas?

I put this and I get an error: =IF(AND($G5=""($H5=<>"")) I was testing the water for adding and OR statment also. I really want if cell G5 is blank and cell H5 or I5 has text, then G5 should be red.

View Replies!   View Related
Multiple Operations And Formulas In Spread Sheet
I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?

View Replies!   View Related
Adding Constraints To Averaging Formulas Over Multiple Cells
I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).

For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following

(Examples)
DD-ED = Dividends Decreased & Earnings Decreased
ND-EI = No Dividends & Earnings Increased
As can be seen below this code is present for each company

What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.

So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").

So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).

View Replies!   View Related
Multiple Formulas Causing File Size Increase
I am working with 4 seperate workbooks where 4 different people enter data into cells. I have a 5th workbook that needs to have all the data from the 4 individual workbooks bought across so I can see all data. Like a master file. basically, in each cell of the master, I have the following code. =IF('[loans - 0708 - officer 01.xls]07-08'!B5="","",'[loans - 0708 - officer 01.xls]07-08'!B5)

I then have the code for each officer. As there are 24000 lines collectively for the financial year, the size of my master is 37mb, even when its empty, obviously cause each cell still have a formular in it. Is there ANY way to copy across the data from other workbooks without having to have every single correpsonding cell have this kind of formular to view the code? Also, copy it without having to open the other workbooks.

View Replies!   View Related
Create Multiple Sheets
i have a master Sheet which have 15000 Entries (A:A15000) many are Duplicate, Unique is 63 . Now i want to make the 63 Sheets in a Same Workbook as per these Entries.

I record the Macro for the first 7 Sheets. Now how can I Modifiy this Macro to 63, Instead of record again for another Entries.

Actually, I have recevied a File some time it has 15000 Company Names sometime 20000 and sometimes 2000, Now i want to Make the Sheets according to Company Name Wise.

Now , i know how to record the Macro, but i want to know how to Modifiy the Macro.

" How to Learn and Modifiy the Macros"

My code

Sub CreateSheets()
'
' Macro1 Macro
' Macro recorded 7/16/2009 by AAditya
'
' Keyboard Shortcut: Ctrl+w
'

View Replies!   View Related
Create & Copy Multiple Graphs
I'm pretty much a novice at Excel so obviously my knowledge is fairly limited. Basically I have created 2 sheets called 'Week 1 - By Day' and 'Week 1 - By Campaign'. The first sheet (By Day) contains data which supplies graphs to not only that sheet but also to sheet 2 (By Campaign). Sheet 2 (By Campaign) only contains graphs sourced from data within sheet 1.

So, I need to copy both of these 52 times (for every week of the year). That’s 52 x sheet 1 and 52 x sheet 2.

I have been able to copy sheet 1 easily enough by holding down the Ctrl button and dragging the sheet. This works fine and the formula and graphs within the sheet change automatically. However, the major problem at the moment is sheet 2(sheet 2 only contains Graphs which is related to data from sheet 1). When I do the same thing with sheet 2 (hold down Ctrl and drag) the graphs within the new sheet (sheet 4) are still sourcing the data from sheet 1 instead of sourcing the data from Sheet 3 'Week 2 - By Day'.


View Replies!   View Related
Create Formula With Multiple Values
I need to create a formula that will allow me to use two sheets within the same workbook. I set up a dropdown box in my B2 cell in my first worksheet. My A4 cell is where I am trying to create my formula. I am not even sure how to say what I need. My drop down is filled with numbers from 1-211. I started my formula with =IF(B2=1,Sheet2!B3) this much I can get to work. It fills in the correct information. I do not know how to get it to continue. I need B2=2,Sheet2!B4.

View Replies!   View Related
Macro To Create Multiple Graphs
I would like to create 2 graphs for each set of data in my worksheet. the first set of data that I would like to create a 2D line chart for is in cells AQ3:AS19 (not sure if that is the correct syntax??) and the second is AW3:AY3.

I would like to position the first chart (top right corner) at BA3 and re-size it so that it is 17 cells tall and 7 cells wide. the second chart should be positioned at BI3 and the same dimensions.

I would like to delete the chart legend for both graphs

then I need to repeat about 1000 times. Each new set of data starts 20 cells lower (ie. AQ3, AQ22, AQ41..). Or every time "Band" is found in column AP.

View Replies!   View Related
Create List From Multiple Names
Have a list of aprox 50,000 names. in that list names may repeat themselves multiple times. Is there a way in excel to extrapolate from that list of 50k to display only a single instance of each name in a separate Colum?

View Replies!   View Related
Create Multiple Rows Per List, One To Many
I'm trying to do. I have a sheet that gets popluated and once populate needs to create a one to many output sheet. See example. Sheet1 (gets populated from another process):

1
2
3
4

Sheet2 (is my output sheet, which I then save into a text file to be imported into a different application):
11111
12222
13333
14444
21111
22222
23333
24444
and so on...

Although there is other stuff that I'm doing, this is the jist of it.

View Replies!   View Related
Create Multiple Workbooks From A Single Workbook
On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.

Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.

I am trying to create a macro that will break the report up into seperate workbooks.

For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".

View Replies!   View Related
Parse Worksheet, Create Multiple Worksheets
I have a single worksheet containing data in columns A-J. I need to Copy all cells to a new work sheet when the value in column A changes. The worksheet should be named the value of column A. I have found thread 656226, but am unable to modify to work.

View Replies!   View Related
Create Multiple Entries From Each Row (truncating Rest Of Row)
I have a spreadsheet with 4 columns of information, then a column for a primary name, and then many columns of secondary names (with a different number of secondary names for each row).

For rows with more than 1 secondary name, I need to create a new row with a copy of all the columns up to the secondary name for each secondary name in the row.

A row in the spreadsheet looks like this:

data data data data PrimaryName SecondaryName1 SecondaryName2 SecondaryName3... SecondaryNameN

I need this row to be replaced by the following set of rows:

data data data data PrimaryName SecondaryName1

data data data data PrimaryName SecondaryName2

data data data data PrimaryName SecondaryName3
...
data data data data PrimaryName SecondaryNameN

View Replies!   View Related
Coding A Userform To Create Multiple Entries
I've been trying to code this user form so that when the appropriate command button is pushed, the Inventory Number is populated the amount of times indicated by the "multiplier" number.

I haven't gotten very far successfully. Mostly runtime errors.

Here is a screen shot of my table, along with my non working code.

Private Sub cmdfront_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Scanned")

View Replies!   View Related
Create Multiple Scatter Series By Condtion
how to create a new XYScatter series based on dynamic conditions. For example, I have a very large, unsorted table with three columns: TGT, X, Y, Z.

TGT is an integer from 1-99 and is being filtered dynamically with an Advanced Filter. Anywhere from 0 to 10 conditions are being filtered by the Advanced Filter.

I would like to create a new series for each unique instance of TGT, with or without filtering.

View Replies!   View Related
Combine Multiple Sheets To Create One Table
I need to combine the category sheets back into one main table, knowing that there may well be an increase in the number of categories, as well as the number of rows in each category sheet.

I have attached a sample workbook - if anyone can help me with the code so that when the macro is run, the data in sheets A, B and C are combined into Main Table.

the macro would also then update the Pivot table, that would be the icing on the cake (I have shown the pivot table configuration I need, in this case using only the data from sheet 'C', for reference)

View Replies!   View Related
Create Unique Lists From Multiple Ranges
I have values in Worksheet 1, spread over A1:D25 and A200: D250.

In worksheet2 I have values again from A1:D25 and A200:D250.

Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?

Is that a thing more for a UDF, or is there a excel function/option that does exactly that?

I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?

I found this code on some office help page:

Sub SortAllRangeData()
' Place column header for temporary sort area.
Range("IV1").Value = "Numbers"

It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?

View Replies!   View Related
Create List From Data On Multiple Sheets
I have data contained in matching cells from several sheets. I need a worksheet that reflect the summary of the data from the worksheets. No formula is required. I just want my summary worksheet to list the all the data from my other worksheets. I might end up having about a hundred worksheets that I need included in the summary. Attached is a sample. It would be better if the summary automaticaaly updates as new worksheets are added.

View Replies!   View Related
Create Multiple Sheets And Copy Data To Them
I have a worksheet with a list of project managers and a bounc of data on them.

How can i make a new file with sheets for every project manager in the list and copy the data for each one into his own sheet ?

Every project manager has many lines (there are about 200 managers and 30 000 lines and 30 columns )

I have managed to create a sheets for project managers from a list of unique entries but that's all.

View Replies!   View Related
Create Links To Multiple Csv File In The Same Workbook
My first post at last.

I've recorded a Get External Data macro and wish to modify source and destination components. I would like to replace the absolute link to the file name with a variable and the Destination range to the active cell at the time I run the macro.

This is the is the macro code line that handles the source and destination components I want to modify.

Range("AQ2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:filename.csv", Destination:=Range("AQ2"))

View Replies!   View Related
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want:

Sub Macro5()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveWindow.SmallScroll Down:=-3
End Sub

How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).

View Replies!   View Related
Macro To Create Multiple Graphs From X Sheets
I am trying to use VB to create 50 charts each with 3 lines using data from 3 different excel spreadsheets in the same workbook. I am able to create one chart with 3 lines using data from the three spreadsheets, this is good. However I exprience problems when i try to insert a loop to create 50 charts that correspond the the rows in each spreadsheet. I am new to VB and am very inexperienced with VB. In each worksheet my data is arranged in rows. row 1 of each worksheet has the header information and rows 2 through row 50 have my corresponding data. for example A2 lists the name and F2 through T2 lists the data. I have tried a bunch of different things but nothing seems to alllow me to be able to create multiple charts which correspond to each row of data?

Sub Chart2PPT()
Dim arow As Integer
Dim acol As Integer
Dim StartPoint As Integer
Dim EndPoint As Integer
Dim rStartPoint As String
Dim rEndPoint As String
StartPoint = 2
EndPoint = 4
For arow = 2 To 5
rStartPoint = "f" & StartPoint
rEndPoint = "t" & EndPoint
Charts.Add
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlLine 'Type of graph........................

View Replies!   View Related
Create New List Based On Multiple Conditions
I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part:

IBM US 3 C60 Equity
IBM US 3 P60 Equity
UKX 3 P5000 Index
UKX 3 C5000 Index

I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003.

----CODE----........................

View Replies!   View Related
Create A Single Range From Multiple Ranges For VBA Input
I have a VBA function with the header:

cubspline(Xval As Double, XRange As range, YRange As Range) As Double

The problem is that XRange (and also YRange) is in different areas of the spreadsheet. I want to combine these areas into one range which I can pass on to the cubspline function. What is the easiest way of doing it? I'm looking for something like a "union" function for ranges in Excel.

View Replies!   View Related
Create Multiple Workbooks With Names Based On List
I have some very tedious work to do in Excel:

table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .

S7999 u ee
S7999 w aa

My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?

View Replies!   View Related
Create Multiple Graphs/Charts From Data Groups
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:

Sub Graphs()
Dim Startpoint As Integer
Dim Endpoint As Integer
Dim count As Integer
Dim xStart As String
Dim xEnd As String
Dim NumberSets As Integer
Dim yStart As String
Dim yEnd As String
Dim DataSet As Integer
Dim Data
Startpoint = 11 'The first set always starts in row 11
Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10
NumberSets = Range("L7").Value 'number of times I need the loop to work
count = 1..........................

View Replies!   View Related
Create Multiple Buttons All Assigned To Macro Code
I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.

View Replies!   View Related
How To Create Macro To Move Multiple Horizontal Data To Vertical
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function

Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD

View Replies!   View Related
VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is

Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName

It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.

View Replies!   View Related
Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View Replies!   View Related
Two Formulas In One Cell.
I am trying to clean up a databse and I was wondering if anyone knew of an answer to this:- In Cell C,17 I have this function, ROUNDDOWN(A17/$C$8,0), and in the cell next to it I have ROUNDDOWN(C17/3,0)*3. As you can see, I need the result of C17 to make the other formula work, but I want to have the function as one formula. I am having trouble in writing the formula for the function to work. It should go something like ROUNDDOWN(A17/C8,0)/3(*3) but so far I have not had any lick in achieving a result.

View Replies!   View Related
Paste Formulas As Values (strip Out Unwanted Formulas)
I have a macro running this code to strip out unwanted formulas and formatting.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................

A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.

View Replies!   View Related
Expand/Create Single Cell Alpha Series From Cell
I have a cell with data such as: a0001-0004, a12, C8AF7-8, b17, j35-40 and i want it to output in the same cell(basically write-over the data): a0001, a0002, a0003, a0004, a12, C8AF7,C8AF8, b17, j35, j36, j37, j38, j39, j40.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved