Replies

Thanks Paul.

I had to put the Receipt #s in the workbook bcs it seemed like there was no way to generate them in Word w/o VBA and for a directory, as first asked. So for now, it looks like I’ll have to handle it this way.

I understand what you’re saying about the dates. However, the plan, for now, is NOT to create new records every month with the same info but just a different date and receipt number. The same rows will be reused but new receipt numbers generated just by changing that first receipt number in AM2. The only other thing that will change is col A if the autopay did not go thru for some reason; then the corresponding row in col A will have a NO. So in Word, I’ll just put into the “Edit Data” step of mail merge just to filter those records that do not have NO (similar to what the workbook is doing when generating the Invoice #).

But you do raise an interesting point. Instead of re-using the rows with a different Invoice #, why not just keep adding the same rows every month and let Excel generate the invoice #s for the new rows? That way, there will be 1 file with all the data. In the merge process, I can set up something to filter out the old rows from previous months.

Will give that some thought.

Thanks for the help.

Fred

Hi Paul,

I’ve been doing some work on what I need and am attaching it.

My approach, in the absence of VBA on the MAC for Office 2008, is to have a row for each receipt to be generated. You can ignore pretty much all the columns since they are things needed on the receipt. The key thing is the generation of invoice #s.

As said in my previous post, the same set of receipts “pretty much” need to be generated every month. There may be exceptions if a parent decides to go on the program of auto billing to his/her credit card (add a row to the spreadsheet) or if they can’t be billed that month (card expired but they forgot to tell the school or the bank, so the auto payment does NOT go thru – NO receipt).

Invoice #s need to be generated each time the mail merge is done each month. Oct might have #s 100-139 but Nov might only have 140-175 since a few auto payments didn’t go thru (the school owner gets emails from the bank saying success or not and can set col A to NO for the failures).

I have not designed the directory yet. But I’m envisioning some things being done in Word: the date for the receipt (always the first of the month), purpose of payment being that month’s tuition. The receipts will always be generated within a day or two of the 1st of the month when the bank does the autopay.

You’ll notice in the spreadsheet that there’s a place at the far right (AN2 but I’ll probably move this to another sheet) where manually the owner has to enter the first invoice # to be used the following month – add 1 to the last # generated this month. This is what I was hoping to avoid using some of the approaches that I’ve seen elsewhere but they all need VBA and it’s not clear they can be used in a directory.

I may have to repost here for some of the things mentioned in above. I’m assuming in Word that I can get “today’s date” and parse it to create a date of the first (ie, even if the mail-merge is done Jan 3, 2016, the receipt should be dated Jan 1, 2016); from the same date, I need to be able to create “for January’s tuition”. I’ll be checking those tutorials you posted.

Of course, I’ll skip those records that have a NO in the Print Rcpt field (and I may come back on this too).

Thanks.

Fred

Hi Maud,

While I don’t know if the thread I was looking for had some of your code, I’ll give it a try when I have a little more time (leaving on vacation tomorrow). I’d prefer a formula approach so was trying to implement Rory’s suggestion allowing circular reference; while seemingly straightforward, I ran into some problems that I’m trying to work thru.

Let me be a little more specific about what I’m trying to do.

This is for my gradebook, for which I’ve asked lots of questions on this forum.

Every 2 weeks during the semester (eg, 5 times during a normal fall/spring 15-week semester; we don’t go until the end of the semester), instructors have to enter a “monitoring” code that says how each student is doing. “NTR” means “nothing to report,” “333” means having trouble with the work, etc. If a code other than NTR is entered, a corresponding letter is generated by “the system” and sent to the student’s home address.

The entry is done on a form containing all the students CURRENTLY in the class. So the first entry, pretty much everyone who started 2 weeks earlier is still there but some may have already switched to another section of the class. Certainly as the semester goes on, students will drop.

For example, the starting roster has 30 students. By the time of the 1st monitoring period, there might only be 26; 2nd monitoring period only 22;…5th monitoring period only 20.

What I want to do is save, in my gradebook, the monitoring codes entered for ALL students – even those that have dropped out. Clearly the dropouts will only have 1 or 2 codes in my gradebook, depending on when they dropped out.

What I want to avoid is having to enter the codes manually again into my gradebook, since I’ve already done that in the school’s form.

I can download a copy of what I entered as an Excel spreadsheet (about 8 columns for name, ID, …, and the 5 monitoring entries; 1 row, other than heading rows, per student). But as I suggested above, this spreadsheet will only contain CURRENT students.

So what I was doing is copying/pasting the school’s version of the monitoring spreadsheet (as described in the above para) into my own “monitoring” sheet. My sheet looks like a replica of what I downloaded with the name, 5 cols, etc. The key difference is that mine has ALL the students who were ever in the class whereas the downloaded version (now in my sheet) only has CURRENT students. The school’s version gets pasted into the same area of my sheet every 2 weeks, so formulas don’t have to change. But as students drop out, the # of “useful” rows I’m pasting decreases (I can paste more rows, but they’ll be blank, to make sure there are no left-over students from the previous download).

If I wait until the end of the semester, I’d have all 5 codes for the students who survived until the end, so I’d only need to do it once for them. But I would have nothing for those who dropped out along the way since they’re no longer in the school’s version.

So when I download the school’s monitoring sheet every 2 weeks, I want to copy (by formula, not copy-paste) whatever info is in the school’s version into my version. Here’s where the formula I need comes in.

Using some general references, hopefully this will be understood (imagine an n-row by 6-col [name and 5 monitoring codes] area in my sheet):
=if student-on-this-row-in-my-monitoring-area is found in the school’s-download, copy the code in the column for that student’s row from the school’s-download;
if not found, leave the code I had (maybe from a previous download and they subsequently dropped out)

I can do the searching with a MATCH – no problem. I can copy a current code – no problem. Where I need help is with the “not found” part. I don’t want the IF test to give whatever result it gives when there’s no FALSE expression.

Unfortunately, I don’t have the time right now to create a sample since I’m leaving on vacation tomorrow. Hopefully the above will explain what I’m trying to do.

Thanks for all the help.

Fred

Hi Gumintwork,

Thanks for the suggestion but that doesn’t do what I’m looking for. I entered your formula in B1 and used A1 as you have. When A1=5, then B1=”Yes”. But when A1 is changed to something other than 5, B1 becomes 0 (the “value” of the “” in the inner IF). I can go back and forth in A1 and this is what will continuously happen.

See my response to Maud for a fuller explanation of what I’m trying to do.

Fred

Sorry Paul.

When I said

However, my needs are slightly different:
– the document to be numbered is actually a directory – several receipts on a page (3 or 4, haven’t decided yet) that have to be numbered

I thought that saying in a Word forum that this was a “directory” implied a mail-merge – so my bad.

As to your last paragraph, there would be 7 monthly merges (Oct-April) for all the same students. That’s a general statement since a parent might elect to go to automatic payments in the middle of the school year (rarely do they go off auto payment). Other than that, the records to be merged would be identical (same parent, same tuition); the only things that would differ is the date of the receipt, the “Note” for the purpose of the receipt (“October tuition” on October’s receipt, etc.), and the receipt number. As mentioned above, the May tuition is different from any other month. I’m thinking that the date and purpose of the receipt could be handled in Word; it was the sequence # that I was concerned about generating.

If you’re using the same records, but with different fields each time, you could use field coding to test which field you’re merging from

Not sure I follow; per above, everything is the same with just the different. Not sure what you meant by “use field coding to test which field…”

Fred

Hi Paul,

I’m confused about what you’re suggesting.

simply add a new row to the table and Word will give that row a new number

I do understand that if I start a table and number the row using Word’s numbering feature that adding a new row to the table results in a number in that new row with the next sequential number. But it’s not clear to me what the table is and how the number in the row relates to the receipt #.

Let me be a little more explicit about the setup.

My friend has a “data base” in Excel (MAC 2008) with one row per student in her school. The row contains lots of information about the student, including the monthly tuition and whether the tuition is handled as an automatic payment against the parent’s credit card. The tuition is the same Oct-Apr with a different amount for May (don’t ask), so there are actually 2 cols for these (one for Oct-Apr and another for May); there’s another col to indicate whether tuition is billed automatically. Since automatic payments are handled by the bank at the beginning of the month (about 40 or so), she wants to generate a receipt for the parents after seeing the automatic payments have all been done. The set of parents doing automatic billing doesn’t change much, if at all, from month to month.

What I was going to do was set up a mail merge from the Excel workbook to a Word document in the form of a table that looks like her receipt (the size would allow 3 or 4 to a page). The table would have a space for a receipt #. The number should increment for each receipt printed that month, so say 1-40 for Jan over about 10 pages. The last step of the merge could be to a document so she can make any final edits to the document.

The following month, she needs the receipts numbered 41-80; then 81-120; etc.

I’m not sure if I can do the merge directly from her spreadsheet so I may have to create another sheet in the workbook with just the appropriate info for the receipts. I could set up a col for receipt #, which can be changed easily from month to month. I was thinking this might be needed to generate the receipt numbers since it’s unclear how to do this in Word w/o VBA.

Comments?

BTW: I think there’s a bug in the “Go Advanced” button. This is the 2nd time it has happened to me in the past few months. I click on Go Advanced to see what I’ve done (the quotes) and I’m logged out. Luckily, this time, I had copied the contents of my msg before hitting Go Advanced so I just pasted the contents back in and added this paragraph.

Thanks again.

Fred

Thanks Rory.

After I posted my question, I thought about it. Clearly, a cell can have a formula and a value, which is what’s usually displayed (I do rarely use CTRL+` to display formulas). But what is it about allowing iterations in Options that makes Excel behave differently than if not allowed (not looking at numerical iterations to converge on a number)?

As I noted in my original post, someone asked the same question a while ago. It would seem useful to allow the true or false part of an IF test to evaluate to “keep the current value” w/o needing to turn on iterations.

Just my 2 cents.

Fred

Rory,

I’m curious about the iterative solution.

Using your example of =IF(A1>0,A1,B1) as the formula in B1:
– B1 has a formula in it as you gave
– the value of B1, for example, is the word “cat”
– if A1<=0, the formula evaluates to false, so the contents of B1 becomes whatever had been in B1. Is that the formula or is that the value of B1? Clearly, "cat" is being displayed.
– suppose A1 is changed to some other negative number so again the formula evaluates to false. But isn't "cat" in B1 so where's the formula?

While I understand the idea of iterative numerical solutions to converge on a numerical value and stop calculating after x iterations or the change from one iteration to the next is smaller than some threshold, I'm having trouble understanding what's going on here.

Does turning on iterations somehow tell Excel to allow a cell to be associated with a formula and a "constant"?

Thanks.

Fred

RG, Rory,

Thanks much.

This works fine in my little test. Let me try it in my gradebook.

Fred

Thanks RG.

Seems simple but I’m not getting it. What I’m getting is what I’d expect of the IF test. If I start with A1 empty and put your IF test in B1, I get nothing in B1 (ok). If I now put “cat” or “4” in A1, I get “Yes” (ok). If I now set A1 to 0, I get “” (expected but I’m looking to keep the “Yes”).

I think the solution probably involved some VBA probably with a worksheet change event. But I couldn’t find it.

Fred

Actually, your question about col D prompted me to think of something else (but no she wouldn’t have deleted all of col D).

The drop down depends on a few other cells:
– col C, which is hidden, so she can’t get to that
– cell B2, which has a drop down to choose the costume company for the class, and B4, which has a drop down to choose whether the default size chart for the class is Adult or Child.

Cells B11:B50, allow her to override the size chart for a particular student (eg, most of the class is “child” size but one or two bigger students are sized according to the “adult” chart).

Col C, again hidden, creates the table name based on concatenating the costume company name and the size chart (either from the override or from B4).

Cells D11:D50 look up the table number based on col C.

The DV formula depends on col D.

So if I delete B4 or B2 that looks like it could create the problem. BUT:
– she says she doesn’t delete the entries in B2 or B4 bcs, as soon as she clicks on either one, the drop down appears and she picks the appropriate values
– I tried it and it seemed to work (ie, create the #REF!). But I couldn’t get it to work consistently.
– Moreover, one would think that if I entered a valid value in B2 and B4 after the deletion that the DV formula should be restored. I tried it and it was.

So still a mystery but maybe not as big a mystery.

Thanks.

Fred

Hi Rory,

Delete key: nope, it’s marked “Delete”. It’s in the upper right corner, just like on the main part of a Windows keyboard (where there are both “Delete” and “Backspace”. It just works like a backspace. After all, who ever said that “Delete” had to work to the right? Some Windows maven! But I do find it very frustrating.

I read Steve Jobs’ biography by Isaacson. Steve was somewhat spartan in his designs – nothing more than absolutely necessary. I recall this issue of 2 keys vs 1 key was even mentioned in there.

As to your theory on cutting and pasting over the cell that was referred to in the DV formula: no for several reasons
– that cell (in col D) for all rows is protected, so she couldn’t disturb that
– she’d have no business in that cell (or anywhere in col D) since she probably doesn’t understand the use of that column (why isn’t it hidden? bcs I was using it to make sure things were working correctly and never bothered to hide it)
– that would certainly explain if the DV formula for that row was then corrupted but not why the entire column of DV formulas got corrupted at the same time

We moved on to an alternative approach to overriding the size, as mentioned near the end of my original post in this thread. As a result, we found an error (actually having nothing to do with this) when the data for the vendor tables were entered last year that, for some reason, was never caught. But it would be nice to know why DV was getting corrupted.

Fred

Hi Rory,

Thanks for the thoughts.

I’ve tried selecting multiple cells on her PC and then pressing the “Delete” key. Only 1 cell’s contents are deleted. I just tried it now: select a bunch of cells (by dragging over them); press Delete. First cell’s contents are deleted but Excel is waiting to see if you want to type something else in that cell. When you hit return, the first cell’s contents are deleted and Excel advances to the 2nd cell in the selection.

This is similar to the Windows Excel action of selecting cells and entering data in the first cell. When you hit return, Excel advances to the 2nd cell in the selection.

As to the theory on the validation rules getting corrupted:
1. If she was deleting the cell (which appears right under the Edit menu item for clear or above the Clear on the right-click menu), she’d get the message about what to do with the cells. At that point, she’d ask me what does that mean (this is not a power user). Even if she chose on her own (move up?), I’d notice the structure of the spreadsheet being messed up – which it’s not. Moreover, since the sheet is protected, Delete is not even an option.

2. I tried your theory on the cut-paste. The cell that’s cut loses it’s drop down box but the cell onto which it’s pasted has the drop down with the correct values. Moreover, the problem, when it happens, affects ALL cells in that col. As mentioned, there’s 50 cells in that column and ALL are affected (corrupted) even though she’s not working with anywhere near that number of rows/students. What happens in my case is that all cells still have the drop down arrow when you click on any of them but nothing drops down since the formula has been corrupted.

If you’re interested in taking a look for trouble shooting, I’ll send you the file privately (I really don’t want to share this publicly since it’s not really mine to share). However, right now I don’t have a broken version; also per my post, I’ve implemented an alternative which is working fine so far. The alternative doesn’t even use a drop down but allows her to type in the Override col (the one where the formula was getting corrupted, which has now been deleted). I provide a list of valid sizes for her to see based on the company. I then check in the “Final” selection column that the choice is one of the ones in the list.

Fred

Thanks Guys.

I thought I posted something on Friday night – thanks to RG and Maud. And saying I couldn’t redesign the spreadsheet as RG suggested. For one thing, there are numerous columns with the type of formula I mentioned.

But I don’t see it here.

Based on Maud’s first VBA post, I also had done some work on my own to use formulas rather than text strings. I thought I had succeeded with only one minor problem: the UDF would be invoked, say, in F35 but the column was only filled to row, say, F22. The remaining blank rows were to be used for future entries. Doing that gave me a #VALUE error due to the blank rows. So I changed my invocation formula to:
=grandavg(INDIRECT(“F4:F”&COUNTA(F4:F34)+3)) – the 3 is for the header rows.

This was only after trying to fix the VBA to skip blank cells. Thought it should have been easy. I tried it 2 ways:
– process all the blanks but do a test to see if the formula was blank (LEN < 1) – if so, goto the loop and keep checking until the end of the range – I couldn't get this to work. There would not be blank rows interspersed with filled rows, so this was really not that efficient (but we're not talking about lots of rows here).
– on encountering the first blank (LEN < 1), exit the loop by going to the statement after the "next cell" – this did work.

So Maud, let me take a look at your latest and compare it to what I got. I suspect, other than the error checking, they should be pretty much the same – of course, yours will be better.

BTW: there is a difference between the 2 ways of getting the "grand average":
– add up the components of the nn, rr, aa and redo the formula = nn/(rr-aa) for the grand avg using the totaled sums
– take the average of the averages

With a little bit of algebraic manipulation, one can see that the 2 alternatives above are the same only under certain conditions. In general, they'd be different.

Fred

Chung – thanks for all the input!

A few thoughts.

•With partitioning, a user needs to reboot from his/her preferred operating system into another OS that might not be as familiar. With a VM there’s no need to reboot.

I understand there’s no need to reboot. But running Windows under a VM or in a different partition seems to still require familiarity with something she’s not familiar with – ie Windows. No? I think ease of use vs rebooting are 2 different things.

I get the other advantages. That said and from the feedback in this thread, it seems that VM is the way to go if I stick with Access.

LO Base supports more scripting languages than MS Access so if you already know C++, Python, Java and/or CLI it might be an easier transition compared to learning LibreOffice Basic.

While I get that, it’s not clear to me that

VirtualBox uses a similar GUI for Windows, Macs and Linux which will make it convenient for you to build/test a VM on your own Windows computer and copy it to your friend’s Mac whenever it needs to be updated.

Not sure I get this. Why am I building/testing a VM on my Windows PC? I have Access so why can’t I work w/o a VM on my machine?

If you decide to go with a VM running Windows, consider using Microsoft’s free Access Runtime…There’ll be less risk of accidental changes to the database app and the VM will have a much smaller disk footprint.

This sounds great but I don’t quite understand it. With my Excel spreadsheets, I end up having to protect the sheets and cells that shouldn’t be touched and also color the cells. Of course, when I have to make a change, I forget that something is protected. So I’d like to understand this a lot better! What does the Access run-time do that prevents my friend from making inadvertent changes? (Of course, I assume, I still have to do lots of error checking.)

I should also mention that the DB has to run on several MACs: she has one at home, one at the studio, and her daughter also can enter info.

Do I hear a server-based solution? We’ve discussed this and she’s really not in favor of it.

By the way, there are server/web-based solutions for dance-studio management but they don’t do some of the things my spreadsheets do, do other things she doesn’t want, and cost too much.

already know C++, Python, Java and/or CLI it might be an easier transition compared to learning LibreOffice Basic.

But it wasn’t clear if any of these, including LO Basic, can access a program’s object model. If not, to me, this is a clear advantage to Access with VBA.

Also, I read a comparison of the development community for Open Office vs LO (can’t find the link). It didn’t really seem that either’s scripting language were that good. While LO seems to have active development whereas OO was less active, I didn’t come away with a good feeling that LO’s Basic was very useful.

Desktop Database: FoxPro, LibreOffice Base, Microsoft Access
Database Application: FileMaker, 4D

I didn’t quite get the distinction between these 2 approaches.

the spreadsheet tables aren’t directly suitable for importing into a database table (e.g. missing column headers, no primary key column, etc.

Actually, her spreadsheet “Master Student File” does have column headers but no primary key.

In a database application, formatting (aka. presentation) is handled separately from the data.

I assume you’re talking about reports for presentation (or, worse case, queries).

Yes, LO Base can easily handle multiple tables. Please correct me if I’m misunderstanding, but it sounds like you’re asking about database normalization? If so, then it’s a good idea to try to do that whenever possible. There are times when it’s alright to bend the rules a bit to avoid having overly complicated database relationships.

Yes, it is DB normalization. I am concerned about the complicated relationships. But as I’ve learned more about her system, we just keep adding more columns to her “Master Student File” to do things right. So there’s a trade-off on bending the rules more and more versus complicated DB relationships.

Without seeing your friend’s current Excel spreadsheets it’s difficult to picture how the various tables relate to each other, but personally, I would avoid managing separate tables for each class year. Instead, use a key to identify the year that a record represents unless the database schema will change a lot from year to year.

I don’t think it will change much, once it’s done right! For example, as mentioned, this year we expanded the # of classes a student can take from 12 to 14 and created 16 “class columns” to give yet more room for growth. But that would not be a change in a DB schema (maybe a table of “values” somewhere that put a limit on the max # classes but can be more easily changed). We also added some extra billing info (columns) this year too bcs that was really a nightmare.

I could put in some year key. Her school is not so big that having records of students who’ve left would slow things down much. But a purge of graduated students every now and then wouldn’t hurt (and that’s what she does now: copies last year’s “Master Student File,” renames it for the current year, deletes graduated students, and goes from there).

BTW, her “Master Student File” is the only file and it has headings in ALL the Excel columns. Actually, that’s not entirely true: there are some separate spreadsheets where she lists all the classes (type, start time, duration, etc).

Rather than dealing with user typos, use drop-down lists of valid choices whenever possible.

I do that as much as possible. However, fields like names (I’ve already mentioned trailing spaces), zip codes, addresses, etc. obviously can’t use drop downs. I’ve even considered including an Excel Soundex addin to make sure she doesn’t enter a “new” student who’s already in the DB but spelling a name differently (and I’ve seen that).

Access web solutions for SharePoint

I’ll take a look.

Fred