Forum Discussion
Problem with getting CONCATENATE text combined with an IF/THEN and nested VLOOKUP to display...
So as I mentioned above the line:
IF(AND(U12>0,T12>0),CONCATENATE(VLOOKUP($U$12,W290:X299,2)," ",VLOOKUP($T$12,W304:X313,2)),""))&CHAR(10)&CHAR(10),
is also TRUE because text values are > numbers so AND(U12>0,T12>0) resolves to be TRUE (again highlight that part and you will see TRUE in the 'bubble') and that line does NOT concatenate the "P.S., " before the VLOOKUP result and is why you weren't getting that P.S. part before. Now you are getting the correct line T12=" " AND the incorrect line T12>0.
You can fix it by adding the check or if you never have 0 just replacing it with ISNUMBER(T12).
As for the difference between "" and " " let's think about a balloon that you take out of the bag that isn't inflated and the balloon after you inflate it. I would call both a balloon but they certainly aren't the same.
Another way to look at it is that don't you think" " is different than " "because the former has a lot of spaces while the latter is only 1 space so why wouldn't you think "" is different than " "?
I would have preferred to have an actual App developed but the problem with going down that road is the frequent need to modify strategies and the calculations that become necessary due to them. Also, we need to be able to track thousands of properties as they work through our acquisition pipeline. I have tested and/or played with numerous CRMs and from Sales Force to Goldmine to...you name it. Nothing does what we need. Excel provides a flexible platform for our needs. It may not fit your vision of cutting edge and contemporary Excel formula usage but it does get the job done. My post wasn't to get into a conversation about, "do you guys like my work or not". It was to answer a somewhat simple formula problem I couldn't figure out on my own. It is fine if you choose not to do what I posted about and instead look at things that are absolutely irrelevant to my needs. If you can help, that would be great...there are two issues that I outlined within two inserted notes. You can pass this rather lengthy explanation to the other gentleman as well...
You guys make a good case for only giving as much information as you can handle rather than being open about the broader picture. Live and learn...yah?
You can pass this rather lengthy explanation to the other gentleman as well...
I guess that I'm "the other gentleman"--your post is open to everybody to read even if you were responding primarily to my on-line colleague Detlef_Lewin. So I'll venture to reply.
First, FWIW, I did offer a possible solution to that presenting problem: break up that multi-leveled nested formula into several "helper columns," with each doing its own thing. Then pull those results together with a simpler, more intelligible, formula. The point being that trying to create a single formula, multi-layered-nesting and all, can be satisfying when it works, but is not considered good practice, if only because it is very difficult to debug, almost impossible to maintain, and that's even if you were (as you are) the original creator.
Another couple of suggestions, if I might (based on reading professional Excel resources; I'm just an amateur myself), having looked at and tried to understand your workbook:
- work to separate input, and the basic data tables that contain that input, from areas that process the raw data and definitely from the output -- mixing all those three essential but distinct components also interferes with clarity and can interfere with Excel's power. This might mean, in your case, having separate workbooks that call on one another for essential data. "Might mean"--I'm not saying it must mean. But it definitely would mean separate sheets for input, processing and output. As Detlef_Lewin has pointed out, that current workbook is so very large that it stretches the limits of most computers. Separating the components could make it both faster and less resource demanding.
- hold off on elaborate formatting--in particular things like merged cells, those fancy items across the top--because all of those bells and whistles can interfere, in various ways, with smooth functioning. Add them after it's all functioning, by all means. But here's where that first point comes into play--you can add formatting to the output sheets for the sake of clarity, pop, impact (whatever), but keep the input and processing areas clean and clear. Use something like a yellow background in cells for data entry, but you don't need to make input and processing pages all that colorful; they're meant to be in the background.
Good morning Mathetes and thanks for your response. I didn't mean to intimate any negative connotations upon you by referencing you as "the other gentleman". Only that you had commented and he connected some of his reply to yours. Responding to his less friendly reply came to mind first.
It's hard to take a glance at something like this without understanding the rationale behind a certain layout/design methodology.
That said, I do understand what you are saying in terms of breaking it up into multiple sheet and have to some degree done that here. I pull data from v2.22 into eOpener and eABC Response. But, v2.22 is the central landing zone for all data. And needs to be that way for practical use purposes. It is a live running track for data gathering amd trickle timing between the user, in most cases that is me, and my counterpart (the seller, commercial broker, lawyer or other family rep.). This is used a track to not only gather info required to map negotiations, due diligence requirements, acquisition Capital stack to stabilize/optimize the asset then conversion to long term financing and eventually proforma (projected) performance of the asset over time.
I will see how I can add helper columns and other ways to lessen the load including spreading the work between different workbooks. It is just far more convenient to have everything in one spot. Database management is best done in one single database and given that the main data entry point must function as a real time communications tool I don't know how it would function if it was spread out amongst more than one sheet much less across multiple workbooks. I can play with it and see if I can arrive at a practical solution.
I appreciate your input on this...