Convert non-cell input to cell values

[TBD]

Ok, I realize the title was kinda rough, but let me explain.



I would like to make it so that I user can simply type items in the form of a tabbed list like this: (I had to use "-" instead of " " or tab because of the forum formatting)

Name 1
----Detail 1
----Detail 2
----Detail 3
Name 2
----Detail 1
----Detail 2

and have it come out in excel like this:

A---------- B
Name
--------Detail 1
--------Detail 2
--------Detail 3
Name
--------Detail 1
--------Detail 2



I want to do this because then I can set up formulas to automatically formulate whatever is necessary to match the required information, while also being able to search each entry for keywords as needed. This can't all be done in cell however, as you cannot press enter in a cell, and if you do anything funny after tabbing over to column B then pressing enter will simply put you to the next row of column B, instead of the next row back in column A. So is there any way for me to pull data from a text box? Or to have excel open a text document, save it under a specific name, and then pull data from it after the user saves it again?

I'm trying to make my worksheet dynamic and idiot proof all in one go, and it's proving rather difficult.

[BobBridges]

Well, I'm not sure I understand the exact details, TBD, but the impression I get is that you want to enter data with as few keystrokes as possible and to figure out how to make sure each value ends up in the proper....what? Column? That is, you want the end results to be this:

  A       B
1 Name 1
2         Detail 1
3         Detail 2
4         Detail 3
5 Name 2
6         Detail 1 
7         Detail 2

...only without having to do a lot of laborious arrow-pushing to get each value into the right cell?

Pending your clarification ("no, I meant something else..."), there oughta be a couple of ways to do that. Depending on how much effort you want to go to up front:

a) There's a setting in Excel that tells it where to move the selection after you hit <Enter> on a cell. In Excel 2010 try <Alt-F>,t to open the Excel options, then select the Advanced section and, under "Editing Options", look for "After pressing Enter, move selection", which lets you decide what direction it moves; you say yours is down, which I think is the default, but I always turn it off so the selection stays where it is. There doesn't seem to be a down-and-right option, but maybe this'll be a help.

b) You can enter the data all in one column and then use Excel functions to split it up and/or move it around.

c) The same from another angle: You can enter the data in an external text file, then import it into Excel.

d) You can write a VBA program to help you look at the value of each cell and decide what column it should be in.

But the question I keep coming back to is this: If you're asking this question, it's because you have a lot of data to enter. (If you had only three lines and four columns of data, you wouldn't be here looking for an easier way; the job would be done already.) And if you have a lot of data to enter, then where are you getting it from? Surely it's not on paper, is it? I mean, can't you just import it straight from the source, from the web or where you have it, and then do a few things to format it correctly?

[TBD]

Very close

There's a lot of data to enter, and I want as few keystrokes as possible, but the data doesn't exist yet, and will be entered multiple times into multiple renditions of the sheet by multiple people. That's why I want to try and keep some level of uniformity, but the result needs to be able to be a bunch of things.

I suppose another way to describe all this, is that I'm intending to turn excel into a compiler for my own custom programming language that will apply only to my worksheet. I realize that this means I'm not really using excel for what excel is supposed to be used for, but there are so many other things that I want the worksheet to do that only excel can do better than most anything else.

I feel like I'm still not explaining this right, so I'll address each one of your suggestions and see if that doesn't help clear up what I'm trying to do:


a) There's a setting in Excel that tells it where to move the selection after you hit <Enter> on a cell. In Excel 2010 try <Alt-F>,t to open the Excel options, then select the Advanced section and, under "Editing Options", look for "After pressing Enter, _m_ove selection", which lets you decide what direction it moves; you say yours is down, which I think is the default, but I always turn it off so the selection stays where it is. There doesn't seem to be a down-and-right option, but maybe this'll be a help. Multiple copies will be going to several different computers, so I don't want to rely on a change in settings. Regardless, this is also only one section of a much larger document (sort of a win-all kind of worksheet with a point-and-click interface using graphics and page links with over 20 sheets... 8mb so far.)

b) You can enter the data all in one column and then use Excel functions to split it up and/or move it around. A very simple solution, and one I greatly considered, but I ran into the issue that it could get difficult to review all the data by eye when entered that way. Additionally, if the end user (not myself) decides that they want to insert data in the middle somewhere, it requires a fundamental understanding of excel to copy and paste or insert rows (which none of them have, and they seem to have no ability to learn)

c) The same from another angle: You can enter the data in an external text file, then import it into Excel. That sounds like exactly what I'm looking for, but I need there to be a button in the spreadsheet itself that will automatically create and name a new text document, and then I need it to import the data when they're done typing without any other interaction (see previous comment on their alarming technical ability) ...Is there any way to do all that? Even if it doesn't make a new document, I can probably find a way to slip a pre-made text file in the same location as the excel sheet, but I would still need all those other criteria to work, regardless of the folder structure on the computer.

d) You can write a VBA program to help you look at the value of each cell and decide what column it should be in.
I'm not too familiar with VBA. But this seems more like a replacement for the formulas I was already planning to use (and a very good replacement I'm sure) rather than making this any easier for the end user.

At its core, I'm writing a worksheet for a bunch of technological cavemen, that's designed to solve all their technological problems in regards to a specific and repeating task that we want to make go as smoothly as possible... without buying special software or hiring a coder.

Thanks for the help!

[BobBridges]

Decades ago I used to get frustrated with other users: Maybe automating the work isn't as fun for everyone else as it is for me, but why couldn't they see how valuable it is? Eventually I realized that if everyone were like me, no one would ever get any work done; the world would be full of people who just wanted to find slick new ways for other people to get their work done. So the world needs a few of those to make the work go faster—but only a few. Oh, this sounds like a fun project!

Ok, I understand better about the background. The main point, as I see it, is that lots of people are going to be entering the data, and you need a way of a) getting that data into a standard layout, and b) dealing with all their departures from the standard with as little manual effort as possible. You won't be able to invent any method that can deal automatically with every case ("it's impossible to write a foolproof program because fools are so ingenious"), but you want the least effort possible.

By the way, one important assumption I'm making above is that these folks will enter the data and then you'll be the one who makes sure it's ended up in the right format. You didn't quite say that, but it seems obvious because someone has to oversee the process, explain "no, not like that, like this", correct mistakes etc.

If I were doing this project I would need two things: A) I'd have to invent the best (ie easiest and least error-prone) way for the users to enter the data. And B) I must know exactly what the final format should be, whether that's imposed on me by someone else or I have to invent it. Given those two, I know I can work out how to translate from A to B.

And so can you; if you can't now, you will be able to by the time we're done. We can write either a set of Excel function or (more probably) a VBA program that will quickly and reliably transform A into B...once we know what A and B are.

And to answer your question below, yes, you can have them enter it into some other receiver (a text file, or Word, or whatever else works) and then import it into Excel. The catch is that your functions, or VBA program, have to be able to tell by looking at the text where to put each datum. That can usually be done, and sometimes without even much ingenuity.

Tell me first, then, about A: What is the (exact) format you want for the final result? An example is best, using real data in their real columns. I specify real data (not just "Name 1" / "Detail 1") because sometimes if I know what the names and details are it's obvious how to teach Excel to differentiate between them; perhaps the names are all text and the details are all numeric, or the first detail is always a month and the last one is always a dollar figure, or something. Other times it's not that easy, but that's where we have to start.