Text entries into data table?

[Musical]

Hi, Im not new to Excel (2002) but my knowledge level only scratches the surface. Ive just learned how to do the basics in charts and using a data table. From what I understand so far, I can put numbers into the data table and the chart will update. That wasnt hard to figure out. Can I achieve the same result by entering text instead of numbers? Perhaps the best way to ask my question is to give an example, but first, my objective is:

I want to plot the events of a scenario on a daily basis. Ive got 31 days of a month along the horizontal axis (category axis). The various aspects are represented by the value axis or vertical axis, also represented in the Legend.. Each aspect has anything up to around 10 variables, and reflects that on the chart by entering a number Ive allocated to that variable. Unless youve got an elephants memory its a very un-elegant method I suspect.

Example: Suppose I want to plot the rainfall where I live on a daily basis for a month . Each day is represented along the horizontal axis by 31 cells. So, for each day I can simply punch in the amount of mls where applicable and the chart shows the right amount. However, what if I want to include, say, wind direction as well? 1/North (NTH) - 2/Nor Nor west (NNW)- 3/North west (NW) - 4/ West (W) - 5/ South west (SW) - 6/South South west (SSW) - 7/ South (S) - 8/South South East (SSE) - 9/South East (SE) - 10/ East (E)- 11/North East (NE) - 12/ North North East (NNE). .... Rather than having to try and remember the right number allocated as in the preceding example, and punch that in, it would make more sense to just enter NW or E and so on. Is there a way to do this thats not too complicated? or, could someone guide me thru the necessary steps? I hope my question makes sense.

Any help much appreciated.

Musical

[teylyn]

Hello,

why don't you just enter SSW or E etc. into the text cells to capture the wind direction? What do you want the cells to show? How does that relate to a chart? Do you mean a graph when you say "chart" or do you mean a table?

Maybe you could post a sample file and explain in context what you want to achieve.

cheers,

[Musical]

Thankyou teylyn for your quick response.

When I use text in the data table, the corresponding chart/graph/table doesnt update and show anything. I know this is probably something so simple to those who know what theyre doing, but hard to explain for a noob.

I used the rainfall /weather scenario purely as an example, as the real subject is based on personal info so I dont want to send you that. Maybe my example wasnt good.

My understanding is that 'charts' in excel is an alternative way of viewing a collection of data which gives you a better overview at a glance. To do that you need to input data into the data table. My Excel 2002 calls these 'charts', a graphical display of bars and/or lines etc. so maybe they are called graphs and tables as well, I dont know.

I can still achieve what I want in the end, but my way is cludgy and Im sure theres a better way of doing it.

Are we allowed to post piks/screenshots in this forum, or only excel files?

Musical

[teylyn]

Again, can you post a sample file? Where do the text bits go in the chart?

There are certainly ways to achieve what you want to do, but without more detail I cannot give you the steps, because I don't know what exactly you want to do.
You can post Excel files and pictures. Excel files are better, though. Pictures are fine if you need help with Photoshop. For help with Excel, post Excel files. Create a workbook with some dummy data and a chart that looks like the one you want to show. Then explain where the text should go.

[Musical]

Thanks again teylyn, your response is very much appreciated.

I will try and set up a dummy file tomorrow some time as its bed time for me right now.

Musical.

[teylyn]

Bedtime already? (joking )

I'll be happy to wait until you have put together a sample file.

cheers,

[Musical]

Hi teylyn, ha ha bedtime indeed, for all the good an early night did Oh well.

I thought Id stick with a weather example to make a dummy chart.. Another part of my (real) objective is to have the chart showing enough detail without having too much clutter not to mention too many printouts. For example, I wouldnt want to build a seperate chart for just one type of event even if it does have a number of aspects.

In my Example File Ive done 3 days worth of weather for the month. Rainfall is easy as I just type in the number of mls. Likewise, with temperature. 'Cloud cover' however doesnt relate to any definite numbers directly, but I still want a bar to represent a 'how much' scenario on a scale of, say, 1 to 10, but with certain increments meaning something definite. Something like this:

10 = Heavy
7 = Med Heavy
5 = Med
3= Med Light
1= none

So in a nutshell rather than try and remember 7 = Med Heavy and punching in 7, I would like to type Med Heavy into the data table and have Excel 'translate' that into 7 on the relative bar in the chart. That is exactly what I want and the data table doesnt update the chart if I put text in. This still might be a poor example but hopefully it suffices to show what I want.

In my real scenario, I would simply have too many numbers and their 'equivalents' to remember. It would be far easier to just punch in the text.

