# Thread: INDIRECT formula getting corrupted

1. ## INDIRECT formula getting corrupted

Hi All,

I've developed a spreadsheet on a Windows platform (Excel 2003) that is being used by a friend on a MAC platform (Excel 2008). The purpose of the spreadsheet is to allow her to pick costume sizes for each routine in a show based on each performer's measurements (there are 4 measurements per performer such as 35, 30, 32, 52). The costumes come from various vendors that each have their own measurement tables and size labels. One company might just have SA, MA, LA while another company might have XSA, SA, MA, LA, XLA - where each label covers a range of measurements, so SA might cover 33-34 for one company but SA covers 34-36 for another. These vendor tables have been input to one of the sheets. She also has to choose the company based on the costume to be used in each routine. Actually, each vendor has 2 tables.

Based on the measurements and company, a suggested size based on the measurements of each performer (each performer is a row) is proposed (eg, SA). However, that can be overridden by choosing a size from a drop-down list (done via a validation rule). However, since each vendor has its own labels for sizes, as mentioned in the previous paragraph, the choices in the drop-down change based on the company being used for the particular routine.

Following is the Source formula for getting a list of the correct set of labels, based on vendor for that routine, to choose from for overriding a particular selection of costume size that the spreadsheet suggests:
=OFFSET(INDIRECT("tbl"&D11),0,2,8,1)
Col D refers to a vendor table # (since a vendor can have 2 tables, col D actually says, for each performer, which of the 2 tables for that vendor is to be used). Col D might be 1 and 2 for vendor ABC or 3 and 4 for vendor DEF, so tbl1 is a named range of sizes (XSA, SA, MA, LA) associated with vendor ABC's costumes while tbl3 is a named range of sizes (SA, MA LA, XLA) for DEF. Once a vendor and which of its 2 tables is chosen (from a drop-down listing the vendors' names), col D gets filled in with the tbl# for that vendor (based on another range that has vendor name and tbl#).

What seems to be happening quite often is the above formula gets corrupted somehow. When she goes to override a selection, there is no drop-down. When I check the Source formula, D11 (or whatever the row# is) has been changed to #REF! for all rows. There doesn't seem to be anything wrong with the col D values - they always refer to valid table numbers (ie, valid named ranges of the form tbl#).

Any thoughts as to why the formula keeps getting corrupted (happens too often)? The user is really a beginner so she is not going into the Data Validation formulas and changing anything. Further, I've protected cells, like col D, so she cannot randomly choose a number for the vendor's tbl.

TIA

Fred

2. What happens if the Workbook is recalculated when it is showing all those #REF! values ?

3. Good question. Of course, right now, the formulas are ok.

But why do you think that would help? Besides, I don't think I can tell the person using the spreadsheet to do this - maybe I can. But the real question is why this is happening?

4. Fred,

That's a very hard question to answer w/o being in front of that particular computer. At a minimum a copy of the spreadsheet would help to narrow it down a little. Of course I don't think you're going to find much Mac expertise here, though there may be some, and as it may be a Mac version specific problem it again is hard to solve w/o the particular computer in question.

5. Hi RG,

I have used the spreadsheet on my Windows computer and I never had a problem with that formula. I will admit that I haven't used the spreadsheet anywhere near the amount on my PC as she has on hers (it is her project) but I don't see why that makes a difference. And I am trying to convince her to get a Windows based computer to replace her 6 or 7 year old MAC. She did ask what I'd buy if I hit the lottery big and the first item on the list was a Windows based laptop and desktop for her; she didn't accept.

I find working on the MAC somewhat difficult and not just in Excel. A number of things in Excel don't work at all or seem crippled (eg, selecting a bunch of cells and hitting the delete button to delete the contents of the range; the equivalent of F4 to change relative-absolute addressing; help is almost worthless; can't color tabs; and I could go on).

Alas, she is almost finished using the spreadsheet for what was needed and so far, since the original post, no problems . So we may never know since I'm also suggesting this stuff (and all the rest of her stuff) would be better in a DB program and I'm trying to get her to get Access (which only comes for Windows).

Fred

6. Hi Fred

..perhaps the problem is related to using "tbl1" and "tbl2" as your named references, since these can be 'confused' with cell addresses.
(In later versions of Excel, columns go up to XFD, so names like TAX2014, VAT20 will refer to cell addresses etc etc.)
I would use table1 and table2 for named ranges.

zeddy

7. Hi Zeddy,

Excellent thought but...
- This is an older version of Excel (MAC is running 2008, Windows is running 2003).
- I just used "tbl" as an example. The actual ranges have a prefix of the body area of the measurement. So range names are actually "chest_tbl1" or "waist_tbl20".
- why would it work some of the time but not others?

