using a conditional formula from a drop down list and If Statement

[David J]

B2: is a drop down list of 40 Adlet ID Numbers, C2: is and input box of the
number adlets played based on the adlet ID selected in the drop down box, D2:
is and input box of the number of K2P's based on the adlet ID selected in
the drop down box, D2: is an input box of keythrough ratios based on the
adlet ID selected in the drop down box. Note all of the input boxes are
static numbers that require no calculation. now that this is in put this
information needs to be sent to the analysis work sheet w2: cell b2: needs to
have have the input information added to the previous number updated in this
cell from previous inputs again this is based on the adlet ID selected in the
drop down box each time. Now I need this info to up date each adlet ID on the
second work sheet based on the input information from each drop down
selection.

I hope this makes sense.

 

[Bob Bridges]

It doesn't, very much; it'd be better had you used more punctuation. Let me
try to rephrase and you tell me whether I have it right:

Sheet X is basically an input form: In cell B2 you have a dropdown list of
IDs, and in C2, D2 and E2 (you said D2 twice but that's a typo, right?) are
some "input boxes", by which I suppose you mean text boxes. Not sure why you
need text boxes when the user could just type into the cells directly, but
let that go: The user is expected to select an ID from the dropdown list in
B2 and then enter some numeric values for that ID in C2, D2 and E2. Each
time the user has entered the data for a single ID, your program should go to
sheet W2 of the same workbook, look up the row for that ID and add the values
the user entered into C2, D2 and E2 into the corresponding sums (we'll
pretend they're also in C, D and E). When the user has finished entering all
data for the time being, the program (or another one) should take all those
values totaled up on W2 and "update" - you didn't say how - corresponding
values on a third (you said "second" but we're already got two) worksheet
which I'll call Z.

Is that right? If not, correct it please. If it is, by what operation is
the data in Z to be updated based on the data in W2? Should it be added
again (and if so, why didn't we just add it to Z in the first place?)?
Should the W2 numbers replace those in Z? What?

 

[David J]

Bob,

Thank you for your quick response, yes the second D2 was a typo and I meant
just enter the data in the cell when I said input box, with that cleared up,
Let proceed,

In cell B2 you have a dropdown list of IDs: Answer Yes

and in C2, D2 and E2: Are where the data is entered: Answer Yes

The user is expected to select an ID from the dropdown list in B2 and then
enter some numeric values for that ID in C2, D2 and E2.: Answer is Yes

Each time the user has entered the data for a single ID, your program should
go to
sheet W2 of the same workbook, look up the row for that ID and add the values
the user entered into C2, D2 and E2 into the corresponding sums: Answer Yes.

Note this is for a live track and test program so the info that is updated
here needs to accumulate because the info is entered every 30 minutes or so
during testing.

what operation is the data in Z to be updated based on the data in W2: The
fields in Z are the accumulated data from the updated fields C D E from W2

Bob I hope this makes this a bit more clear.

David Jones

 

[David J]

Bob,

One other thing I felt might be of help, there are other fields that need to
be added, I thought once I had the basic formula down or how to build it I
could take it from there. But it probably helps if I named the fields for
you.

B2: Is the adlet ID from drop down.
C2: Is the number of adlets played.
D2: Is the number of times the web surfer keyed through (IE: K2P)to the
website.
E2: Is the K2P ratio between number of plays to the number K2P’s
F2: Is the number of times the surfer K2P to the site by "date"
G2: Is the number of times the surfer K2P to the site at by "time of day"
H2: Is the number of times the surfer K2P to the site by "Country"
I2: Is the number of times the surfer K2P to the site by "State"
J2: Is the number of times the surfer K2P to the site by "City"

This information is pulled from the analytics system in the back office of
the running campaign, and is input into this spreadsheet by hand but the info
is spread out over 12 different locations, the sheet I am trying to build is
bringing all of this information to one location, and Z sheet is an
accumulation of all that data so I have a quick view of what is working and
what is not.

David J

 

[Bob Bridges]

You were probably right to mention those other things, but I agree that once
you have a general idea of how to go about it, with a few examples, you can
expand it to them. So since I have chores to do this morning, I'll ignore
the additional values for now; if you have trouble with one of them later for
some reason, bring it up then.

 

[Bob Bridges]

Ok, I think I mostly understand what's going on. But you say "accumulated" -
do you mean "added" (as opposed to "averaged" or some other operation)? Or
are the raw data to be "accumulated" in the sense of just listed so you can
review another time?

And if arithmetically added, I may not need to know this but why add them to
two sheets instead of to just one? Is W2 a daily sheet and Z a total for the
length of the test, or what?

Now, in your first post you never actually asked a question or said what you
need. I presume you want a hint as to how to write some part of the code
that is to accomplish your purpose...but which part are you having trouble
with? If you already have a working drop-down list and text boxes then you
apparently know how to write VBA code, so your program is already reading
those values. Does it also know how to identify the fields in W2 and Z that
it should add those values to? What I'm asking is this: What does your
program look like so far? If you're having a problem with one part of your
logic, which part is it and what problem are you having? Or to put it
another way, what's your actual question?

[Yes to all of that with the following additional comments:]

Note this is for a live track-and-test program so the info that is updated
here needs to accumulate because the info is entered every 30 minutes or so
during testing.

what operation is the data in Z to be updated based on the data in W2: The
fields in Z are the accumulated data from the updated fields C D E from W2

Let me try to rephrase and you tell me whether I have it right:

Sheet X is basically an input form: In cell B2 you have a dropdown list of
IDs, and in C2, D2 and E2 (you said D2 twice but that's a typo, right?) are
some "input boxes", by which I suppose you mean text boxes. Not sure why
you need text boxes when the user could just type into the cells directly, but
let that go: The user is expected to select an ID from the dropdown list in
B2 and then enter some numeric values for that ID in C2, D2 and E2. Each
time the user has entered the data for a single ID, your program should go to
sheet W2 of the same workbook, look up the row for that ID and add the
values the user entered into C2, D2 and E2 into the corresponding sums (we'll
pretend they're also in C, D and E). When the user has finished entering all
data for the time being, the program (or another one) should take all those
values totaled up on W2 and "update" - you didn't say how - corresponding
values on a third (you said "second" but we're already got two) worksheet
which I'll call Z.

Is that right? If not, correct it please. If it is, by what operation is
the data in Z to be updated based on the data in W2? Should it be added
again (and if so, why didn't we just add it to Z in the first place?)?
Should the W2 numbers replace those in Z? What?

 

[David J]

Bob,

Your right I didn't ask the question and I am sorry, I kind of figured out
how to build a dropdown list I am not sure the way I built it will allow you
to read the data in it, but I would assume so and if it took VBA to do that I
had no clue, I just followed the instructions.

When I say accumulated here is what I really mean: I log in check the stats
of my campaign, and enter them into the spread sheet, this includes all of
the fields I have mentioned, I check them again 30 minutes later and I enter
those figures again. Now for the accuracy of the read outs I have to have a
sum total of all readings I am entering into each cell. Same goes for each
individual cell.

Example After the campaign is started, I ran 25000 ads of a 100,000 ad
campaign, but they were entered 3 to 5000 at a time, but I still need to know
that I ran 25,000 This will be the same with all of the other cells.

Now when I go to Z and look at my findings, I see I have ran 25,000 ads, and
2500 has clicked through to the website, and that I have a 10% key through
ratio and mostly they are playing between 5 am and 9 am each day, and
primarily from the US, from the states of Texas, Utah, Fla., And New York
that all shows on the one page Z as a report of all of the information
collected and this way I have a window of all of the 12 different locations
of the analytic back office which is where I collected the information for
thei part of our campaign. Now its all in one nice easy to find and read area.

Thanks David J

 

[David J]

Bob,

I did not see any response from my last post, do I need to repost this
issue, for further assistance, if you don't have time I understand.

David J

 

[Bob Bridges]

David, I apologize for never getting back to you. I'm sitting here this
Thanksgiving day catching up on old email and I see there are a number of
these that I just dropped about that time; I wondered why, for a while, and
then remembered that's when I got my new job and moved to a new city, so I
was pretty busy then and I guess I forgot all about it. Understandable, I
suppose, but inconsiderate of you and I'm sorry.

If it's not too late, I'd like to take this up again. I didn't really
understand what was going on, but I want to, not only because I like puzzles
but also because I want to understand how web advertising works; I figure
after helping you with this I'll understand much better what market forces
are at work behind the scenes. But if you've already solved the problem,
I'll understand.