Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Text brough up from a different excel file keeping different font options

0

Hi all!

I have made two excel files. The first is a database with all the information I need to know for a product (eg. 1st collumn - code of product, 2nd - name of product, 3rd ingredients of the product, etc) and the second excel is a format that when you put the number of the code you are interested in, it appears all the data from the fist excel. As far as concerning the ingredients, they are all written in one cell for each product as a text and some of the words are in BOLD because we reffer to allergenic ingredients (for example: wheat flour, water, cheese, spinach, pepper, salt) . The problem is that when I use the #lookup or the #vlookup command, the text comes at the second excel without the BOLD words but all in the same font. I need to find a command that will keep the original font options that I have chosen at the first excel.

Could you PLEASE help me ?

Thank you in advance,

Fotini C, Greece

Answer
Discuss

Answers

0

The beauty of Excel is in that it inserts no sharp divide betweeen data and their presentation, as SQL or Access do. From a properly designed database it would be absolutley no problem to extract all allergenic or non-allergenic ingredients, all products that contain a particular ingredient or those, intended for the same purpose but not containing a particular one. Your database has none of these capabilities. It highlights certain ingredients - the job of a presentation.

You find yourself in the situation of wanting to create a presentation from a presentation. That's like making a river flow uphill. It can be done, with great effort, over a small stretch, for a limited time - but for which benefit?

Your database has already lost many, if not most, of the capabilities it should have by having allowed the design of a presentation to creep into this one field. To undo the damage you would now have to read each character in the field, plus its font properties, instead of reading the entire field, in fact you could read the entire column in the same time you now get a single character. Other than the sheer cost of getting such a program the effort isn't prohibitive in terms of time it takes to display a single item, probably less than a tenth of a second for each call. But the cost you should consider is the uselessness to which you condemn your database in regard to other tasks that should come natural to it. Here a list that must surely be central to your business is degraded to fit a single purpose which, in final analysis, is merely a convenience.

Obviously, a proper db would list each ingredient in its own column, perhaps with an "x" or Yes/No or even the quantity of it in the product (perhaps in the future), and separate columns for each ingredient's properties, such as allergenic: Yes/No. Such a db would be much easier to maintain than what you have and what now passes for your "database" would be easy to produce from it, as would be the item definition you are now chewing on.

As an interim or make-shift solution you do need to get rid of your bold characters. You can keep them as a means of presentation but not to define data. Your list of ingredients must be neatly comma-separated and the allergenic property might be indicated by additional characters, e.g. Salt (A), Pepper (A), Carotin, Spinach (A). Such a field would be machine-readable, thereby restoring most of a db's properties and capabilities to your data collection. It would be slow to work with and hard to maintain free of errors - for example, attention must be paid to place commas correctly, a task that doesn't exist in a real db and therefore can't cause entry mistakes there - but it would give you the capability to split the unwieldy Ingredients field whichever way you want, including make separate columns of it in the future.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login