Musical

[teylyn]

Hello,

there are several ways to tackle this. If I were to do it, I'd use a separate table for data entry. For the cloud cover, I'd use data validation with a list. List values would be

10 - Heavy
7 - Med Heavy
5 - Med
3- Med Light
1- none

The source table for the chart would refer to these data entry cells. For the numeric values, it would be a straightforward cell reference. For the text values, I'd use a formula, for example:

=IF(ISTEXT(B28),TRIM(LEFT(B28,2))*1,"")

In B28 I have selected a value from the data validation drop down, for example

7 - Med Heavy

The formula checks if B28 contains any text. If it does not (that is, if I have not yet entered a value), it will return an empty text string. If it finds a text entry in the cell, it will take the leftmost two characters, remove any leading or trailing spaces and convert the remainder into a number by multiplying with 1.

This is now a number that can be charted.

See attached.

Could that be an approach?

You can also set up the values for data validation lists on a helper sheet (easier to maintain). Depending on what you need to extract from the cell, there could be different approaches. Instead of trimming the entered value down to the leftmost number, you could also use a lookup formula and a table where text values get mapped to their respective numeric values.

Let me know what approach you'd like to pursue.

cheers,

[Musical]

Hi Teylyn, youre definitely getting a pat on the back. You have GENIUS status in my view. Thankyou for your valued contribution.!!!

In short, I went from this: .... to .... to this I think I'll leave out the 'cool' one for a while yet though, ha ha. After a little playing around, looking at the help menu and re-reading your post numerous times (and managing to crash Excel a couple of times) I finally think Ive got the basics.

I went from ZERO knowledge about 'data validation drop down' to being able to put it in place which also got me to learn how to add another dummy row to my chart. I copied your text string into the relevant cells and finally got my bars to show. GREAT.

Now, do you have a 'shortcut' tucked up your sleeve when you put the text strings into the 31 cells on the source data table, or did you have to type them indivually? (obviously the string has to point to each individual cell)

Even though Im marking this solved, if you are up for it and have time, I would love to see your 2nd option using the lookup formula method.

Again, thankyou heaps.
Musical

[teylyn]

Hello,

I'm not quite sure what you mean with this:

put the text strings into the 31 cells on the source data table, or did you have to type them indivually? (obviously the string has to point to each individual cell)

What text string? Do you mean the data validation or the formula?

You can copy cells with formulas or cells with data validation just like any other cell.

For data validation: Set up the data validation in an empty cell. Test it. If you're happy that it works, select the cell and hit DEL to clear the value. The data validation will stay intact. Now you can copy the cell and paste it to all the cells that need that data validation. With a list data validation, you should see a drop down arrow as soon as the cell is selected. Click it and select a value.

If that's not what you meant, then please try to rephrase your question.

cheers,

[Musical]

Hi,

Ive got Data Validation working after I set up my own list following your example, that is, once I found it on Excels menu. From there it wasnt too difficult to figure out. I just highlighted all the cells I wanted the flip menu to appear in, and after I wrote the list, it was all good. As I said in my previous post. ...

"I went from ZERO knowledge about 'data validation drop down' to being able to put it in place..."


For the 'text string' thing, sorry I meant formula. (please bear in mind some of the terminology can get mixed up with us newbies ) Below, Ive put the necessary increments in bold. I took it as obvious that you would need to change these for each of the 31 cells as explained below, other wise Im thinking everything from your data validation flip menus would keep going to one cell. A useless scenario.

=IF(ISTEXT(B28),TRIM(LEFT(B28,2))*1,"")
=IF(ISTEXT(C28),TRIM(LEFT(C28,2))*1,"")
=IF(ISTEXT(D28),TRIM(LEFT(D28,2))*1,"")
...and so on.... to 31

...it was then but a short test for me to just click something from the flip menu and see the source table was updated as well as input on my chart. (GREAT!!!) OK it took me a good part of the morning to figure this all out but I got there in the end. .

So, rather than changing the letters for each cell in your formula by typing it in each time, did you find a quick way to do it? Its no biggie to have to copy&paste your formula into all cells then manually change the letter but Im into time savers all the same.

Musical

[teylyn]

Enter the formula into the cell. Then click the Fill Handle in the lower right corner of the cell and drag it across. The formula will adjust in each cell to reflect the current column.

[Musical]

WOW you little beauty! Thanks again for your help.

I wouldve given you another pat on the back but for this....
"You must spread some Reputation around before giving it to teylyn again."

Musical