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've been hoping that somebody else might venture into this hornet's nest of a spreadsheet and I could learn from their solution. But with over 50 views and no reply, I think all who've looked at it have despaired, as I did.
A suggestion, then: don't try to do all of this Issue #1 in one unintelligible formula. Create some "helper columns" to do parts of the task, and then incorporate the results.
But the reality is, this is probably a spreadsheet in need of fundamental re-design, or maybe conversion to some other software (Access?)....
I was hoping for your friendly post. Now I can get to the not-so-friendly post.
This is a *** workbook. 21 MB. Several hundred notes and textboxes.
The person who created this workbook shall be cursed forever.
I wonder if anyone really voluntarily works with this spreadsheet.
I wasn't able to delete columns/rows to make the file smaller. Not enough memory.
And I agree with your conclusion: Rebuild from scratch.
- Hey not so friendly poster, nice to meet you. I am the guy that designed it for use by...my private equity group. We buy commercial property nationwide and use this as a mock CRM to not only run multiple fairly complicated analytics but act as more of an App than a simple spreadsheet. The way it lays out, the conditional formatting and notes are made so that a VA or other lower end personnel could work through data entry and have answers to simple questions at their finger tips.
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?When I open a workbook I expect NOT to be confronted with a notes-cluttered screen.
A major reason for you big file size is that the UsedRange in sheet "01 - v2.22" is XE1048576. And the cells are mostly empty. You should delete those rows/columns. The same issue maybe with the other sheets.
Then there are at least two circular references in sheet "01 - v2.22" and in sheet "03 - eABC Responses". I don't know if this is on purpose or an error.
And I accidentally noticed that one cell has NO formula but an input text. That is CA26 in sheet "01 - v2.22".
The only way to "work" with the spreadsheet was to get rid of all sheets expcept the two mentioned above. And then copy the data and formulas in a new workbook. It was down to 1 MB.
Then I tried to understand your problem. But it is not clear what the desired result is.
For problem #1 you should try mathetes tip with helper cells. That is a faster way to identify errors or problems.
