Dynamic SumIF Formulas That Reference Changing Codes
I have a list of codes from which I'm trying to extract all unique entries.
For example, col A has 001, 001, 002, 003, 003, 003, 004, ...
I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004.
Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code.
The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Stop Formula Column Reference Changing On Insert But Not Row Reference
A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side. When i use =COUNTIF("Attendance!C9:Z9", "Present"), and the next date comes along the formula changes to =COUNTIF("Attendance!D9:AA9", "Present") ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
View Replies!
View Related
Dynamic SUMIF
I'm looking for a formula to help me handle dynamic sums. On worksheet 'A' I have 12 columns, the 12 months of the year. The rows are filled with expenses and the last row sums up the expenses for each month. In a different worksheet I want to present man info, but dynamically. So if the first month is the start of a project, and it's the third month now, I would like to sum up the total expenses of months 1 and 2. If it's the fourth month now, I would like to sum up months 1,2 and 3. I'm using a cell on the man info worksheet to store the number of months gone by.
View Replies!
View Related
Dynamic Sumif Function
I'm trying to create a sumif formula that finds the column the information is in that it needs to sum. I have attached a copy of the file I'm trying to create a formula to get results that I want. Generally you would normally use the "=SUMIF(range,criteria,sumrange)". I was wondering if there was a formula I can substitute "sumrange" to also search a range of data (Eg: "G16:DA28"). I need the range to be dynamic as the column to be sum can change based on the criteria/option button being selected from the product category. I assume there is an indirect function involved, but I am not familiar with it. If you have a better and less complicated formula please suggest.
View Replies!
View Related
Sumif With Dynamic Column
I'm trying to use the Sumif formula when the "Sum_range" parameter is dynamic. I can identify the column number (by using "match" formula) but I have no idea how to use this information as a parameter in my SUMIF.
View Replies!
View Related
COUNTIF Or SUMIF: Changing Amount
I have a data range with 3 columns - A -employee number, B -charging number, and C -charge amount. "A" shows multiple times and must be associated with value from "B". I am looking for totals of "C" for any combination of "A" and "B". Ex. A B C 1 101 6 1 201 7 1 101 4 2 301 6 2 101 1 2 201 4 3 201 5 3 401 7 etc. I am looking for totals (from column C) for A and totals for A with defined B. e. g.: 1 for all 101 1 for all 201 1 total 2 for 201 etc..
View Replies!
View Related
Using Sumif And Rank Formulas Together...
I have a list of "weights" in column H. They can all be ranked from 1 to 30 (none are exactly the same). I want to write 1 formula that sums the top 10 (biggest) weights without having to make another column for "weight rank". I tried the following as an array formula, but it didn't work. =SUMIF($H$3:$H$32,RANK($H$3:$H$32,$H$3:$H$32)&">=10")
View Replies!
View Related
Multplie Sumif Formulas With Division
I have this formula. I'd liked the entire referenced amount(s) to be divided by 2. Nothing I tried worked. = SUMIF( 'X'!$B$1:$B$3000;'Z'!$B34;'X'!$F$1:$F$3000)/7,6+(SUMIF('X'!$B$1:$B$3000;'Z'!$B34;'X'!$G$1:$G$3000)/7,6)+SUMIF('Y'!$B$1:$B$3000;'Z'!$B34;'Y'!$F$1:$F$3000)/7,6+(SUMIF('Y'!$B$1:$B$3000;'Z'!$B34;'Y'!$G$1:$G$3000)/7,6)
View Replies!
View Related
SumIf Reference To Date
How can I reflect the correct trainee count for Day1-Batch1? Currently columns F,G,H computes for expected, actual and total variances of trainee respectively reference to the training date J3. Since this is a 2-day per batch even, I could not capture the 1st day trainee total of every batch because I divide the sum by 2 (2 day / training batch). The sum is only corrected on the 2nd day. Attached herein is the sample file.
View Replies!
View Related
Sumif With Named Reference
See attached for an example. I'm trying to use the SumIF funtion with a named reference as the criteria. Here is my formula. =SUMIF(B10:F10,"<MinReq",B10:F10) It keeps returning 0 even though there is valid data.
View Replies!
View Related
Sumif - Reference To Cell On Another Worksheet
I'm trying to reference a cell on another worksheet in my criteria and the formula doesn't seem to be working. All I get is a value of 0. = SUMIF(G3:G76, ">='City Wide'!B6",I3:I76) If I replace 'City Wide'!B6 with an actual number the formula works. This does not help as 'City Wide'!B6 is a value that can change.
View Replies!
View Related
SUMIF Formula With Reference To A Cell
I attached a file I made conditional formatting on cells C4..G6 which makes any number less than value in cell C15 invisible. In line " Total" I used SUMIF formulas which ignore any value less than 30. However the formulas should not contain static numbers but rather a reference to cell C15. But I simply cannot make it work.
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
Dynamic Chart/Graph For Changing Ranges
I have a database in excel with a lot of tables in which lets a uses a combo box so the user select can view just one table. I want to be able to create a macro so when a button is pressed, it copies the data into a new workbook and creates a chart so they can do whatever they want without disrupting anything in the original workbook. I have been able to find code that will export the data and create a chart (see below) but the problem is the range changes from table to table so I need to find a way of changing how the chart picks up the data range. Sub Chart_New_Book() Sheets("Temp").Select Cells.Select Selection.Copy Workbooks.Add ActiveSheet.Paste Range("A1").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10") ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" End Sub So at the moment when charting ony cells A1:C10 will be plotted no matter what the size of the table is and making the target range bigger only sees the blank cells being plotted.
View Replies!
View Related
Auto Size Dynamic Changing Chart
I have created a dynamic chart in excel where either the number of Series or the number of Data Points will change dependent on user inputs in the model. I've done this using the Offset Function in a Defined Name in Excel and dropped this Defined Name into vba code using ActiveChart.SetSourceData. Anyway, all works fine, pulling the correct data, etc. What doesn't work is the final appearence of the chart. The chart has a Legend placed at the bottom of the chart, as standard, unless there is only one Series, when I remove the legend altogether. Whilst the legend works fine, the Plot Area of the chart doesn't update automatically to accomodate the changing size / existence of the Legend. Does anybody know how to do this? I tried using hieght, etc properties, but I don't know how to make them variable
View Replies!
View Related
Dynamic Filter-Dependant Formulas?
I have a Filter set up across a few columns of data. Lets say one of the columns is called "Hours", and another is called "User". There is another cell called "Totals" outside of the Filter. What I would like to have is a self-updating Totals cell that will Sum the total number of Hours for whatever the Filter is currently showing. Examples: If I set the Filter to show only UserA's data, the Totals cell will Sum the Hours for UserA. If I set the Filter to show All Users with Hours greater than 2, the Totals cell will Sum all the Hours greater than 2. If I set the Filter to show UserB and UserC and Hours less than 5...well you get the idea. Can I write a function that loops through the data that meets the Filter's criteria? Do I need to create a macro to update the Totals cell each time the Filter is changed? Is there an easier way that I don't know about?
View Replies!
View Related
Make Lookup Formulas Dynamic
I need to compare two tables in two different worksheets (A and B) and update the weekly changes (income and sales) from B to A. I have been using an Index(Match) formula which has been working perfect. I am however forced to update to a macro. The worksheets are identical and each item is identified by a unique ID. Any ideas in exchange for a 6-pack Budweiser?
View Replies!
View Related
Macro For Changing Info In Formulas
I have a template work book that has several worksheets with numerous formulas thorughout. For example; =SUMPRODUCT(--ISNUMBER(SEARCH("MCSH",P9:P218)),--ISNUMBER(SEARCH("HWD",G9:G218))). When I set up another template for a different crew I manually have to go through the entire work book and change the crew identifier (MCSH) in every formula.
View Replies!
View Related
Changing Cell Formulas To Values
I currently maintain a program that creates an Excel report. The report is full of formulas (mostly vlookups). After the report is built, I'd like to get rid of the formulas. The only way I know to do this is to copy the report and do a paste special values.
View Replies!
View Related
Sorting Data Is Changing Formulas
I have a league table (attached) - that contains some formulas for updating various columns when scores are typed in. I have one piece of the puzzle left to do, and that is to have the table sorted by column 'O' (points column) in descending order. ie, the team with the highest points sits on top of the league. The problem is, that whenever I sort the league table, the formulas attached to the 'Plyd' column (column H) get screwed up. This always happens and it doesn't matter if I do it through the use of a macro, or if I sort the league table manually by highlighting and then sorting. Always, the selection boxes in my 'COUNT' formulas seem to shift.
View Replies!
View Related
Copy Formulas With References Changing
I am trying to do a simple copy and paste of number of cells A42:H76 to another sheet (in the same workbook) and have all the references stay when pasting to the new sheet. Instead when I paste it I get #Ref and the cell formulas no longer point to the right cell (they are all off by the same number because I did not paste the cells on the same row/column on the new sheet). Also, they don't refer to the older sheet where I want them to refer to.
View Replies!
View Related
Dynamic Range Reference
I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly? For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.
View Replies!
View Related
Dynamic Cell Reference?
Q: How can I point excel to the begining value cell if I know the ending value cell and the nubmer of cells to move to get there? More details: I have an investing sheet that I need to calculate the CAGR (compound annual growth rate). The formula is (ending value/begining value)^(1/number of years)-1 The number of years can vary but the ending value will aways be in the same cell. I am using a count to see how many years have been entered.
View Replies!
View Related
Dynamic Reference To A Cell
I need a dynamic formula which if hard-coded would be something like =(tabname!A7). But instead of A, I want a calculated value such as ($C$2*12+1). My columns are numbered, so I want to say "the 26th column along", "the 37th along" etc, as C changes. I tried the index formula but couldn't get it to work, assuming it is even the right one.
View Replies!
View Related
Dynamic Cell Reference
I have a question about dynamic cell references that I canīt find any info about anywhere else. This is what I want to do: I need to create a sentence that changes depending on user choices. Example: Cell A1 = Sheet!AX Displayed text Dear Mr. "name" Where the X is a number that will change depending on other things. I would love to do something like: =Sheet!A(E1). Where the value in E1 is for example 4. The result would then be =Sheet!A4 and if I changed the value in E1 the reference =Sheet!A(E1) would also change. I donīt know the syntax for doing this or if it is even possible.
View Replies!
View Related
Changing A Cell Reference
I have three sheets - Actual( which has actual figures from Jan - Dec), Budget ( figures from Jan - Dec) and a Summary sheet ( which is just the summary for the current month, say July and compares the budget VS Actual). Every month i need to change the summary sheet to the next months set of figures, say Augusts etc etc. Currently i've been using find and replace, and changed the formula to the correct months column - but its a rather large spreadsheet( its a management accounts pack and has many many more sheets that link up - like lasts years comparison etc etc) Is there any easy way of linking ( without VBA) or anything complex, so that each month i can change my data easily?
View Replies!
View Related
Reference Worksheet With Changing Name
I want to select a specific worksheet by it's VB name. In other words, if the sheet comes up as: Sheet3 (ESF) in the VB Editor, I would like to choose it using the Sheet3 designation. I can do it using the (ESF) designation using: Sheets("ESF").Select But I prefer to use Sheet3 so when the users change the name, the macro still works.
View Replies!
View Related
Copy Formulas Without Changing Range References
I have a large workbook with LOTS of large formulas. However they dont contain $ characters before the ranges as I haven't needed to manipulate them til now. Now I am changing the sheet and I cant seem to move or copy these cells without the ranges whithin the formulas changing! Is there a way to do this? If not is there a way in VBA that I can check through selected cellls and enter a $ before all the ranges?
View Replies!
View Related
Dynamic Cell Reference Under VBA
The original worksheet will have a sheet of data, but I want the user to be able to insert a row of data into this sheet and have the original values still referenced in the macro. I do not believe the range function will work, because the relative cell positions are what are changing. Also, the offset command would not work, because the positions are not absolute to the active cell. Correct me if I am wrong... Snippet of If Shape = "Freeform 30" Then 'AR Receiver = "kim.randy@companyX.com" End If I want it to point to something like =Receivers!C13 How would I do this without hardcoding the data above?
View Replies!
View Related
Change Formula Without Changing The Reference
I have a set of formula in column C to H. I have set a formula as sheet1+sheet2+ like this for about 24 sheets with different reference of cells. I want to change in the entire range of cells C to H as =sum(sheet1:sheet24!b04). The reference what I have mentioned changes in different cells.
View Replies!
View Related
Pivot Table With Changing Reference.
I'm trying to take data from multiple worksheets and copy them into a summary sheet in the same workbook, and then make a pivot table from the summary sheet. I got the first part done, but I can't figure out how to make the pivot table to incorporate new data in the summary sheet as new worksheets/data is added.
View Replies!
View Related
Formula To Reference Changing Name Of Table
I am trying to run a quick format on a table that includes adding a Vlookup formula. The formula references a table that comes out of a pivot table, so it has a different name every time. If I look up the new generated table name, and change it below it works. Is there a way to capture the table name and insert it into the formula? Or possibly rename the table to the same thing everytime (which I think is going to cause some conflict). ....
View Replies!
View Related
Change One Value Of A Cell Without Changing Others That Reference It
I am creating a schedule to use for our helpdesk - it has pre-defined tasks that need to be assigned each day - easy enough to accomplish with a formula. And I've done this by using this similar formula on each line of the tasks (This first task is based on a manual entery into one field (B19)) =IF(AND(B6="Liz"),"Paul",IF(AND(B6="Paul"),"Mark",IF(AND(B6="Mark"),"Diane",IF(AND(B6="Diane"),"Jeff",IF(AND(B6="Jeff"),"Dan R",IF(AND(B6="Dan R"),"Chris",IF(AND(B6="Chris"),"Liz",""))))))) So, B4 is one task (phones) and the formula above (B6) is from another task (tickets) and then a third task (admin) looks at the value of B5 and uses the same formula above, etc. However, I want to be able to change the value of a single cell, based on the (manual entry) value of another (out of task) series of cells (i.e. Sick (B35) & vacation (B36)) - and I don't want any of the other task cells(in the C cell series of 'tasks'), that are basically linked together by the value of the other cell, to change. Thus, in turn, would mean that the same person would now be in two different cells, and thus is creating a circular reference. Let me spell this out a bit clearer. The first manual field is B19 - If I enter 'Chris' into this field, then field C5 matches it. Then field B6 uses the formula above to figure it's value; as does B7 thru B12. Now, If I put another manual entry into 'Sick' (B35), then I want whichever task that person is assigned to (for this example, we'll say I entered 'Jeff" into Sick, and Jeff happens to be on 'tickets' that day) to change to whoever is in cell B12 - but I don't want any of the other cells to change - but, since they are all dependant on each other, this doesn't seem like a possibility. I've tried creating a count of names in the Sick or Vacation fields and then added this to the formula =IF(AND(C5="Liz", B38=0),"Paul",IF(AND(C5="Paul",B41=0),"Mark",IF(AND(C5="Mark",B40=0),"Diane",IF(AND(C5="Diane",B40=0),"Jeff",IF(AND(C5="Jeff",B43=0),"Dan R",IF(AND(C5="Dan R",B37=0),"Chris",IF(AND(C5="Chris",B45=0),"Liz",B12))))))) B38 being Pauls count; B41 being Marks, etc - so as long as their count equals zero, they are 'eligible' to fill that slot. B12 is the last person on the schedule.
View Replies!
View Related
Reference Changing Files In VBA
I have recorded a basic macro that sources data from a number of workbooks. If I change the filename in the workbook with the source data or move this file to a different folder path the macro does not run without errors. Does anyone know what vba code I can use to address this problem?
View Replies!
View Related
Dynamic Formulas- List Of Serial Numbers In A Column
i have a list of serial numbers in a column, say column a, for example, and i have corresponding data in columns to the right of this, say just column B for example- I also have a header on column B which counts the data in column b. (just a COUNTA, counting occurences of 'x' ) my issue is this: i need to limit the range of the COUNTA dynamically- by the number of serial numbers in column A. for example: if i have 10 serial numbers, i want my COUNTA range to be B1:B10 (excluding header) and... i need this to be a built-in formula, NOT a VBA macro. i need it to update automatically, as soon as another serial number is entered. i've tried using references to named ranges and all sorts of language tricks, and i cant seem to get it to work. (such as: ' =counta(b1:namedrange1) i'm going to have to do the same thing with COUNTIF's, so if that is also easily explainable,
View Replies!
View Related
Changing Column Letters In Formulas Based On Month
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View Replies!
View Related
Save 10,000 Hours On Manually Changing Formulas
This formula returns the production for a certain month: =+SUMIF(Line2!472:472,D5,Line2!18:18) I am needing to change this formula to show sales forecast... The formulas will be identical EXCEPT the 3rd part of the SUMIF will be 2 rows down. So this one would be :=+SUMIF(Line2!472:472,D5,Line2!20:20) How in the world can I take a formula, and change it for over 4704 entries? I actually tried this: =+SUMIF(Line2!472:472,D5,Line2!18+2:18+2) of course will no prevail.
View Replies!
View Related
Copy/Pasting Formulas But Only Changing Certain Values In Formula
I'm trying to copy and paste this formula to multiple cells and am having difficulty doing so without everything in the formula changing.. SUMIF(Bankroll!Q14:Q6000,U143,Bankroll!Y14:Y6000) I only want the U143 to change to U144, U145, etc. Yet when I copy and paste down the sheet it changes the Q14:Q6000 and the Y14:Y6000 values as well. I've been having to go through and paste the formula one line at a time and then manually change the U143 to the current U cell that I want it to represent in order to keep the rest of the values the same. Is there a faster way than this?
View Replies!
View Related
Formulas (Circular Reference)
I have 2 cells which I will select on a work book; (say A1 and A2). A1 is the input cell and the formula and the result generated is in A2. Suppose I input 3 in A1 and A2 shows 60, what I also want to do is input 60 in A2, which should show me 3 in A1, without destroying the formulas. In short both cells will have formula and both cells will be input cells for each other.
View Replies!
View Related
Formulas Reference Other Tabs
I want to have a summary tab calculate figures of other tabs. For example in "Summary" tab, I want to Sum (a:a) for tab "January" =January!sum(a:a) Does not work. I have also tried other options as well. Is it possible to use formulas when referencing other tabs.
View Replies!
View Related
Copying Formula - Dynamic Worksheet Reference
Is it possible to copy a formula and have the worksheet name increase incrementally instead of the cell reference? Example: In column A, I have the following - =TDI_SR_F_1!$G$1 =TDI_SR_F_1!$G$2 =TDI_SR_F_1!$G$3 I want to copy this across to column B, but instead of just copying that exact foruma across I am wanting a formula that will update the worksheet reference to: =TDI_SR_F_2!$G$1 =TDI_SR_F_2!$G$2 =TDI_SR_F_2!$G$3 TDI_SR_F_2 is the worksheet after TDI_SR_F_1. NB - I cant use find and replace function as it will change the '1' which is part of the cell reference ($G$1) to a 2 as well. I know I can use TDI_SR_F_3 as a search string, but the process is going to be extremely tedious, i.e. there are literally over 100 rows I need to do the find and replace on.
View Replies!
View Related
Reference Last Cell In Dynamic Named Range
In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est. This is because the number of roads changes for each project. I created a dynamic range name surfacing with the following formula: =offset(qty!$a$1,0,0,counta(qty!$A$A), counta(qty!$1$1)). My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.
View Replies!
View Related
Udf Vb Looking To Define A Dynamic Absolute Reference
I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column. I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns. Function SRCref2() Application.Volatile myfield1 = ActiveCell.Column Dim1 = Columns(myfield1).Range("a1").Value SRCref2 = Dim1 End Function Function SRCref3() Application.Volatile myfield2 = ActiveCell.Row Dim2 = Rows(myfield2).Range("a1").Value SRCref3 = Dim2 End Function I would nest these in a formula such as "=srcsum(srcref2(), srcref3())". Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum($a$2,$b$1)"."=srcsum($a$3,$b$1)"."=srcsum($a$4,$b$5)".etc. It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.
View Replies!
View Related
Adjust Formula To Allow For Changing Sheet Reference.
I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating. =IFERROR(INDIRECT("'"&COLUMNS($A$1:A$1)&"'!P"&ROWS($A$1:$A8)),"") I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect? =IFERROR(INDEX('1'!$A$8:$AT$115,MATCH($A3,'1'!$A$8:$A$115,0),MATCH("PAT",'1'!$A$7:$AT$7,0)),"") The sheet reference will change from '1' through '90'.
View Replies!
View Related
Copying A Formula Without Changing A Part Of The Reference
I have this If statement on one of the cells =IF(A7=AQ6,AQ32,IF(A7=AR6,AQ32,IF(A7=AS6,AQ32,IF(A7=AT6,AQ30,IF(A7=AU6,AQ29,IF(A7=AV6,AQ33,IF(A7=AW6 ,AQ33,IF(A7=AX6,AQ31,"")))))))) If I want to change only A7 to A8, A9, A10 without changing the other parts of the formula, how do I do that. I need to copy this formula in about 300 rows.
View Replies!
View Related
Relative Reference Formula Not Changing Until Re-Entered
When I copy the formula to another cell, It doesn't show the result. But when I double click on it, it works. For example there r values in A1 as "john" & A2 as "Jack". I write formula =A1 in cell B1. It shows "John". Now I copy this formula to B2, it shows "john" instead of "Jack" . When I double click on B2 & enter, it shows "Jack".
View Replies!
View Related
Function/Formula To Reference Changing Rows
I have a table of consolidated data that consists of the date at which certain payments occur and their amounts that other sheets need to reference to perform calculations. I am currently using the formula below to put the data under the correct date column. =INDEX(ImportedData!$B$10:$DB$10,MATCH(B3,ImportedData!$B$4:$DB$4,FALSE)). The problem that I have is that the INDEX range, currently B10:DB10, will need to change depending on the number of files that are consolidated ie with more files the row will increase.
View Replies!
View Related
If Statement Limit Workarounds: Convert Various Codes From One Column Of Spreadsheet Into Different Codes In Another Column
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts: =IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning","")))))) I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
View Replies!
View Related
|
TRACKER
