I have changed this formula: =COUNTIFS(Apr!D:D,"Car Ins",Apr!H:H,"sale")
To: =COUNTIFS(Apr!$D2:$D200,"Car Ins",Apr!$H2:$H200,"sale")
I have made the change in the formula bar and pressed enter, the formula then appears as text with no calculation.
I am trying to make the references absolute as I need to copy/paste the column D5:D18 to other columns.
Is there a way to do this with copy/paste or do I have to retype all the formulas?
under settings, is the calculation set to automatic?
AND, you also have copy >> paste special >> formulas.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Where do I find settings?
click the file tab at the top, then (I think) it is options (the problem is I'm home and using excel for mac and it isn't the same as excel for pc).
Ok I think I found it under excel options/advanced/formulas.
Yes checked for automatic.
using excel 2007 there are tabs at the top of the sheet. The first tab is called "file", click that tab, then the settings for excel are there.
one of the first settings (again I think because i'm using excel for mac) is calculations. Under calculations should be a setting for formulas that should be set to automatic. If it is set to manual the formulas will only calculate when you hit save.
Nope dont see it.
Capture.JPG
click on that little round multi colored disc just to the upper left of "Home". I use 2010 at work and the file is right beside home, I forgot what 2007 looked like.
OK thats where I was its excel options/formulas auto is checked.
so the formula, =COUNTIFS(Apr!$D2:$D200,"Car Ins",Apr!$H2:$H200,"sale") doesn't show a little ' in front of it does it? That will stop it from working.
Otherwise you may have to post a small sample sheet that contains it to get better help.
Make sure the cell format didn't get changed to TEXT.
Format the cell as General (or Number)
Press function key F2
Then press Enter
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
OK I have attached a copy.
In the Sales tab under April C5, I am trying to copy C5:C18 , D5:18 and E5:18 to the other 11 months.
PO Sales 2015 - 4-5 - Dunferm. - Copy.xlsx
try changing this =COUNTIFS(Apr!$D2:$D200,"Car Ins",Apr!$H2:$H200,"sale") to this =COUNTIFS(Apr!$D2:$D200,B5,Apr!$H2:$H200,"sale")
Ok thanks for the input, I was having too many problems with it and eventually used Find and select to make the changes, Not the quickest way no doubt but quicker for me than re typing all the formulas.
The calculation options stands on 'manual' maybe you have to change it to 'automatic'
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
I did change it to automatic but kept getting issues no doubt due to my limited knowledge. Although you guys would probably find it cumbersome but using the `Find and select` function made it a lot easier for me to make the necessary changes.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks