Here is my formula =IF($H$2<='C:\Documents and Settings\All Users\Documents\Budget \Finances\2010\[Budget_Estimation_Tys.xls]Sheet1'!$B$2,"Within budget","Over budget")
H2 is the Average per month calculation , this is correct and the formula is working correctly for averaging. B2 is the estimated budget amount for Groceries. How do I get Excel to recalculate the formula that will change B2 to the next cell reference that matches what I've selected from the drop down list from the other file? Maybe it cannot be done because I'm using two different worksheets? On Oct 13, 8:41 am, Paul Schreiner <schreiner_p...@att.net> wrote: > A lot is going to depend on what your data looks like and what > you're trying to accomplish. > > For instance.... > To ME, when you say that you want the "formula" to change, > my first interpretation is that if you select "Groceries", > you want the average, but if you select "Utilities", > then you want the sum... because THAT (to me) is the "formula". > > If what you really mean is that you want the CRITERIA to change.. > well that's different... > > Let's say that you have a pull-down list of criteria in cell H1: > Groceries > Utilities > Mortgage > Insurance > Auto > > and you have a list in A2:B100 that has things like: > Category Amt > Auto $53.00 > Utilities $66.00 > Auto $141.00 > Utilities $92.00 > Utilities $45.00 > Insurance $168.00 > Auto $202.00 > Auto $184.00 > Groceries $207.00 > Auto $65.00 > Groceries $119.00 > Auto $166.00 > Auto $172.00 > Utilities $173.00 > > Now, if you're wanting to calculate the average of the selected criteria, > you could use: > > =ROUND(SUMIF(A2:A31,H1,B2:B31)/COUNTIF(A2:A31,H1),2) > > is this the kind of thing you're looking for? > > Or am I totally off-topic? > > Paul > > ----- Original Message ---- > > From: Susan <susan.m.ander...@comcast.net> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > > Sent: Tue, October 12, 2010 3:36:04 PM > > Subject: $$Excel-Macros$$ Incorporating a List into a formula - Excel 2007 > > > Hi, > > wondering if someone can help. I'll try to be as specific as I can. > > > I want to have a formula that will change according to the Category > > word I select from the drop down selection function. Otherwise I have > > to copy and paste the formula I have now and manually enter in each > > different category word. > > > the formula I have now is an AVERAGE of two cells (using the same > > category word lets say Groceries), within the same sheet and I want > > the formula to change when I select from the drop down selection > > function from the category (LIST) cell. > > > thanks, > > Susan > > > -- > >---------------------------------------------------------------------------------- > >- > > Some important links for excel users: > > 1. Follow us on TWITTER for tips tricks and links : > >http://twitter.com/exceldailytip > > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > > To post to this group, send email to excel-macros@googlegroups.com > > > <><><><><><><><><><><><><><><><><><><><><><> > > Like our page on facebook , Just follow below link > >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&;... -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
