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, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9657
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

Is there an alternate formula for =sum(sheet1:sheet500!A8)?

Customer Question

Is there an alternate formula for =sum(sheet1:sheet500!A8)? The compiler (DoneEx) I am using cannot handle this formula: it works in the original excel workbook but does not work in the compiled excel application. I have excel 2007.


 


Secondly, is it possible to have all workbooks in a way that they can be accessed by one step at a time?

Submitted: 1 year ago.
Category: Microsoft Office
Expert:  John D replied 1 year ago.
Hi,

Short of listing all the individual sheets, which is obversely not practical for 500 sheets, the only alternative way is by creating a custom formula that uses vba code (i.e. user defend function)

If you would you like me to set up such a formula for you let me know
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9657
Experience: Bachelor of Science - Engineering Consultant
John D and 8 other Microsoft Office Specialists are ready to help you
Customer: replied 1 year ago.


Yes, what I am looking for is something that will work on the compiled application.

Expert:  John D replied 1 year ago.

Sorry for some reason I had not ben notified about your last message above. I just happened to see it now

 

Sure I will be happy to create the custom formula with the code. However, it would be easier for you if you could send me your file so I can set it up on your file directly.

 

Can you do that or would like me to write the code on a sample file and send it to you?

 

 

 

.

 

UPDATE

 

Since I have not heard back from you I went ahead and wrote the code on a sample file

 

Here is the file with the formula/code

 

http://www.ge.tt/6pNMQ2V/v/0?c

 

The new formula or function is called zSum(). It has three text parameters which are: 1) the name of the first sheet in the range, 2) the name of the last sheet in the range, and 3) the cell reference as text

 

You can see that formula in the yellow cell on Sheet1which adds the cells A8 in Sheet2 through Sheet5

 

The code is called "Function zSum" and is located in Module1

 

 

Please note that since there is code in the file you need to make sure that you have your Excel Options configured so that macros are enabled and hence allowed to run, otherwise nothing happens when you type that function. If you need assistance in this regard please let me know,.and provide the version of Excel you are running

 

Hope this helps. Let me know if you have any questions

 

 

 

 

 

Customer: replied 1 year ago.

 


What I saw in your code is =zSum("Sheet2","Sheet5","A8")



Will the code above add up from 500 sheets in a workbook?

Customer: replied 1 year ago.

I tried it for just for 350 sheets using this =zSUM("Sheet1","Sheet350","A8") but this is what I get #NAME?

Customer: replied 1 year ago.


I tried to send you my file, It appears my file is too big to be attached. Is there any other I can send it to you?

Expert:  John D replied 1 year ago.
It should work for as many sheets as you have. Are you sure that macros are enabled on your system?

If so then yes please send me the file. To do so go to http://www.wikisend.com/ and upload the file using the Browse button (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

If the file has sensitive information let me know before you upload it
Customer: replied 1 year ago.

