How Do I Create Multiple Formulas In One Cell

Dec 29, 2009

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.

ADVERTISEMENT

Using Cell References In File Paths For Formulas To Create Dynamic Formulas

Dec 3, 2013

I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:

=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292

What I want to do is extract the file path from the above formula and make it a composite of several cell references.

So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:

_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH

I tried several versions, I am hoping for something like this:

=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292

View 4 Replies View Related

Multiple Formulas In Same Cell?

May 3, 2002

Is there a way to use multiple formulas for just one cell without damaging the others?

View 9 Replies View Related

Multiple Formulas In One Cell

Jan 24, 2014

I have compiled a spreadsheet and it has columns for the days of the week, Mon through Fri. I have written a formula in R9 which will add a range of comments in the Recommended Actions column (R) depending on what is entered in I9:M9. So far so good

Unfortunately when I enter anything in I9, then enter something else in J9 the "Recommended Actions" just adds another comment after the previous comment in the same cell as the formula in there references I9 & J9 & K9 & L9 & M9.

Can you think of a way to set it up so that when I enter something in I9 then enter something else in J9 it changes the "Recommended Actions" from the old comment to the new comment or deletes the old comment before adding the new comment?

Formula:
=IF($I9="OK","Working ok",IF($I9="No Modulation","Check Profile and modulation",
IF($I9="FLD","Check pulse unit and meter operation",
IF($I9="No Data","Check Comms",IF($I9="Low Pressure","Check pressure on outlet",
IF($I9="High pressure","Check outlet Pressure",

[code].....

View 7 Replies View Related

Entering Multiple Cell Formulas?

Mar 4, 2014

I have a requirement to enter 25 formulas into 25 different cells. The formula is this:

[Code]......

The only thing that changes is the cell reference at the end which goes up in increments of 20,000, i.e. R20000, R40000, R60000, etc.

I have this set up and it works fine. My question is whether there is another quicker way rather than have the above code repeated 25 times?

View 5 Replies View Related

Multiple CountIf Formulas In One Cell

Jun 27, 2012

I am creating a holiday chart for absence, I have put the following formula in one cell

=COUNTIF(F4:NF4,"H")+COUNTIF(F4:AJ4,"HD")

Now when these letters are typed in to the range they calculate "H" and "HD" to be 1 - is it possible to have "H"=1 and "HD"=0.5 to then give a total in the desired cell?

Basically "H" is 1 day holiday and "HD" is (0.5) half day holiday, I would like the total from a range to appear in one cell

View 8 Replies View Related

Multiple Formulas Applied To One Cell

Mar 28, 2014

How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"

View 5 Replies View Related

Locking Multiple Cell Reference Formulas At Once

Jan 15, 2010

I am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.

It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?

View 7 Replies View Related

Multiple Formulas In One Cell To Return 1 If One Condition Met

Apr 13, 2014

I would like to put the following two formulas in one cell, so if one condition is met, it returns 1:

Code:
=IF(IF((AND(MATRIX!C4=0.795),1,0)

Code:
=IF(IF((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0)

The formulas work fine as single formulas in one cell, but I can't figure out how to do both in one cell. I tried this but I get errors.

Code:
=IF(((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0,IF((AND(MATRIX!C4=0.795),1,0)

I've also tried using the OR function, get errors. I'm not sure which to use, IF or OR, and I can't get the syntax right on either one.

I actually have six formulas for each cell, but how to do two, then I'll know how to do all six.

View 6 Replies View Related

Data Validation: Multiple Formulas Required In 1 Cell?

Nov 23, 2009

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 8 Replies View Related

Formula To Combine Multiple Formulas Into Single Cell?

Mar 12, 2014

I'm looking for a way for Excel to combine formulas together into a more compact form for me.

Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.

Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?

Here is the one-cell version of a formula I work with.

[Code] .......

That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.

Attached File : Excel Formula Combination Question.xlsx‎

View 7 Replies View Related

Delete Only Cell Values (not Formulas) In Multiple Sheets

Nov 3, 2009

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 5 Replies View Related

How To Unfix Multiple Cell References From Formulas In A Range Of Cells At Once

Jan 3, 2014

Is there a way to unfix multiple cell references from formulas in a range of cells at once?

View 2 Replies View Related

Posting Day Book Details And How To Enter Multiple Formulas In Single Cell

Apr 28, 2014

Problem-1) i want to round off the digit in the same cell i,e. Formula in F12 should be included in D12.

Problem-2) i have entered a sample account.in this i want to post the data entered in day book into their respective sheets of jaya and supervision automatically.

View 1 Replies View Related

How To Create Multiple Checkbox Lists In Each Cell

Feb 10, 2014

I would like to know if there's a way to create a checkbox list in a cell that influences other cells:

For example, I would have column A as "Materials Accepted", and each cell would have a dropdown list with the options "Wood", "Drywall", and "Metal", in checkbox format. Then to the right of that, I would have each of the Materials as actual columns so that column B is "Wood", column C is "Drywall", and column D is "Metal".

The way I would want it to work from there is that whatever options I select from the dropdown, it would then put a TRUE flag in that column. So if I select "Wood" and "Metal" on the dropdown checkbox list, columns B and D would both read TRUE on that particular row.

Just wondering if something like this is possible, as I wanted to apply this concept to a much larger scale (About 29 columns for 29 Materials, scrolling left and right is really time consuming). So far I've only seen guides on how to create a SINGLE checkbox on a row, and none with multiple checkboxes that would function in this manner.

View 1 Replies View Related

Create List From Same Cell On Multiple Worksheets

Oct 11, 2007

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 3 Replies View Related

How To Create VLOOKUP And INDEX Formulas

Jul 8, 2014

I would like to create a spreadsheet with 3 columns: employee ID, last name, first name. Then on the 4th column I would like to enter the employee ID and have the last name and first name auto populate into the 5th and 6th columns. How do I do this?

View 14 Replies View Related

Create A Timesheet With Time Formulas

Feb 26, 2008

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 6 Replies View Related

Combine Multiple Cells To Create Unique Cell Value In A Column

Sep 23, 2012

I would like to know how to combine multiple cell value from each column to create a unique value. for example column A will have values (a,a,b,c,d,e,e,f,i,j) and B will have (1,2,1,1,3,4,5,5,6,7) and C has ( xxx,yyy,zzz,xxx,yyy,zzz,xxx,xxx,yyy,zzz).

i need to create a list of unique possible combination of data into column D.

I have placed below example and how to create a similar code and what functionality does this.

A
A Code
B
B Code
C
C Code
D
D Code

[code]...

View 5 Replies View Related

How To Create Patterns When Copying / Pasting Formulas

Jan 24, 2012

I was wondering if there was a way to create patterns when copying and pasting formulas?

For example, here is what I want to do right now:

=AVERAGE(Sheet1!I2:I650)
=AVERAGE(Sheet1!I2:I649)
=AVERAGE(Sheet1!I2:I648)
=AVERAGE(Sheet1!I2:I647)
=AVERAGE(Sheet1!I2:I646)
=AVERAGE(Sheet1!I2:I645)
=AVERAGE(Sheet1!I2:I644)
=AVERAGE(Sheet1!I2:I643)
=AVERAGE(Sheet1!I2:I642)
=AVERAGE(Sheet1!I2:I641)
=AVERAGE(Sheet1!I2:I640)

In other words, have a pattern in the formula?

Or something like this, but filling out cells horizontally.

=A1/A100
=A1/A99
=A1/A98
=A1/A97
=A1/A96
=A1/A95
etc...

Or:

=A1*A1
=A1*A2
=A1*A4
=A1*A8
=A1*A16
=A1*A32
=A1*A64
etc...

View 1 Replies View Related

Create Multiple Text Files From Cells & Save As Name In Adjacent Cell

Jun 26, 2008

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 7 Replies View Related

Need Macro To Create Formulas And Split (Comma Delimited) Cells Into Rows

Mar 30, 2013

Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.

I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...

Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.

Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .

Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).

Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).

E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"

Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....

Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F

View 7 Replies View Related

Excel 2010 :: Create Macro Button To Clear Multiple Cells On Multiple Worksheets?

Jul 2, 2014

I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.

On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells

B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42

On all other 'Caravan sheets' I want to clear the contents of cells

D4 & D5
E4, E5, E22, E23, E41 & E42

It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead

View 8 Replies View Related

Create Multiple IF Statement And Multiple VLOOKUP Within Same Formula

Aug 11, 2014

I am trying to create a formula which looks in 1 cell to determine 'IF' it states either "BUTT CUT" or "DIE CUT" and then does a VLOOKUP in a table array for each which ever column relates to asnwer to 'IF statement.

This is as far as I have got but even this won't work:

=IF(I15="BUTT CUT",=VLOOKUP(J15,V13:Z22,2,FALSE))

View 7 Replies View Related

Create Multiple Sheet Tabs From Multiple Cells

Jun 22, 2006

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 14 Replies View Related

Create Multiple Files For Multiple Groups Of Data

May 9, 2008

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 2 Replies View Related

How To Add Multiple Formulas

Dec 13, 2008

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 9 Replies View Related

Create Multiple Charts On Multiple Sheets?

Apr 14, 2014

The macro creates multiple charts from data in one sheet, but now when I'm trying to scale it up to multiple sheets, the previous charts created disappear. E.g Creates 5 charts for sheet 1 (can see them being created, using debugger mode), looping to the next chart and they are created but the first ones has disappeared! What am I doing wrong? I'm creating my chart by the following code:

[Code] .....

The startpoint variable is changed for every new chart within the one sheet and the outputsheet is changed when you change the sheet.

View 4 Replies View Related

Combining Multiple IF / AND Formulas?

May 26, 2014

Is it possible to combine the following three formulas into one formula?

=IF(AND(H5>59,(I5<5),(J5<15)),("Close Out"),(""))
=IF(AND(H5>59,(I5<15),(J5<5)),("Close Out"),(""))
=IF(AND(H5>55,(I5<10),(J5<3)),("Close Out"),(""))

View 2 Replies View Related

Edit Multiple Formulas At Once

Dec 9, 2013

Is it possible to edit multiple =VLOOKUP formulas to add in a "[range lookup]" = FALSE without editing each one individually? I was going to use a find and replace for the "col_index_num" and add the FALSE to the end of that, but in this case my "col_index_num"s vary too much.

View 6 Replies View Related

Multiple If Formulas Plus Sum Formula

Mar 12, 2009

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 4 Replies View Related

IF Formulas With Multiple Ranges

May 31, 2009

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 3 Replies View Related

Using Multiple COUNTIF + AND Formulas

Nov 17, 2008

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 9 Replies View Related

Changing Multiple Formulas To =IF(,) At Once

Apr 26, 2006

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 4 Replies View Related

Combine Multiple If Formulas

Nov 14, 2006

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 3 Replies View Related

Countif Formulas With Multiple Criteria?

May 26, 2014

I need a formula to count cells E7:E500 that say "Submitted" but only if cells R7:R500 say "PPB".

My original formula below is counting all cells except for the ones that say "PPB"

=COUNT(IF(E7:E500="Submitted",IF(R7:R50="PPB",'E7:E500)))

View 3 Replies View Related

Activating Multiple Array Formulas At Once...

May 14, 2009

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 14 Replies View Related

Dealing With Formulas In Multiple Sheet

Jan 16, 2012

I am setting up an excel sheet, which requires over 40 sheets + an Input Sheet. The sheets are names, sheet 1, sheet 2, sheet 3...

now, cell A2 in sheet 1 uses a formula, say:
5.42*Input!A2

Cell A2 in sheet 2, would have the formula:
5.42*Input!B2

so and so so forth.

Since I am dealing with over 40 sheets, Is there any way of simplifying this process rather than manually typing out the formula in each of the 40 sheets (especially since each sheet would have over 40 rows, with Sheet 1, linking to Column A in the input sheet, Sheet 2 linking to column B and so on and so forth).

View 3 Replies View Related

Multiple Criteria Filtering With Formulas

May 2, 2013

I have a database with 10 properties of a certain kind of material #.

IE: Material 1206 is Black, SG is 1.2, Duro is 70, Cure, tensile, price, etc.

I want a user to select 5 criteria from a drop-down list and have the 6th drop-down box populate available materials that meet the previous 5 criteria. I am trying to do this automatically and avoid user trying to autofilter (have to make it easy). I've extended the database out to show either a "0" or "1" if the 5 criteria match, but I can not get them into a drop-down list with a formula due to a #REF! error. I am not sure where the circular reference is coming from or what I have to do to change it.

View 1 Replies View Related

Copy Formulas From First Row To Multiple Rows With Some Changes

Aug 1, 2008

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 9 Replies View Related

Add Multiple Formulas In Row & Auto Fill Down

Mar 15, 2008

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 3 Replies View Related

ADVERTISEMENT