In the attached example, I need to create a formula for cell I2 (Balance Remaining) that is driven off the most recent calculation (in this case, F68) but still allows for additional rows to be added to the ongoing list of orders. I believe I'm supposed to use an index formula but not sure how to write/apply it? I need to be able to add rows past 68 and still have Cell I2 (Balance Remaining) continue to calculate correctly.
View 10 RepliesI have a spreadsheet that evaluates cells against eachother and if they don't match I either delete the whole row, or delete up. When I do this, I have to redo the formula as I get an error.
So I start with: =EXACT(A1,G1).... and once I delete a row or delete "up", I get =EXACT(#REF!,$G3).
I have tried =EXACT(INDIRECT(A1,G1)), =EXACT(INDIRECT($A1,$G1)), and also =EXACT($A1,$G1).
None of these work.
I'm writing a custom function, and in the function i want a reference to the cell that the function is entered in, how do i do that?
View 7 Replies View Relateddo you know if there is vba code to lock formulas so someone sees but does not touch and the same for values see but cant change??
my sheets with formulas are SUMMARY and Sheet2
sheet with value is TABLE
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?
This may be an easy answer, but I haven't been able to figure it out. I have a cumulative page of formulas that reads "SHEET1!B8+SHEET!B9". I need the next row formula to reference "SHEET1!B47+SHEET1!B48", and then "SHEET1!B86+SHEET1!B87". Each row the formula moves 39 rows on sheet 1. Is there a way to get excel to automatically do this, so I don't have to go line by line?
View 9 Replies View RelatedHow do you lock a column of formulas so you can't change the format by cut and paste? Also, the same for data, locking data so it cannot be overwritten in Excel 2003 and 2007.
View 4 Replies View RelatedThe spreadsheet works in Excel 2000 when you open it in any newer version it is corrupted. Which is fine so we are rewriting it in Excel 2010/2013.
We have data in columns A:E which comes from the refreshable query. Data in F:I are different formulas based on the data from the query. The number of rows we get will vary from query to query. The headers never change just the data. How can we make it so that when no matter the data length the formulas will always autofill up or down depending on the data length?
I'm preparing an accounting model for my office use. How to solve the copying of formula to all other cells.
The detail is here:
Sheet 1:
1) I've entered a number 1000 in L2
Sheet 2:
1) I've entered a values in columns D,E,F,G
2) Calculations :
at G2 : the formula is =D2*Sheet1!L2
The problem is when I Copy the formula in G2 through G3, G4, G5........... it changes to =D3*Sheet1!L3, =D4*ValidData!L4, =D5*ValidData!L5 and so on... but it should be =D3*Sheet1!L2, =D4*ValidData!L2, =D5*ValidData!L2, so that the L2 value shall be constant for calculations in all cells.
So I am trying to create a budget worksheet for past and future expenses, basically an excel checkbook with running balance
In Column A I have my debits/credits
In Column B I have my running balance so that there is a current total next to each of the debit/credits in column A, it is formulated as follows, it adds the cells directly above it to the cell directly to the left of it (i.e. Cell B10 = B9+A10, which was dragged and iterated to many cells below)
I wan to be able to drag in drop cells in the A column (Just moving them up and down different cells in the A column) with out it changing formulas in the B column.
I tried the $ trick so that for example B10 = B9+$A$10 but if I moved cell A9 to say A10, I get a reference error in cell B10 (why is it doing this when B10 is locked with $)
I then tried going to format and locking the B column and not the A column and then protected the document but I still get the same REF error.
I know I can copy and paste with out effecting it but this is tedious, the whole reason I did this was so that I can move around bills EASILY and play around with what get paid when. There has to be a way to do this, but I'm probably searching with the wrong keywords.
I have a SS that puls data from another sheet using the following formula
=IF(OR('Site Visit ASSESSMENT'!D9={"PARTIAL COMPLIANCE","NOT COMPLIANT","NOT ASSESSED","ADDITIONAL INFO NEEDED"}),'Site Visit ASSESSMENT'!A9,"")
I also have VB Code in teh sheet as follows to hide rows. If I protect colum A and protect sheet I get an error. How can I protect the sheet and still have the following commnad work?
Private Sub CommandButton1_Click()
'Toggle rows hidden where cells in column D are blank
If ActiveSheet.AutoFilterMode Then
[Code].....
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.
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.
I would like to drag down the formula from the last cell in Column A in reference to the last row in column B.
E.g My last data in Column A104 (code to select the last cell and this can vary from time to time the report is updated)
Column B has data till B142. I want to drag A104(contains formulas) down to A142.
Here is what I have but Selection Autofill doesn't work.
Range("A2").End(xlToRight).Select
Range("A2").End(xlDown).Select
Selection.AutoFill Destination:=Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault
On choosing Auditing Funtion, Trace Dependants, a small icon representing a spreadsheet ? appears at the end of a dashed line. What does this refer to?
View 3 Replies View RelatedFormula: B2+C2
In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.
As it stands I can only get the formula to calculate within the same row.
I'm actually having difficulty with the sumproduct function, I'm trying to count items based on multiple criteria (and yes I have looked under that). I'm using a very helpful formula that I grabbed from this site. =SUMPRODUCT(1*(base!$B$1:$B$19465=locations!B$1),1*(base!$A$1:$A$19465=locations!$A6))
The problem that I have is that this doesn't work if I replace $B$1:$B$19465 with $B:$B. now as the number of rows on the base sheet may change, this means I need to change the formula each time I change the data on the base sheet which is somewhat ridiculous. I've been playing around with indirect trying to reference the last cell in a range, but I don't seem to be getting anywhere with that.
I want to be able to sort a table alphabetically by one column after adding a new row to the bottom. But there is another table that uses the values of the former table. How can this be done without screwing up the formulas in the second table.
View 5 Replies View RelatedHow do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.
View 6 Replies View RelatedI have a 2 columns (A and B) next to each other that is using a Sum formula and other multiplying formulas. They reference 3 cells in a different column (C) to come up up with the answer for A and B. Therefore A1 and B1 reference C1 C2 and C3 (and possibly if want to use in future column D with D1 D2 D3).
Then in A2 and B2 I want to reference from C4, C5, C6
Then in A3 and A4 I want to reference from C7, C78, C9 etc.... and so on...
Instead of retyping formulas for each row in A and B, I want to copy down, but I getting the wrong answer when I do that.
I am therefore assuming I typing the formula wrong. How do I type it correctly so the cell reference changes automatically when I pull down columns A and B?
Formula for Column A: =SUM($G28:$G30)
Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17
Therefore next row should be referenced from G31 to G33 and H31 to H33
But when I copy it only adjusts it for 1 reference down.
I have 2 sheets in a workbook where i would like cell G8 on sheet 1 to equal the value on A11 sheet 2 & G9 to equal A12 and so on down the sheet. The problem is that G8 are 3 merged cell so every time I try to use autofill the reference number jumps by 3. The formula in cell G8 is
View 3 Replies View RelatedI 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.
I have a series of formulas in Row 1 across the columns in worksheet A. I would like to copy these formulas to a single column and down rows in worksheet B while retaining the references to worksheet A. I know that I can manually enter the references in worksheet B but that would take a long time to do.
View 1 Replies View RelatedI 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
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?
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View Related=IF(A4='Exported Data'!B2,'Exported Data'!C2,"")
How can I lock A so when I copy over to the right the formula doesn't change that reference to B4?
Is there a way to have the True value (Exported Data'!C2) reference one cell over instead of selecting the cell to reference from?
Need code that would make cell J2 be populated with U no matter what was placed in cell J2 either manually or externally.
View 9 Replies View RelatedI can have amacro which will lock each cell where a "Y" or "N" is inserted........ eg the netire sheet is unprotected but the moment Y is inserted in C 5 the cell gets locked, next entry in D 10 has a N entry and that too upon entry get's locked
The idea being once Y or N is typed that cell can no longer be changed
Currently have a column of cells that after the data has been entered we would like the cell to lock to prevent changes. The sheet is currently protected with the cells in question unlocked (D173:D250). We would like to be able to change the data if we unprotect the sheet. We would also like to be able to add/delete cells (list) in the code as needed.
View 9 Replies View Related