Reference Not Adjusted
Oct 9, 2008
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.
View 3 Replies
ADVERTISEMENT
Jan 17, 2008
i have a due date column and an adjusted due date column. a proof is allowed to be held 3 days, if it is held longer, the due date is adjusted. for every day over the allowed days that a proof is held, we are allowed 2 more days of production. i want to create a formula that will calculate how many EXTRA days the proof was held, multiply that by 2 and then add that many days to give us the adjusted due date. is there a way to do that?
View 10 Replies
View Related
Feb 7, 2014
Here is a table with values from Factor1 to Factor 5. Underneath that, for a specific index, I can mark Y or N for factor values. For instance, for index 1, Factor1 and 2 is marked as Y. The goal is to calculate total factor based on variable and factor values. In this case, variable value is 6, factor 1 =1 and factor 2=1.25). So Total factor = 6*1.25*1.5 = 11.25. If all factor values are marked as N, then total factor = variable value (Example is for Index 3)
How this can be implemented. I tried using COUNT function to count the total number of Y but that works only if all factors have same values.
Table_factors.jpg
View 4 Replies
View Related
Feb 26, 2009
I'm creating a spreadsheet that has:
- budgeted dollar amount
- entry each week for that week's total expense
- total expense to date
Question: how can I hold the value for "total expense to date" so that when the new week's info is added the previous total is not lost?
View 13 Replies
View Related
Oct 30, 2007
i'm trying to use HLookup to find an adjusted midterm grade that's given. but i have some conditions:
If student missed exam and has a zero – keep zero.
If student has a grade of 1-119 points, increase their grade 40 points.
If student has a grade of 120-125 points, increase their grade 35 points.
If student has a grade of 126-131 points, increase their grade 31 points.
If student has a grade of 132-139 points, increase their grade 27 points.
with these conditions, if my midterms grade is 120, how would i calculate it using HLookup? i worked on it but i keep getting the #NA! error.
=H4+HLOOKUP(H4,B24:D25,2).
View 5 Replies
View Related
Feb 4, 2014
I 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] .......
View 4 Replies
View Related
Mar 6, 2008
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 2 Replies
View Related
Mar 11, 2009
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 Related
Feb 15, 2010
I 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.
View 14 Replies
View Related
Jul 14, 2014
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?
View 3 Replies
View Related
Jul 20, 2014
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?
View 2 Replies
View Related
Mar 26, 2009
=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!!
View 9 Replies
View Related
Jan 8, 2012
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?
View 4 Replies
View Related
Jun 11, 2008
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.
View 9 Replies
View Related
Aug 14, 2006
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
View 3 Replies
View Related
Apr 25, 2014
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 Related
Jun 10, 2009
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 Replies
View Related
Jul 25, 2008
Can 3-D reference be used in the following formulas:
SUMPRODUCT
MATCH
IF
INDEX
View 9 Replies
View Related
May 10, 2014
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
View 4 Replies
View Related
Nov 11, 2008
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.
View 11 Replies
View Related
Apr 20, 2009
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?
View 14 Replies
View Related
Aug 10, 2009
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 Related
Sep 2, 2009
I'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 Related
Dec 21, 2009
I 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 Related
Jul 24, 2013
I 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 Related
Aug 6, 2013
Sheet 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 Related
Jan 27, 2014
Is 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?
View 2 Replies
View Related
Jun 3, 2014
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 ?
View 11 Replies
View Related
Dec 18, 2008
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 Related
Mar 11, 2009
Checkbox reference. I am using the following code
View 4 Replies
View Related
