REF# in Sendmail

B

B Schwarz

I am getting a REF# again where there is formulas when using Sendmail.
My fix the last time was divideing up the formulas so they wouldnt have so
many sheets to sum up.

What I have now is this....

Formula is suming up the same cell in many sheets. No matter what the first
sheet is in the formula, if I have a value from it coming into the sum
formula I get REF# instead when the sheet has gone through Sendmail and
stripped.
These are order sheets. When I put a fake sheet in for the first sheet in
the formula, and didnt put an order on it, everything was ok.

Any ideal what would cause such a thing?
Barbara
 
D

Dave Peterson

When I want to sum the same cell across multiple worksheets, I'll "sandwich" my
real sheets between two worksheets.

I'd create a worksheet named "Start" (before my real sheets) and another named
"End" after my real sheets.

Then on my total worksheet (outside of that sandwich!), I'd use a formula like:

=sum(start:end!a1)

Now if I remove either of those two worksheets (start or end), I'll get a
reference error.

I can either convert to values (copy|paste special values) or just keep my dummy
sheets in the workbook.

Could you have done something similar?
 
B

B Schwarz

Frank
here is the formula, copied and pasted right out of the spreadsheet, the
DM- numbers are the names of the sheets.
=SUM('DM-OAC010L:DM-OUC041-002'!D150:AQ150)

Dave,
Why is it that you "sandwich" the sheets? Does Excel start freaking out when
there is so many sheets? If so why didnt this happen before? It is a very
large excel workbook, close to 30 mg with about 130 sheets in it.

Your sandwich ideal does work, that is basically what I have to do, go
around it. What I did was copied the first sheet "DM-OAC010L" and put it in
the front, added it to the formula as a "DM-OAC010L (2)", then hid the sheet.

If that is the only way to fix it then could I create a marco for it, send
the macro out to the people who have this workbook, and have it in some type
of self extract file so all they do is click it and it will load and fix
thier workbook. These people dont know much about computers and I wanted to
fix it without having to mail them new CD's.
Barb
 
D

Dave Peterson

I use the helper worksheets (completely empty, so they don't add too much to the
workbook) so that I can delete any sheet I want without worrying if I deleted
the first or last in a formula.

It also makes it nice to be able to drag a worksheet out of the sandwich and see
what happens to the formula--or add more sheets to the sandwich. Hmmmm...
Sandwich...

Did you delete one of those sheets?
 
B

B Schwarz

No, I didnt delete any sheets, only when I was troubleshooting the problem.
I was afraid to use an empty sheet. This workbook is large and finiky.
I wanted it to have the same formulas as the others so in my macro I just
copied the first one over.
I will keep it in mind for future ref to sandwich the formulas when going
across so many sheets.
Now I am not sure of the best way to get the macro into the other workbooks.
Any ideals??
 
D

Dave Peterson

I think the only way you'll see #ref's in this formula (if the worksheets still
exist) is to have an error in the data.

Have you looked at all the sheets to verify that there isn't a #ref in that
range (D150:AQ150)?

ps. You meant formula, not macro, right? I didn't see any macro in any of your
posts.
 
B

B Schwarz

That is the first thing I checked. No REF# in the formulas. If I use Sendmail
and only send the report sheet (sheet that all the vaules come back to ) it
doesnt give me the REF#. Only when I send other sheets with it and no matter
what sheet I use for the first sheet in the formula, if there is an order on
it, I have the problem.

I have about 25 of these spreadsheets in the field. I created a macro that
will copy a sheet to put in front of all the order sheets and add it to the
formula. These people are not real computer friendly. Is there a way to send
them the macro and have it self-extract into Excel? This is the only way I
know to fix the problem.
I contact Ron De Bruin the last time I was getting a REF# and all he could
say was it was a huge file and that might be what is causeing Sendmail to act
up. I broke down the formulas and it fixed the problem.
This is a 30 mg file with many links and formulas and in a folder with the
linked graphics the size is over 100 mg.
Do you know a way for me to send and have the users easily install the macro?
Or am I stuck re-burning the CD's?
Thanks,
Barbara
 
D

Dave Peterson

First, I didn't mean #Ref's in the formulas. I meant #ref's in the cells that
the formulas use.

You could send a separate workbook that contains a macro that can the user can
run.

Tell them to open the problem workbook and the workbook with the macro. Make
the problem workbook active. And then tools|macro|macros... and run your macro.

I would think that it's a one time procedure, so there would be no need to
really install a macro. But if you need to move your macro into one of the
workbooks that you already sent, Chip Pearson has some sample code at:

http://www.cpearson.com/excel/vbe.htm

=======
And the macro that you were writing about is Ron's SendMail workbook?

Ron has multiple versions of this code. Are you sure you're running the correct
version for what you need to do? If you shrink down your data to just a few
worksheets (with smaller amount of data), do you get the same error?
 
B

B Schwarz

Maybe I didnt say it right, but yes I know what you meant and no there was no
Ref# in those cells, it only happened after going through Sendmail.
Thanks for the ideal of the "separate workbook" , I didnt know you could
work a macro that way, but now I do. But I did know how to import a bas file.
Thanks again,
Barbara
 
Top