Replies

That sounds like an approach that will work, at least in the beginning. Thanks for the suggestion.

Thank you for the explanation. I had thought that argument was an elegant way to make that last argument evaluate to TRUE, but I wasn’t sure why it was necessary. I had figured out the first part of the answer before my original post, but I just couldn’t get that last part.

Thanks again,

Hi Steve,

Thank you for your suggestion. Your formula works to perfection.

I’m curious about the last argument (…NOT(ISBLANK…). Could you explain what it does?

Thanks,

Hi John,

Thanks for your response.

Yes I had thought you were setting up a conditional format. The active cell gets its content from users keying in numbers. I need to restrict their inputs along the lines I mentioned in my original post (ie, where “X” in the reference cell restricts the user to entering a positive number and “Y” in the reference cell restricts the user to entering a negative number and anything else in the reference cell imposes no restrictions on the active cell).

Actually, the formula that Steve offered, below, works.

Thanks again for your efforts.

Regards,

Well, something that is kind of klunky but works for me is to use Views and Report Manager. You can store your print areas in Views, then create print reports in Report Manager to print them. Report Manager allows you to select consecutive page numbers.

If you’re not familiar with this approach, click on your View menu. You should see the command Custom Views. If you don’t see Report Manager you will have to enable it in Tools Add-ins.

What you do is create a print area, adjust your settings in the Page Setup dialog, then store it as a View. You can store multiple print areas from the same worksheet as different Views, if you wish, or just store one print area per worksheet. (When you name the Views it’s a good idea to use the sheet name as part or all of the View name.)

Then, after you’ve created the Views, open Report Manager and add the Views to your print report. (You can also add Scenarios to a print report if you have any.) Once you’ve created the print report you want, you then use Report Manager to print rather than the File menu.

As I said, it’s kind of awkward, but it works.

HTH,

Unwanted links can be problematic. To the best of my knowledge, there is no automatic method of breaking links in Excel 97. Microsoft recommends changing the link source to the current workbook, an approach that has worked for me in the past.

Also Post 30606 contains information about unwanted links and it also contains links (pardon the pun) to two utilities that deal with unwanted links.

HTH…

Legare,

Thank you for your interest and your approach. I haven’t had time yet to test it but I will try it today when I get a chance.

I’ll let you know how it works for me.

Thanks again,

Hi Andrew,

Thanks for your interest and your suggestion. I took a look at the post you suggested but I think I need to spend some more time taking a longer look. I agree that it would need some adaptation for my needs, but it looks like it’s very close to what I’m doing in Excel. I will take a longer, more thorough look as soon as I can.

The biggest reason I am doing this project is that my boss told me she wants it done More seriously, I think they are trying to do a study here of how certain formulas and functions are being used, why I don’t know. VLOOKUP is only a test formula; if I can come up with a viable approach (or I should say if the Woody’s Lounge community can), I think they will be looking at other functions and formulas, but that’s just a guess on my part.

Thanks again for your suggestion. I will let you know how I am progressing with your code when I have the time to take a longer, more detailed look.

Regards,

Hi John,

I just read through your code with much interest. I really like your UDF and I’m going to test it today. It looks to me like it will be REALLY useful for a lot more than this project.

I am amazed at the varied and creative responses I’ve gotten here. I can’t think of a better way to learn this stuff.

Thanks much for your continued interest and many thanks for your efforts. I’ll let you know how your code does for me.

Regards,

Wassim,

Thanks for your reply. I really like the website you so kindly suggested. I haven’t broken down and bought the book yet, but I probably will this week.

Regarding the daunting project: I can tell you with confidence that they will not pay for any type of outside help, so it’s my project, do or die I am slowly making progress, though, taking it a step at a time and searching for help in places like Woody’s Lounge.

Thanks again.

Sam,

Thanks for your suggestion. I’m finding this to be a somewhat daunting task, but breaking it down into manageable chunks definitely helps.

I agree with you about the slowness of data tables, but I have no control over that issue. This is a business model that a number of users have developed and continue to work on. They are far more familiar with data tables than with array formulas and the culture of the place is not such that change comes easily.

But thanks for the suggestion. I certainly agree with the philosophy of the idea.

Regards,

I read your other post and it sounds intriguing. It is creative and rather a departure from the norm. I will think about it and discuss it with the users, but I also like John’s idea of using names. Even if the users don’t like it, I will probably give it a try on my own time, just to see how it works.

Thanks for the suggestion.

That’s a great idea and I will try it. It is less klutzy than creating and hiding a lookup table somewhere.

Let me thank you again for your interest and your terrific suggestions.

Regards,

John,

Thank you for your posts, your interest and your really nifty suggestions.

Here’s my assignment. I have some users who have a large model that contains numerous data tables that take some time to calculate. What they want me to do is a two-step operation. The first is to give them a macro button that when clicked will select each table in the model and convert each table to values, thereby speeding up their model appreciably. I’ve got that much done so far.

However, step 2 is more challenging, at least for me. What they want there is to be able to click a second macro button to convert the tables back to actual tables, that is with formulas and not values. So it occurred to me that I could get the row and column input cells in the first step (before converting the table to values) and create a lookup table elsewhere on the sheet that would store the row and column input cell addresses for each table. Then the macro to convert the tables back to formulas could use the lookup table to get the appropriate row and column input cells for each table and go ahead and regenerate the tables.

So in this step I need to capture the table formula in order to extract the row and column addresses within the formula. I just used A5 and A15 as sample addresses for now in order to figure out both the logic and the actual coding.

That’s when I turned to Woody’s Lounge (once again).