The last couple of weeks have been hectic and we have been busy like bees, uploading the Dashboard 101 tutorials. This has kept me away from the blog—thankfully not for too long—and from the CompTraX updates.
I am back on track now and today, I would like to explain a set of formulas that I used while building the first version of CompTraX
You would have noticed that there is a sub-section on the Display Sheet of CompTraX that displays recent news.
This section fetches the data from the Database Sheet with the help of a spin button (circled in red above). The cells which hold the relevant data in the database sheet look somewhat like:
These cells have different news items in the same cell; separated by a line break (I have added color to different items to differentiate them in the pic). However, the Display Sheet shows the news items one by one on clicking the spin button. So, how is this accomplished?
The key lies in a group of four cells on the Process sing Sheet which looks something like this:
Cell 1: takes input from the data base sheet i.e. the group of news items
Cell 2: takes input from the spin button and helps decide which news item to show. E.g. in the image above the value ‘1’ means—show the first news.
Cell 3: has a formula that adds an identifier to demarcate the start and end of a news item. The formula is:
= SUBSTITUTE (SUBSTITUTE (A44, CHAR (10),”start1start”, B44), CHAR (10),”stop1stop”, B44)
This formula replaces the first instance of a ‘line break’ with the keyword ‘start1start’. Then it again acts on this modified string and replaces the first instance (which would earlier have been the second instance) of ‘line break’ with ‘stop1stop’. So we have a demarcated news item. Something like this:
‘start1start news item stop1stop’
Cell 4: has a formula which extracts this demarcated news item. The formula is explained below:
It might seem a bit complicated at first look, so, I will try to explain it using another example—see the picture and the maths below.
Let’s say cell ‘A44’ has some text value. Formula in Step 3 adds a start and stop position to this string. Say this is stored in cell ‘C44’. Now, Step 4 extracts the demarcated news item from this string. As mentioned earlier, the entire set of formula is dependent on the spin button and with every click of the button the next news item is extracted.
That’s all for this post; hope you find this useful. In the next post I will upload a new version of CompTraX which would have a search interface. So, stay tuned and keep Excelling!