I've attached a sample. I'm attempting to make a formula to identify low quantities 7 or less and consolidated them to the stores who've sold the most. I would like the formula output the store who's sold the most. I've tried to use if,vlookup,hlookup,max combination and keep getting NA returns.
View 3 RepliesI have encountered some difficulty in modifying a macro I wrote into what I need. I created a macro that searches a column (Column C) for a cell value of, "stop", and then it copies everything above that cell and pastes it onto another sheet. In the sample data set that I was using, "stop" first occurred in cell C541, so the macro copies C1:C540 and pastes it onto another sheet. The problem is that the macro created an absolute reference to C540. What I desire is for the macro to use the 'Find' function to locate the first occurrence of, "stop", offset one cell above that cell, and then reference the active cell (which was positioned by these last two steps) in the range that should be copied. Basically, I'm hoping to have cells C1 through the active cell copied and then pasted onto another sheet.
Code below.
Sub FAIL()
'
' FAIL Macro
'
'
Sheets("Reformatted").Select
Columns("C:C").Select
Selection.Find(What:="stop", After:=ActiveCell, LookIn:=xlValues, LookAt _
[Code] .......
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.
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies View RelatedI have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
Is it possible to reference a cells value to define a range reference?
[Code] ......
I am trying to define the row value in the range reference with a value in a secondary cell?
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C
As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.
I also need E (which has also shifted over one) to sum A,B,C, and C2.
Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies View RelatedCan 3-D reference be used in the following formulas:
SUMPRODUCT
MATCH
IF
INDEX
Please refer to attached sheet.
I have monthly data for 3 stores as shown.
I want to know if any data exsit at certain cell location.
See data in Cell X1:Y4
For Store1:
I want to check if any data in cell reference (10,12), where 10 and 12 is variable and values will be in cell Y2 and Z2 respectively and it will change month to month.
If I have data in this cell reference, then AA2=TRUE, if no data then AA2=FALSE
For Store2:
I want to check if any data in cell reference (110,12), where 110 and 12 is variable and values will be in cell Y3 and Z3 respectively and it will change month to month.
If i have data in this cell reference, then AA3=TRUE, if no data then AA3=FALSE
For Store3:
I want to check if any data in cell reference (210,12), where 110 and 12 is variable and values will be in cell Y3 and Z3 respectively and it will change month to month.
If i have data in this cell referance, then AA4=TRUE, if no data then AA4=FALSE
I'm trying to get E7 to become E8, E9 etc as i fill the formula down, but E369 must remain E369 because that is the cell on sheet $R$2 (which is called "2008") is the last cell with information in it.
=SUM(INDIRECT($R$2&"!E7:E369"),$E$4:E6)
It seems like with the indirect function, whatever is in there just doesn't want to change anymore.
I have 2 workbooks. 1 is a summary report and the other is the raw data.
The raw data has a list of data thus: (There is more data but for your purposes)
Neil 80%
Neil 90%
Joe 100%
Joe 60%
etc...
The summary table is supposed to pull off the percentages and display them nice and neatly, but transposed horizontally rather than vertically.
I need a formula that says something along the lines of (IF a1:a50 = 'Neil', then b1:b50 = c1) where c1 would contain the 80% thing.
Anyone have a way for me to do this?
I would like to fill down references in a column this way: =A1 =A2 =A3 consecutively. Is there a way to do this?
View 7 Replies View RelatedI've got the following formula that I want to tweak. As you can see from the formula, Henning is the name of the tab on the other workbook. If I were to add a column on the spreadsheet that has this formula, and put "Henning" in it, is there a way to use that cell in the formula so that it would still open the Henning tab in the other workbook? I'm trying to avoid having to modify each and every formula.
View 2 Replies View RelatedI am using a multi-tab spreadsheet in which one tab has an outside-generated report dumped in as the input, with other tabs referencing its data. The Input tab has two sets of near-duplicate data, and I need to reference the second set, which begins in different rows for each Input report. I can't do a specific vlookup, because the reference appears twice in the tab but I need the data following the second reference. Can I name a range, beginning at a changing point (the cell which reads "Exposure:Net") and continues to the end of the tab, and have functions referencing it work properly?
View 9 Replies View RelatedI have a spreadsheet, that I have been using for years to estimate jobs. I started learning excel, by building this sheet. It is still a work in progress, as am I. I am always trying to figure out ways to make my life simpler by making the sheet more intelligent. I would like to be able to go from one sheet, to another and click on a part name, which would return a value associated with that part name, to an additive formula on the first sheet, to build and assembly. And, eventualy build a parts list from it as well. For ordering parts.
View 7 Replies View RelatedSheet 2 contains my checking ledger and sheet 1 is my summary page that I would like to have show current balance from the ledger. Is there a way to reference a value from sheet 2 on sheet 1?
View 3 Replies View RelatedIs it possible to reference to a cell's value only?
I am using =TODAY() formula to display current month's day number but it does not work properly if I use that cell in another calculation,so is it possible to reference to a cell's value only (in order not to copy it manually) or is there maybe another formula to convert month's day number to numeric value?
How can get the referencing cell based on cell name ?
like, i have updated "MyCell" as a name to Column 'A'
ActiveWorkbook.Names.Add Name:="MyCell", RefersToR1C1:="=Config!R2C2"
So, now I want to get the referencing cell by passing cell name("MyCell") ?
So how can i get it ?
I want to arrange a dataset in blocks of 8 lines and for this I need to refer to the actual row of the active cell. Below code does not work - the "Row" does not change as the active cell moves down through the rows
View 5 Replies View RelatedCheckbox reference. I am using the following code
View 4 Replies View RelatedI recorded this sumif in VBA, but know I am trying to figure out how to tie in the last row reference in place of the 27.
=SUMIF($AR$2:$AR$27,$AR29,AS$2:AS$27)
FormulaR1C1 = "=SUMIF(R2C44:R27C44,RC44,R2C:R27C)"
I have a userform that presents the user with 4 choices and once they click on one choice a macro runs that filters and formats the data and when the user clicks on the button start over it will reopen the file they were just using prior to the changes occurring. The reason for this is the same spreadsheet is printed up to 4 times with different information (remember the part where the data was filtered?). So basically the user clicks on option 1 the macro runs formating the page and then the user clicks start over (different userform then the first one) and the spreadsheet closes and reopens before the changes.
The way I have excel reopening the file now is by having the user input the file name into a text box and clicking OK this passes the file name to the open code in VBA. How can I tell the code the name of the sheet after the sheet is no longer active (because a userform was made active)? Basically the end goal is for the user to click over and excel will just reopen the file without any user input.
I have a defined name that holds the names of each relevant worksheet I want to calculate.
Lets Say I want to sum every A1 in each worksheet in the defined name. How would I go about doing that?
I'm using a few worksheets to make balance sheets. I use a connection to a database. Worksheet1 contains the opening balance amounts per account.
Worksheet2 has a balance sheet. Because the database contains more than one administration I use the sumproduct function so I can use multiple criteria.
The formula I use is:
=SUMPRODUCT((Worksheet1!A$1:A$4000=Balance1!$A$2)*(Worksheet1!C$1:C$4000=Balance1!A4)*(Worksheet1!G$ 1:G$4000))
One of the administrations has 3 new accountnumbers. These were added to the database. When I refresh the data Excel adjusts the formula and adds 3 rows to the ranges, except the last range. The formula then looks like this:
=SUMPRODUCT((Worksheet1!A$1:A$4003=Balance1!$A$2)*(Worksheet1!C$1:C$4003=Balance1!A4)*(Worksheet1!G$1:G$4000))
Because the last part (Worksheet1!G$1:G$4000) is not adjusted, Excel returns a #N/A error. What is wrong with this formula? It works great as long as no accountnumbers are added to the database.
I am using the following code (thanks to Leith Ross) to display a row of cells in a messagebox based on the row number entered by the user via an input box;
View 7 Replies View Related