Automated Entry In Cell Instead Of Manual (without Causing Circular Reference)
Apr 22, 2008
I need to make cell A1 = cell D1.
Cell A1 is calculated by entering a number in cell E1[COLOR=blue ! important][COLOR=blue ! important][/COLOR][/COLOR]. Due to the various formulas used, when cell E1 goes up, the value in cell A1 goes down (and vice-versa - When E1 goes down, value in A1 goes up). Cell D1 is calculated using formulas UNASSOCIATED with A1 or E1.
I can't enter a formula for cell E1 to do the calculating due to the circular reference created.
I need some type of code that will automatically figure out what number needs to be in cell E1 to make cell A1 equal Cell D1 without creating the circular reference.
View 9 Replies
ADVERTISEMENT
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
Mar 14, 2007
I have an action that deletes a row, and decrements rows counter by one. But, if the user manually deletes one row, I don't get that information (decrementation). Workbook pretection is out of the option.
What are my choices?
I was thinking, is it possible to capture a delete event in general, and add some code to it? Or maybe even completely disable it?
View 9 Replies
View Related
Apr 3, 2012
I am using the below code to enter the data in Cell "D" & "E" of the worksheet.
Dim a, b As Integer
a = WorksheetFunction.CountA(Sheet2.Range("C:C"))
ActiveWorkbook.Sheets("Retailing Data Sheet").Activate
'If Range("C" & a + 1).Value "" Then
[Code] .......
But, If someone wants to enter the data manually into the Cell "D" & "E" its allowing which i dont want.
It should be enter by using the form only...
View 5 Replies
View Related
Jun 12, 2007
Workbook attached. Workbook contains 53 sheets. 52 sheets are identical and represent one sheet for each week of the year. At the bottom of each column is a SUMIF function that member JimFuller1 showed me how to use. The 53rd sheet is where I would like to calculate all of the totals (per item and per first-aid kit) I tried using the same SUMIF principal, applying it to all sheets but I keep getting a circular reference. I asked a coworker of mine and he said that it was due to the fact that the cells I am trying to put in the SUMIF function allready are part of a different SUMIF formula.
View 3 Replies
View Related
Jan 11, 2007
if this is possible without becoming a circular reference.
Aim: to have a Macro that takes user selected cell and converts it the result and place it back in the same cell.
Example: could have a typed value in A10 say 500, and when the cell i selected and the Macro is run is converts the value by B10 (.5) and places the value back into A10. ie A10= A10*B10 (250)
I know this is a circular reference but could the macro say take the Value A10 and hold it in a temporary string of some sort before calculating and then returning the new value in there?
View 9 Replies
View Related
Mar 16, 2009
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
View 4 Replies
View Related
Aug 31, 2012
I have a situation where I need a macro to reference another cell in the sheet if the cell is left blank. If the user wants to edit the value, they have the option to manually input a value but if they decide to leave it blank, it autopopulates the referenced cell.
View 1 Replies
View Related
Mar 29, 2007
I would like to use a combobox and I want to force the user to selected one item from the list, and not to be able to type anything in it.
My other solution would be to use a listbox but I like the combobox design better...
I found this code in a MSDN Forum but somehow, it does not seem to work.
Maybe it is because I do not know what to do with a "public class", or my Excel 97 does not support this.
I tried pasting the "private sub" in a sheet code (where my combobox is), but it did not work.
Public Class Form1
Private Sub ComboBox1_KeyPress(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.KeyPressEventArgs) _
Handles ComboBox1.KeyPress
e.Handled = True
End Sub
Is this code actually working under excel 97 and if so, where do I have to put it?
View 3 Replies
View Related
Sep 27, 2011
I am relatively new to Excel. I have figured out how to use named ranges as lists and use INDIRECT Data Validation to have a list's selection made in one cell populate a new list in an adjacent cell.
A1 contains a list of "Characters" (The choices are "Numbers", "Letters", or "Other")
Based on the choice in A1, B1 has to populate with the following:
- A list of available Numbers to chose from (1,2,3..etc)
- A list of available Letters to chose from ( A,B,C...etc)
- Text that instructs the user to enter text into the cell manually
(For example, their "Other" character might be something like "@#$%^&*!". (Not unlike my mental state trying to figure out this problem.) I want them to see the instruction to "ENTER CHARACTERS HERE" and then be able to type in the cell.
So I need B1 to either populate with a menu that is dependent on the menu selection in A1, or allow for free text entry depending on if they chose the "other" option in A1.
View 4 Replies
View Related
Mar 21, 2009
how to protect my spreadsheet from data entry errors.
I have a column of data that must be entered manually (column B). Sometimes, the entry in column B could have been calculated by running a formula run upon the data in column A. Sometimes it has to be entered manually.
Is there a way to use data validation or conditional formatting to make sure that the proper figure is entered into column B when it could have been calculated by a formula to avoid data errors?
View 9 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
Apr 13, 2012
I have it all set up already with macros and formulas and such. My next step is to record the sales data ( selected cells ) from the invoice to a seperate sheet I call Cashup. The idea being to record all relevant sales data for the day/trading session on one sheet so the manager can cash up the till run by 2-4 different people during the day. Its targeting a small family run business so security isn't currently the main concern although for future proofing I'm trying to build that in as far as possible. Long story short: What would be a good method for recording each successive sale's data from the same page each time the Finish button is hit , to a seperate sheet in a log format? Currently I have these problems:
1:The operation should record into the next open row on the Cashup sheet and not overwrite the previous record.
2:The record must ignore all formulas and record only the values in the cells . Currently a normal copy and paste results in " N/A" recorded in the cells in the Cashup screen.....( probably since all the data that needs recording is formula generated to begin with )
View 3 Replies
View Related
Apr 10, 2008
I have a set of data which is the total monthly sales whereby each customer has several entries.
I have created an automated invoice through a vlookup system, yet i encounter the problem that i need to return each entry for the same customer into the automated invoice. But my vlookup will only return one entry for each customer as it is looking up the customer code and just brings back the first one it encounters, is there any way to bring back all entries for each client?
View 9 Replies
View Related
May 9, 2008
I'm trying to update a cell with today's date based on the choice from a drop-down list in the neighboring cell, but only if the cell is empty.
Source list for cell B2:Rcvd
Passed
Returned
Flagged
If Rcvd is chosen from the drop-down list, put today's date in A1, but only if A1 is not already populated. Don't do anything if any of the other 3 options are chosen.
I can't seem to use the A1 cell in a conditional statement to check it's status without creating a circular reference.
Trying to do it without resorting to VB.
View 9 Replies
View Related
Jan 7, 2007
I have formulas in wksheet #1 going down 20 columns (they are all the same formulas). On wksheet #2, I need to understand how to switch all column references in only one column to another (from wksheet #1) to show different sums. Clear as mud? The way I want to do this is by having one cell on wksheet #2 where I change a number from 1 to 20. This in essence would be the column heading in wksheet #1.
Example:
*There are more formulas but for the purpose here I'll use 3 for each Wksheet
In wksheet #1 I have
C24 is =SUM(C14*C15,C16*C17,C18*C19,C20*C21,C22*C23)*12
C55 is =IF(B66=0,"",C7/B66)
C56 is =IF(C11=0,"",C7/C11)
In wksheet #2 I have .......................................
View 9 Replies
View Related
Jun 25, 2009
Let's say I have a sheet called Category. Column A contains code; column B contains description. Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given. I thought I might be able to do something like:
in F2: IF($G2<>"",INDEX(Category!A:A,MATCH($G2,Category!$B:$B,0)),"")
in G2: IF($F2<>"",INDEX(Category!B:B,MATCH($F2,Category!$A:$A,0)),"")
(and then copy the formulae down the rest of the columns of course). In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about.
However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX($G:$G,ROW())<>"",... but it still doesn't work.
This sort of situation must crop up from time to time. How do we get around it?
View 3 Replies
View Related
Apr 20, 2007
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 9 Replies
View Related
May 20, 2009
I need to get .02 of a total.
But I need to add this number without getting a circular reference.
View 9 Replies
View Related
May 17, 2007
i got three cells namely; M7, AC7 and BA7. Initially, an input value is entered in cell M7 which will be processed to give AC7. An IF function is entered in cell BA7. This IF function is really simple. It states that IF the difference between the values of M7 and AC7 is greater than 1 then, decrease 0.01 from M7, otherwise, retain the value of M7. The syntax is:
=IF((M7-AC7)>1, M7-0.01, M7)
As I told you, it is really simple and looks fine. I have no problem with it so far. The problem came about when I tried to make the circular reference. I am trying to make M7 to equal BA7. Well, before I made the circular reference, I made a manual entering of the values of cell M7. What I have done was that I put a value like 29.94 in M7 then I let the calculation to proceed. The the value that I get in BA7 (i.e. 29.93), I re-entered it in M7 then I repeated the process until I got a value (i.e. 29.90) that was not changing due to the use of the IF function. The manual calculations are shown in Table2 of the attached file.
I said to myself instead of entering the values manually, I can use a circular reference so that the cell M7 = cell BA7. Unfortunatelly, the circulation of the values instead of giving me 29.90 which is the right answer, it gave me 29.89 which ment that Excel has done one more step further of the IF function. In other words, Excel should have stopped decreasing 0.01 from M7 when M7-AC7 was less than one. This is achieved at M7=29.90 (as clearly seen in Table2). However, Excel went one step further and done: BA7=29.90-0.01=29.89 then it stopped subtraction at 29.89 which is not correct.
View 9 Replies
View Related
Sep 25, 2007
I've often lurked in the background here and have found great solutions to my problems from the ozgrid FAQ's and email outs.
I have a custom VBA formula that has a heap of ranges as inputs. I loop through one range then perform calculation on the other ranges in the formula.
The formula is to work on 2 sets of data, 1 with more columns than the other. I've set Optional variables for the unnecessary columns.
For some reason I keep getting circular references on the 2nd set of Data.
I've attached the spreadsheet (stripped down) to show both examples.
I'm absolutely confused why the "teaching" one calculates correctly and "support" gives a Circular reference error.
I've set the Iteration in options and this corrects it, however I'd like to know why one works perfect and the other fails?
View 9 Replies
View Related
Aug 9, 2008
How do I add the "Circular Reference" permanently to my excel tool bar? Every time i need to use this tool I have to do the following:
-tools
-customize
-check the "circular reference"
-close
..it appears and I can use it. Then after i type something in another cell it disappears again! I would like it to ALWAYS be in the tool bar as a standard item even when opening a new book/ sheet or whatever. I want it to be a standard tool.
View 4 Replies
View Related
Dec 31, 2009
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!
However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?
To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?
View 3 Replies
View Related
Dec 8, 2013
Several places on my spread sheets I need to compare an action date to today's date.
I use: [Code] .....
in one cell and the fomulas to compare a date in a different cell to that cell.
I get the message "Circular References" for the cell the the above "today()" formula.
I do not get circular references in the cell that refer to the today cell, just the today cell.
View 4 Replies
View Related
Mar 2, 2014
This formula might be the problem for the error message.
SUMPRODUCT((MOD(COLUMN(C3:CO3)-COLUMN(OFFSET(C3:CO3,,,1,1)),3)=0)*C3:CO3)
I have other cells with the same formula (but with different range i.e. D3:CP3), yet it doesn't have circular reference error message.
View 2 Replies
View Related
Nov 3, 2009
I have these cells:
http://img194.imageshack.us/img194/427/excelj.jpg
The two center cells are the average of the top, bottom, left, and right cells. Of course the two center cells will therefore reference each other.
I enabled iteration in Excel options but it will not calculate the answer. I am getting a #value error. I checked the cells, the equation is right and they are all numbers.
What am i doing wrong?
See the work book. I had to rewrite everything manually to get it to work (But I have a HUUUGE matrix and rewriting everything will take forever) Please look at the work book and tell me why one group of cells is returning answers and hte other is returning a #value error.
View 14 Replies
View Related
Aug 24, 2011
Is there anyway to create a circular reference between three cells, and still enter text freely into any of the cells?
I've speculated that it might be possible by utilizing three or more other cells slaved to the first three, and simply kept off to the side.
To give a better understanding of what I'm trying to do, I've created a finance spreadsheet for our shop's snack bar, and below is a screenshot of a portion of said spreadsheet.
As you can see, all of the formulas are set up to where Columns F, G, and H are directly affected by what I put in Column E, but I don't want to be limited to just Column E; I want the other users and I to have more freedom than that.
Once again, my goal is to set up a 3-cell circular reference in which data can be typed into any of them. I've considered setting up a macro, but those are only active temporarily, and I would prefer the calculations to be made in real-time.
View 2 Replies
View Related
Sep 4, 2009
I'm at my wits end trying to work out why I'm getting a circular reference when this code runs:
Private Sub TextBox1_Change()
Cells(Rows.Count, "K").End(xlUp).Offset(1).Value = Range(TextBox1.LinkedCell).Value
With Range("K5").Resize(40, 1)
.FormulaR1C1 = "=OFFSET(" & Cells(Rows.Count, "K").End(xlUp).Address(True, True, xlR1C1) & ",-(ROW(RC)-ROW(R5C)+1),0,1,1)"
.Value = .Value
End With
End Sub
The circular reference cell is K29. I have an "X" in K46 after which all the values in TextBox1 are copied.
View 9 Replies
View Related
Jan 10, 2010
if i enter a value in 1 cell and want it to increase by a percentage how can this be done without having a sum column etc
View 9 Replies
View Related
Feb 25, 2008
I'm looking for a macro that changes Iterations from 100 to 10. I have a circular reference that I can't seem to completely remove, so my endusers complain about the pop-up warning. I know I need to resolve the circular ref issue, but in the meantime I would like to create a button that makes it easy for an enduser to change the Iterations with one click. A secondary question is, "Am I sacrificing significant accuracy by limiting Iterations from 100 to 10? I'm dealing with $'s, so the nearest penny is good enough.
View 2 Replies
View Related
