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: 9666
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: 5 years ago.
Category: Mac
Expert:  John D replied 5 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 5 years ago.
Office 2011 for Mac
Expert:  John D replied 5 years ago.

Yes that will work

 

One moment I am reviewing the file....

 

Expert:  John D replied 5 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 5 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 5 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 and other Mac Specialists are ready to help you
Expert:  John D replied 5 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 5 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 5 years ago.
Further to this John, could you possibly contact me by email with the answer/sending of my spreadsheet on:-
[email protected]

Thanks John
Expert:  John D replied 5 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 5 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 5 years ago.
Hi John,

This is the link:

http://www.2shared.com/file/WOpFNaIj/Gaz_Year_2011_V5.html
Expert:  John D replied 5 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