You have understood one thing correctly: a preposition is combined with what follows it to form a prepositional phrase, and they form a single constituent in the sentence. Generally speaking, words in English govern (ie. control or specify) the words that come after them. In linguistics, we say that English is right-branching, meaning that new syntactic elements come after (to the right of, in writing) the elements that govern them.
Note that there are exceptions, such as adjectives, which precede the nouns that govern them. English is not exclusively right-branching, but it is predominantly right-branching.
But what does this have to do with prepositions? Well, just as a preposition governs the noun phrase that comes to its right, the preposition itself is governed by something to its left. And in many cases, that thing is a verb. English is full of idiomatic combinations of verb + preposition, where the verb requires a specific preposition to follow it, and anything else is an error. To take some obvious examples cribbed from other answers:
I converse with you. [Not to/at/of you]
They rely on the bus. [Not with/to/at the bus]
These combinations are highly idiomatic, meaning that the correct choice of preposition cannot be predicted simply by knowing the general meaning of the words involved. So the people who ask about what preposition follows a certain word are asking a reasonable and intelligent question. The choice of preposition very, very often depends on what came before it.
I wouldn't object to appendix for, appendix of, appendix behind, appendix following, and so on, but by the NGram, appendix to is favored by a wide margin:

There may be some false hits here as appendix can also refer to an internal organ, among other things, but since those uses would be overwhelmingly appendix of (e.g. The appendix of a ruminant, unlike the appendix of an ape, is a true appendix, or cecum), the point stands.
Best Answer
It seems to me that the headline question here—"What preposition is used after 'enthusiastic': 'for' or 'about'?"—differs fundamentally from the body text question, which appears to be "Which works better—'get enthusiastic and hopeful for' or 'get enthusiastic and hopeful about'?"
The obvious difference between the two questions is that the headline question asks whether to use for or about after the word enthusiastic, while the body text question asks about whether to use for or about after the phrase "enthusiastic and hopeful." Because readers are far more likely to run afoul of the headline question than of the body text question, my answer addresses the former.
In my view, uncertainty about whether to choose "enthusiastic for" or "enthusiastic about" is due to the influence of a separate pair of phrases—"enthusiasm for" and enthusiasm about." The problem is that "for" is the more common preposition in one of the phrase pairs and "about" is in the other.
Here is an Ngram chart tracking the relative frequency of the phrases "enthusiastic for" (blue line) versus "enthusiastic about" (red line) versus "enthusiasm for" (green line) versus "enthusiasm about" (yellow line) for the period 1800–2005:
Year in and year out for the past century, as the chart illustrates, "enthusiastic about" has been considerably more common in published writing than "enthusiastic for"; but during the same period, "enthusiasm for" has been vastly more common than "enthusiasm about." That is, idiomatically—and notwithstanding the seemingly contrary directions their preferences take—English writers (and presumably English speakers) show an overwhelming preference for "enthusiastic about" and "enthusiasm for."
So (removing "and hopeful" from the example sentence in order to focus on which preposition works best with enthusiastic/enthusiasm), we have two idiomatically favored pairings, either of which sounds perfectly natural:
and