How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9659
Experience:  Worked on Macs and PC's for 20+ years.
53785
Type Your Mac Question Here...
John D is online now
A new question is answered every 9 seconds

Excel spreadsheet colours

Resolved Question:

Attachment: 2011-09-14_133659_example.xlsx

I have a column with figures in, the bottom cell totals those figures above, this is all relevant to a car sells business. My question is, once a vehicle is sold, my business partner then colours one of the above cells (it could be any colour) to tell me he has put aside the VAT liability, so far so good. I now need a formula that will only add up figures that are not coloured in the above cells. Is this possible?
Submitted: 3 years ago.
Category: Mac
Expert:  John D replied 3 years ago.

Hi,

 

Yes that is possible with user defined function which uses vba code, provided you are not running Excel 2008 which does not support vba programming language

 

Please let me know which version of Excel you are running

 

 

 

Customer: replied 3 years ago.
Office 2011 for Mac
Expert:  John D replied 3 years ago.

Yes that will work

 

One moment I am reviewing the file....

 

Expert:  John D replied 3 years ago.

Ok here you go

 

http://www.2shared.com/file/_RCcg0Ab/2011-09-14_133659_example.html

 

(click the blue DOWNLOAD button at the bottom of the page)

 

The function is installed in cell G10. It will sum the values above that are not highlighted in yellow. Since changing cell color does not invoke re calculation in Excel I added another macro to force calculation when a cell is selected. So after you change the color of a cell simply click on any other cell to trigger the calculation macro

 

Please note that since there are macros in the file you need to make sure that you have your Excel is configured so that macros are allowed to run, otherwise nothing happens when you change colors

 

Hope this is ok. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution

 

Customer: replied 3 years ago.
Thank you John,

Sorry for the delay, I've been in a meeting,
I've examined the formula in cell G as you said, it works.
I copied that column and pasted it into my spreadsheet with no effect.(NAME?)
Perhaps this is something to do with configuring macros, how do I do this please?
Expert:  John D replied 3 years ago.

I wish you had sent me the correct file. You see the formula and code were setup for the specific file that you have provided. Is it working on that file?. If so thank you for clicking Accept while I send you the instructions for transferring the formula/code to other files

 

John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9659
Experience: Worked on Macs and PC's for 20+ years.
John D and other Mac Specialists are ready to help you
Expert:  John D replied 3 years ago.

Thanks

 

It will be easier if you can send me the new file so I can set it up for you directly on it

 

 

Ok here goes:

 

Assuming that in the new file the cells that need to be added are the same cells referenced in the original formula, here is how to copy the code/formula

 

Open the file that I sent you (file1)

Press Option + F11

Select all the code and copy it

Close file1

 

Open the new file (file2)

Press Option + F11

Click Insert > Module

Apply paste to paste the code in that window

Close file2

 

Open file1

Press Option + F11

Click on Sheet1(Sheet1) in the left pane

Select all the code and copy it

Close file1

 

Open the new file (file2)

Press Option + F11

Click on Sheet1(Sheet1) in the left pane

Apply paste to paste the code in that window

Close file2

 

finally copy the formula from cell G10 to the G10 in the new file

Customer: replied 3 years ago.
Hi John,

My apologies for the late reply again but I am trying to do this from Afghanistan and the internet is slow to bad to say the least.
I have just got this new Apple MacBook Pro and I don't even know what a 'Option key' is!
The file I sent you was a snip it of a worksheet I am using.
The sheet contains 25 of those items and 12 sheets in the work book as a whole.
Could I just send you the whole work book and you send it back to me as this is frustrating to me, I'm new at this John?
Customer: replied 3 years ago.
Further to this John, could you possibly contact me by email with the answer/sending of my spreadsheet on:-
XXX@XXXXXX.XXX

Thanks John
Expert:  John D replied 3 years ago.

This site does not permit contact by email (that's why your email address came through all X'd out)

 

The only sure way to get this done for you on the other file is to send me your actual file.

 

 

 

Customer: replied 3 years ago.
Hi John,

I have uploaded my file via 2Shared, I hope this is the correct method of doing it.
Each month contain 25 car deals and the formula I need is in column G, first one being G10 etc.
There are 12 sheets for each month of the year.
Sorry to be a pain John, I'd like this to be done properly to save me hours of unnecessary work hours.

Gratefully appreciated John.
Customer: replied 3 years ago.
Hi John,

This is the link:

http://www.2shared.com/file/WOpFNaIj/Gaz_Year_2011_V5.html
Expert:  John D replied 3 years ago.

Ok here are the formulas in all sheets (and the codes embedded in each of the monthly sheets)

 

http://www.2shared.com/file/pjSeJBR4/Gaz_Year_2011_V5.html

 

Hope this is ok. Let me know if you have any questions. Provided you are satisfied with the work, thank you for considering clicking the bonus button for the work on the second file with 12 sheets as well as the time for preparing the step by step instructions

 

 

 

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
< Last | Next >
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
 
 
 

Meet The Experts:

 
 
 
  • Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
< Last | Next >
  • http://ww2.justanswer.com/uploads/macthelife/2009-10-20_1899_mikesebaharsquare64.jpg Mike's Avatar

    Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
  • http://ww2.justanswer.com/uploads/AS/ashiknasameen/2012-5-15_141836_final2.64x64.jpg Ashik's Avatar

    Ashik

    Mac Helper

    Satisfied Customers:

    5282
    7+ Years of Experience in troubleshooting Macs, iPhone, iPad, iPod etc
  • http://ww2.justanswer.com/uploads/DP/dpean/2012-6-6_172828_avatorme1.64x64.JPG Daniel's Avatar

    Daniel

    Mac Genius

    Satisfied Customers:

    4670
    Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.
  • http://ww2.justanswer.com/uploads/VI/vinodvmenon2005/1.64x64.jpg Vinod Menon's Avatar

    Vinod Menon

    Support Specialist

    Satisfied Customers:

    2068
    worked as a Tech support Associate for Apple products
  • http://ww2.justanswer.com/uploads/BE/beboo/2011-1-14_201648_n5063313142021801763.64x64.jpg Brandon M.'s Avatar

    Brandon M.

    Mac Support Specialist

    Satisfied Customers:

    1501
    10+ Years Mac Support as contractor and currently an IT Manager for law firm
  • http://ww2.justanswer.com/uploads/MA/MacDruid/IMG_0232.64x64.JPG John T. F.'s Avatar

    John T. F.

    Mac Druid

    Satisfied Customers:

    1408
    20+ years in the computer/Mac industry
  • http://ww2.justanswer.com/uploads/MA/MacHelpdesk/1d2d506.64x64.jpg David's Avatar

    David

    Mac Support Specialist

    Satisfied Customers:

    1236
    BSc, H.Dip, Apple Certified
 
 
 
Chat Now With A Mac Support Specialist
John D
John D
Mac Support Specialist
9659 Satisfied Customers
Worked on Macs and PC's for 20+ years.