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?
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.
- Hello Detlef. I appreciate your response this morning.
As to your entry sentence...
A...the notes contained on sheet 01 - v2.22 and 03 - eABC response are only there for those who would see this post as initial guidance into the problem I was having. They will disappear once solutions are provided. Also it is important to note, this project is an internal document we will use to breakdown the viability of assets we are in negotiations on. It functions as a real time negotiating and tracking platform to be used when in phone conversations with potential sellers of commercial property. I have exhaustively scouted the CRM marketplace including Goldmine CRM, one that I developed and used successfully while the owner of both a real estate and mortgage brokerage in L.A. I wanted to find an out of the box solution to these types of issues but nothing even got close. Not even something as simple as,
1. Send outgoing email...action: move thread to a long term trickle stage until the recipient responds.
2. Upon response, move thread to a pause stage while we make the seller wait up to 3 days depending on where in the negotiating process we are.
3. Once we take off the pause and send our reply, they go back into the 30 day trickle stage and that timing rolls on and on until we buy. If the seller and I never align the trickle continues on and on or until the property is pulled from market.
Every CRM from free to $1,000s/mo. We're incapable of this. They could automate mail merges and the like, but incapable of what we need. I am attempting a manual version in Excel. We'll see if works out or needs some tweaks or, is destined to fail. I have no idea where this ends up. And most importantly to complete my response in this area, this is not for public consumption. Maybe an assistant, virtual or otherwise might be plugged intl to duplicate volume...we'll see about that...
I saw your comment about a large range taking up a lot of file size along with a notation "XE1048576". I am not understanding what that sequence is referencing.
It might reference a set aside range of rows beginning at A12:UU1500? Within these rows are an extension of all formulas used to drive future data entries that span the range A:UU in this sheet. Each property and owner's information for each property, calculations, etc. encompass 1 row. When this goes live I will add around 50-75 rows of data per day. Some will have mostly complete data entry and some where only a couple of cells contain values.
I have used a similar system for different purposes except that it started small and 20 or 30 rows were added as needed. In that case, the addition of rows might have been 5 rows a month so I could do that manually without worry about mis-handling the copy/paste. I believe pre-adding a larger chunk will be easier and more consistently performing than smaller increments despite the increased file size. The more often manual intervention is introduced the greater the likelihood of mistakes happening.
You said you were not clear on my problem. I will try to break it down differently than in the inserted note boxes.
I am responding from my mobile now. Will send this portion forward and work on better explaining the problem(s) I am attempting to solve from my home computer. I'll send something over a bit later.
Sorry for the elongated response...so much easier on the phone!
I saw your comment about a large range taking up a lot of file size along with a notation "XE1048576". I am not understanding what that sequence is referencing.***If you press CTRL+END you will be at XE1048576. Which is the endpoint of UsedRange.
But it should be VP1501.
- I pressed CTRL + End on sheet "01 - v2.22" and the cursor did zip down to XE1048576 as you mentioned. I have no idea why this sheet thinks there is a range that has been at some point defined at all much less why it would end at that row. I have not knowingly defined any ranges in this sheet nor any other, I don't know how to do that nor what I might use it for. And, not all all sure where VP1501 comes into play. The last column in the data input section of this sheet is in column is currently column UU with formulas and possible inputs down to the row 1500. I have an temporary end column marker "x" in row 1501 to more quickly keyboard-navigate up and down when needed (CTRL + Upward/Downward Arrow). I apologize for the lack of understanding on my behalf. I'll need additional explanation or guidance on this one.
P.S. If what you are saying is true and correctable I can see why the size of this sheet ballooned like this. So, thanks for finding it in advance.