When I typed in =zSum("Sheet1","Sheet250"'"B8") in I am getting #NAME? in the cell I typed in the formula. Please, I am still struggling with the same problem. Where can I attach my workbook so you can see it for yourself?
Dennis


 

Expert:  John D replied 1 year ago.

Dennis I replied to you last night giving you a link to upload your file to. You can see that link in my previous message above on this page

 

Anyway here again is that link where you can upload your file

 

http://www.wikisend.com/

 

when you go there click the Browse button to upload your file (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

 

 

Expert:  John D replied 1 year ago.

Make sure to post back the File ID that you will get after uploading the file cause without that I will not be able to get the file

 

Alternatively you can send the file to the site moderator by email to info@justanswer.com. Make sure to put "For John D" in the subject line, and include a link to this page in the body of the email.

 

Let me know if and when you send it that way. It might take up to 24 hours for the site moderator to forward it to me but I will let you know as soon as I have received it

 

 

 

Customer: replied 1 year ago.

  • File ID: 815214

  • File size: 90.9 MB

  • Time to live: 7 days



Download link:http://wikisend.com/download/815214/Members.xls

Expert:  John D replied 1 year ago.
Huge file!


Are you sure you copied the code that I sent you (if not how do you expect it to work without the code)
Customer: replied 1 year ago.


Did you receive the file I sent to you through the provided link and the XXXXX@XXXXXX.XXX? I have the macros enabled and I am using excel 2007. I cannot get access to the provided link from work. Let me know if you did receive the file I sent you. I have both the file ID and the link to the file in my reply to you.


Thank you.

Customer: replied 1 year ago.

Did you seceive the file I sent you? I sent it to the link and the XXXXX@XXXXXX.XXX. The very cell on the sheet I named "SumTotals" has the formula =zSum("sheet1","sheet350","B8") equivalent to yours (=zSum("sheet2","sheet5","A8"). Since my file starts sheet1 and ends with sheet350, I substituted the formula to meet my need. The MS version I am using is excel 2007 and the macros are enabled.

Expert:  John D replied 1 year ago.

hmm. That formula, which as I said was a custom formula, requires it own CODE so it can work, and the code is in that file that I sent you. You need to copy the code from my file to yours so the custom formula can work
'

If you are not able to copy the code from my file I guess I will have to install it on your file and send it back to you

Customer: replied 1 year ago.


Please send me that code if it is different from =zSum("sheet2","sheet5",A8"). I do not remember seeing a code you sent me.

Expert:  John D replied 1 year ago.

The code is there. Here is how to access it:

Close all Excel files, then open the only file that I sent, press Alt + F11 keys to bring up the Visual Basic Editor window, and you will see the code, as shown below

graphic

 



Then select the code and copy it to your file

 

But before copying it to your file you need to test it first on the file that I sent you to make sure that it does work there as there is no sense in copying it to the large file unless you can get it to work on the simple and small file

 

 

 

Customer: replied 1 year ago.

 



  • File ID: 325846

  • File size: 91.9 MB


Hi John, I have tried it but is not working for me. May be I do not know how to use it. I have downloaded the file again, the file Id is 325846. Please try it and let me know. Thanks.

Expert:  John D replied 1 year ago.
This is taking too long. Could we have an online session where we're both online at the same time so we can communicate in real time and try to resolve this.

Let me know your time zone and when you can be available
Customer: replied 1 year ago.

Your attachment suggestion does not work either, I tried and uploaded the file again and these are the links to my uploaded file:


File ID: 397632


download link: http://wikisend.com/download/Members.xls


URL: http://wikisend.com/doenload/397632

Expert:  John D replied 1 year ago.
I am not sure I understand what you mean about having links.

Anyway please respond to my last message so we can conclude this issue. Here is that message again:

Could we have an online session where we're both online at the same time so we can communicate in real time and try to resolve this.

Let me know your time zone and when you can be available
Customer: replied 1 year ago.

My time zone is eastern and I am available between the hours 8pm and 10pm, Mondays, Wednesday, and Saturdays 1pm -4pm, Sundays 3pm -5pm. I do not understand your statement about links. You suggested in one of your emails instructing me to upload my file and send you the file ID so you can add the code yourself, do you remember that? That is what I did and sent you the file id and links to where you advised me. Please, go back to your mails and see. The file is uploaded and has its id as(NNN) NNN-NNNNand the download link as http:// wikisend.com/download/Members.xls. I aso have the URL as http://wikisend.com/download/3976532.


 


Thank you

Expert:  John D replied 1 year ago.
I already received your file, twice in fact

I'm in California. Ok let us both be available tomorrow for a 30 min. session at 8pm your time. Just post a message here when you are ready at that time. Please confirm
Customer: replied 1 year ago.


That is fine with me. But how? The same way we have communicating?

Expert:  John D replied 1 year ago.
Yes. Just post a message here when you are ready around that time
Customer: replied 1 year ago.


Okay, you will see the posted message around that time. I am looking forward to getting this resolved.

Expert:  John D replied 1 year ago.
Ok, thanks
Customer: replied 1 year ago.

I am ready now.

Customer: replied 1 year ago.

I am ready now to communicate with John D.

Customer: replied 1 year ago.


Hi John, I am ready

Expert:  John D replied 1 year ago.

Sorry I got held up on the way for about 20 mins. I am ready now....

 

.

.

 

 

UPDATE:

 

Ok it's past 6pm (9pm your time) and been waiting for almost 45 mins... Anyway here is what I wanted you to try

 

Let's start by making sure your macros are enabled. Please close all Excel files and Excel applications, then download and save this test file to your desktop (before opening it)

 

Attachment: 2013-01-15_013402_test_macro.xls

 

Then go to your desktop and open the file. Click the button and let me know what happens

 

 

 

Customer: replied 1 year ago.


I don't know what happened why we could not communicate as scheduled. I started communication before 8pm but did not get any response. Anyway, the macros are enabled, F12 cell changed to red when I clicked the suggested button.

Expert:  John D replied 1 year ago.
As I mentioned I was heading to my base to be there at 8 pm but something unexpected happened on the road that held me up for about 20 mins

Anyway, here is the next step

Please close all Excel files and Excel applications, then download and save this test file to your desktop (before opening it)

 

Then go to your desktop and open the file. Click the button and let me know what happens

 

http://www.ge.tt/6pNMQ2V/v/0?c

Then go to your desktop and open the file. Do you see the value 10 in the yellow cell? If so go to any of the other sheets and change the value of cell A8, now go back to the first sheet, has the value in the yellow cell changed to other than 10?

 

Let me know

 

 

 


Customer: replied 1 year ago.

After doing the steps, cell B1 showed the number 10 with this formula =zSum("sheet2","sheet5","A8") showing in the function bar. I have seen this before. I thought you were going do something with the workbook I sent you that has 350 sheets. You need to see that workbook: every sheet in that workbook has January to December with all the dates that are accounted for in this program of mine. I want you to show me how I can account for all the dates assuming some datum input were made into all the categories in the respective sheets. Please let me know the next step to do.


 


Thank you

Expert:  John D replied 1 year ago.
As I was getting confusing messages from you in that the solution does not work (e.g. when you said you got a #NAME! error it meant that the macro was not working, which we know now that it is not the case). So I wanted to make 100% sure that 1) macros can run on your system, and 2) the formula that I have you does indeed work

So we know now that the solution does indeed work. I will now try to set it up on your large file and send it back.
Customer: replied 1 year ago.

Thank you for your understanding. I am earnestly looking forward to seeing the big file working like test sheet. Thank you.

Expert:  John D replied 1 year ago.

Ok I am done.

 

Uploading file which is going to take some time ....

 

 

Expert:  John D replied 1 year ago.

Ok here you go

 

http://www.ge.tt/7cPcTYV/v/0?c

 

I set up a sample formula in the yellow cell (AF5) on the Dashboard sheet. It sums up the values in cell A8 in all sheets from Sheet1 through Sheet350. Of course you can enter the formula in any cell, to sum any given cells, on any sheet other than Sheet1 through Sheet350.

 

Hope this helps.

 

 

 

 

 

 

Customer: replied 1 year ago.

 

Hi John,

 

I do not know where the formula is adding up 14,446,250 from. This is the way my workbook works: When an input, say $1, is made into cell location A8 respectively in all the 350 sheets in the workbook, the cell location in the “SumTotals” with your formula should add up to $350.00. If I do just 100 sheets SumTotals will add up $100 in its cell where the formula is entered. That is not happening when I tried it with your formula. Iinstead I am getting $14,446,250. Your formula should be in the sheet called “SumTotals” in the same workbook. The purpose of the “Dashboard” is just to locate any sheet of interest in the workbook (Members.xls) for data entry.

 

Thank you.

 

XXXX XXXXX

Expert:  John D replied 1 year ago.

I just chose to sum A8 as an example. So it's adding the date values in A8 on all 350 sheets (e.g. 41275 + 41275 + 41275 + etc (so 350 x 412275 totaling to14,446,250). .

Just change the referenced cell A8 in the formula to any cell ref and check it yourself. Say if you use B8 it will return 2 since there is "1" on sheet1 and "1" on sheet2 only.

That same formula has worked all along. At this point and after having spent over 4 hours on the messages on this long thread and downloading and uploading exceptionally large files in addition to the programming time I think it would be appropriate to apply another rating especially if you wish to continue further so I can get at least partially compensated for my time. Thank you




Customer: replied 1 year ago.

I just tried it: It is not working in the SumTotals sheet where I need the formula. I do not understand why you have it set in the Dashboard sheet. I have explained to you the use of the Dashboard in my previous mail. Can you do whatever that you did in the Dashboard to the SumTotals sheet? It is not in the Dashboard I have the problem, the problem is in the SumTotals sheet.


 


Thank you.

Expert:  John D replied 1 year ago.
Dennis, can't you put the formula yourself on that sheet. If you would like me to do it for you again please make sure that you apply a rating as this operation will take yet another 30 mins just in sending that huge file to you again


Thank you

Customer: replied 1 year ago.

Mr. John, I have done that over and over again that is why I sent you the details about the program. Yes, I have typed and copied your formula in the SumTotals sheet and found it not working. That is why I said you try it and see for yourself what I am experiencing. Actually, if one cell in the SumTotals sheet works then I am going to do the same for all the cells under the categories in that sheet from January to December. The categories are "Tithe, Offering, Thanksgiving, etc". So you can see that it is not about typing or copying the formula that is the problem. I have been working on, typing and copying this program since October, 2010 and I do enjoy doing that when expected results are achieved. It is not working for me in the SumTotals sheet. The money I paid was on the condition that if I am satisfied with the results. I do not understand what you mean by rating your work. Please let me know something about the rating for you to get credit for time and work you put in and I will be glad to do it based on the answer to this request.

 

Thank you.

Customer: replied 1 year ago.

 



You replied




Wednesday, January 16, 2013 9:12 PM EST




Mr. John, I have done that over and over again that is why I sent you the details about the program. Yes, I have typed and copied your formula in the SumTotals sheet and found it not working. That is why I said you try it and see for yourself what I am experiencing. Actually, if one cell in the SumTotals sheet works then I am going to do the same for all the cells under the categories in that sheet from January to December. The categories are "Tithe, Offering, Thanksgiving, etc". So you can see that it is not about typing or copying the formula that is the problem. I have been working on, typing and copying this program since October, 2010 and I do enjoy doing that when expected results are achieved. It is not working for me in the SumTotals sheet. The money I paid was on the condition that if I am satisfied with the results. I do not understand what you mean by rating your work. Please let me know something about the rating for you to get credit for time and work you put in and I will be glad to do it based on the answer to this request.


 


Thank you.


Expert:  John D replied 1 year ago.
Dennis. I have set up the macro solution fir you TWICE (one on my sample file and one on your file) and have shown you that it does work in both cases.

No problem I will give it one more shot. Please answer these questions so I can set it up agaiin as per your requirement

1- On which sheet do you want the formula installed

2- In which cell shall the formula be installed on that sheet

3- What is the cell on the 350 sheets that need to be summed (e,g, A8)
Customer: replied 1 year ago.


1. I want the formula installed in the sheel called "SumTotals".


 


2.Please install the formula in the first row (staring with B4


ending AH4) of the SumTotals sheet. The formula should


be adding the B8 cell locations from the sheets (1 thru 350) I


and I will do the rest 365 rows myself for January thru December.


3. The cells start from B8 through AH8. All the sheets have the same


format.


 


If you get the first row working, in the "SumTotals" sheet, I think I can do the rest 365 rows as long as I will be able to copy and edit your formula.


 


The first row cells of the SumTotalS are in this order:


B4, D4, F4, H4, J4, L4, N4, P4, R4, T4, V4, X4, Z4, AB4, AD4, AF4, & AH4


 


The first row cells of the Sheets are in this order:


B8, D8, F8, H8, J8, L8, N8, P8, R4, T8, V8, X8, Z8, AB8, AD8, AF8, & AH8.


In other words your formula in the SumTotals sheet cell locations should be reading =zSUM("sheet1","sheet350","B8") in location B4, =zSUM("sheet1","sheet350","D8") in location D4,=zSUM("sheet1","sheet350","F8") in location F4...=zSUM("sheet1","sheet350","AH8") in location AH4.


 


John, I am a pastor trying to help churches with this program. I will appreciate it if we can get this program right: I have pastor friends who have seen the demonstrations of the program and are waiting for it for their financial record keeping.


 


Thank you and God bless


 


Dennis Ezonnaebi

Expert:  John D replied 1 year ago.
I can give you the formula for the first cell then you can use the same principle for the other cells.

If you want me the formulas in all these rows and columns you need to start a new question for that
Customer: replied 1 year ago.

If you cannot do one out of 366 rows in the "SumTotals" sheet, please do the one cell in the SumTotals sheet and let me go on with that and see what happens with the rest.


 


Thank you.


 


Dennis

Expert:  John D replied 1 year ago.
So please answer the 3 questions that I asked for the cell in question
Customer: replied 1 year ago.

1. I want the formula installed in the sheel called "SumTotals".


 


 


 


2.Please install the formula in the first cell of B4 of the SumTotals sheet. The formula should be adding all the B8 cell locations from the sheets (1 thru 350) .


 


3. It is cell B8 of all the 350 sheets that needs summation.

Expert:  John D replied 1 year ago.

Hi Dennis

 

Here you go

 

http://www.ge.tt/4DOarlV/v/0?c

 

I have set up the formula in cell B4 on the SumTotal sheet (yellow cell). It sums up the values in cells B8 in all 350 sheets

 

Currently the formula is retuning the value "8" since only three sheets have values in cell B8, i.e. Sheet1 has "1", Sheet2 has "1" , and Sheet3 has "6", while the rest of the sheets are empty.

 

Try inserting some values in cell B8 on the other sheets to see how it sums them.

 

Please remember that the sheet needs to be recalculated in order that the formula gets updated,. You can force it to recalulcate by pressing the key combination Ctrl + Alt + F9

 

Hope this helps

 

 

 

Customer: replied 1 year ago.

Hi John,


I cannot access the file from my work. I will try it when I get home. It sounds like you got it working.

Customer: replied 1 year ago.

Hi John, I thought it you had it but this time even the cell you programed is not working. I went back to the old one you programmed in the Dashboard sheet that works, but it works only in that cell. If you try to copy the programmed cell, it is the cell value you can get instead of the formula. If you type in formula still no results . This was the reason I suggested programming at least one row in the SumTotals sheet. I think it is time for me to look for an alternative help, it is frustrations trying to kick in and I refused to be frustrated. I cannot continue on this any more. Please, whosoever is responsible for refunding my money should do so since my problem is not solved. Thank you for your efforts. Please let me know how the rating is done on an unsolved problem.

 

Pastor Dennis

Customer: replied 1 year ago.

What is going going? I am still waiting to hear from someone about my $45.


 


Dennis

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:

 
 
 
  • 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
< Last | Next >
  • 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
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional