Issues Displaying String Of Text From A Formula

Hey!

First post here, just wanted to say I look forward to helping you guys out as much as I can!

I'm currently coming across this issue in a new version of excel, I'll spare some of the details as this sheet is sometimes used to house confidential data.

We run our CPQ system through Excel, not the best I know but we've been doing fine until this niggle I discovered this morning when trying to add a new 'Model' of our product to the sheet.

The way it currently goes is;

The sales person sets the 'Model' of machine the end user would like to recieve, in turn this populates the quote with the correct model name and accessories.. The 'Accessories' are as follows, 'Dual Sweep' 'Single Sweep' and 'Simultaneous Sweep'

These are all held in the backend using a sheet called 'Reference' and are selectable through the main page 'Cover_page'.

On the page 'Quotation' is where the formula I'm having issues with is located, the formula is as follows:

Please Login or Register  to view this content.

So, the first thing I thought to do was to go through and check that all the cells were correct on the reference sheet, which it would appear they are.. I'll break it down into what each function achieves below.

Please Login or Register  to view this content.

This part of the formula Displays what model we need to display on the quotation page.

Please Login or Register  to view this content.

This part shows what 'Accessory' we need added after the previous input, so if it finds that 'Reference!N59' has no value (FALSE) we assume that the model is going to have the 'Single Sweep' accessory (As this is standard).

Please Login or Register  to view this content.

This last part adds the size of the machine in cubic meters, I've supressed the actual sizes due to privacy concerns, all that's replaced is the 'MODELSIZE' text.

So, to finally get to the issue:

The text is supposed to display as follows: "MODEL1 Dual Sweeping Equipment with a MODEL1SIZE Capacity Hopper"

Both models 'MODEL2' and 'MODEL3' display as they should, but 'MODEL1' only displays the model name, not the rest of the text. I've been scratching my head about this for hours, so any help would be massively appreciated :)


MORE INFO:

On the reference page, all links have been checked and confirmed to be working.. Over 300.. individually checked :rolleyes:

The way the reference page works, is when a checkbox is selected on the main screen it turns a value to either 'TRUE' or 'FALSE' depending of it has been selected or not.


If any more information is needed, please feel free to ask! :)

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Instructions (Please Read Carefully):

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Not sure about your actual data, but in your formula I removed some parts and added some "":

Formula:

Please Login or Register  to view this content.

Hi Paul,

Thank you for the input, I will give this a go now!

Something like this, but be careful at merged cells to reference them only by first cell:

Formula:

Please Login or Register  to view this content.

Hey Paul!

Your input is much appreciated,

This seems to have fixed the issue... the only problem being that the 'Capacity Hopper' bit doesn't show on the end when selecting MODEL1

Probably like this:

Formula:

Please Login or Register  to view this content.