The work has now been finished. So, as stated in my previous post, we may never know the cause of the problem. I don't think the spreadsheet will be used again or, if it is, that will be in a year. But it did save lots of time over flipping thru numerous costume books to check each measurement.

Thanks for all the thoughts.

Fred

8. Hi Fred,

I asked about Recalculation because I discovered, to my cost, that a number of actions seemingly unconnected to an open Workbook can cause many cells to display #REF! values. Opening a .csv file is one of them.

Hitting F9 should be enough to initiate the recalculation, or CTRL ALT SHIFT F9 recalculates absolutely everything (if you have enough fingers).

M

9. It could just be the fact that Excel 2008 is the worst version of Excel ever.

10. Hi Martin,

Thks. If the #REF! occurs when certain events happen, like opening a .csv file, I'd understand. But this is a repetitive task that is done over and over and, then, whammo the formula is corrupted. She's certainly not opening a csv file. But who knows what else she may have done that wasn't supposed to be done. I protected cells as much as possible to prevent her from typing gibberish or overwriting formulas.

The logic that got to that validation formula was:
- she picks a costume company from a drop down, also selects one of two "costume schedules" for that company - again from a drop down
- based on the combination of the two items above, the spreadsheet does a VLOOKUP up in a table which "chest_tbl" those correspond to (eg chest_tbl14). The VLOOKUP formula is in col D and it is the col D part of the INDIRECT that seems to get corrupted from time to time.

What else she may have been doing is hard to say. The steps were not complex but she would sometimes mess up anyway. Undo seemed to do the trick each time. I didn't put lots of effort into this - no VBA - and she was happy bcs it was light years ahead of the way she used to do this.

Fred

11. Hi Rory,

Not being a MAC person, I don't know what works well and what doesn't on that platform.

But if that is really true, I'll let her know. And be on my guard on as far as any future projects.

Fred

12. Also be aware that 2008 doesn't support VBA in any way shape or form (though you can still use XLM).

13. Thanks Rory.

yet another feature of Windows Excel not in MAC Excel! Good thing I didn't write any VBA code for the project - otherwise she would have been using my computer, she would have had to go out and get a Windows computer, or I'd end up doing the work myself. No good alternatives. So I gave her instructions about select this, copy, select that, paste; etc. She saved lots of time over the way she had done it (basically a manual process) so she was happy.

I did do a test on whether VBA was recognized on MAC: took one of my files with some XLM and MAC Excel did raise a warning. But I couldn't get into the VBA environment so I suspected what you said.

Fred

14. Fred,

Is it possible the user is deleting row 11 (in the case of D11)? That would certainly give this result.

I like using Table styles, introduced in Excel 2007. Instead of a laundry list of things I'd have to do when creating a table, I now just use a Table style, often (but not always) followed by locking the header row.Table styles automatically do a number of things which otherwise need to be done by hand; for example, new columns and rows are automatically made part of the table, and you can determine the end of the table easily. In your particular case, you'd refer to the table itself instead of to a particular column/row.

--Scott.

Edit: Sorry, I see you mention that it's locked down.

Do you use formula names? The extra level of indirection seems to help with that kind of thing.

15. Hi Scott,

Not sure what you meant by "locked down" but assume it meant protection. Yes, all worksheets are protected to the extent possible (only allowing her to work with cells that she really needs to provide input and these cells are not filled in with a color to further help keep away from protected cells). So I don't think she could delete any rows or insert any rows; there's really no need for that.

As far as Table styles, I'd bet, per some of the posts further up, that her Excel 2008 on the MAC would not have Table styles. So I didn't use them in this project.

But if I was working in Excel 2007 on Windows, I'm still not sure what the Table style would do. Would you use the Table style for each measurement-table - kind of what I referred to, for example, as chest_tbl20, chest_tbl21, etc?

Again, didn't use formula names for this project. Not sure if Excel 2008 would support this either. Tried to keep it simple. Were you suggesting a formula name to be used for the OFFSET(INDIRECT... that the validation rule uses?

Anyway, this project is on the shelf for the year; if I have time, I'll take a look at the MAC version to see if I can figure out the cause of the corruption before this is needed again. It turned out to be a huge time saver. Not only did she not have to look up each student's measurements for each costume in the company's costume books but, with a little extra work, we generated labels for each student's costumes as well as an "order summary" for each company to use for ordering the costumes. When the costumes came in from the company, it was trivial to match the costume with a student's label. What was a process that was done over several weeks turned into a single weekend of work.

Fred

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•