Dropdown to give value to ANY cell

[ducecoop]

Excel 2003
Lets see if I can even explain what I want to do.

will have a date column
Will have a total calorie column (this will be a sum of several column containing individual foods)

On a separate worksheet (food sheet) will be a list of foods in one column and their calories (per serving) In the next column.

On the first sheet I want a drop down that lists all the foods on the "food sheet). selecting from this drop down will use a vlookup (or hlookup - depending) to arrive at a caloric value.

So here is (are) the problems. (and the solutions need to be NON-VBA)

1. How do I tie the drop down selection to a vlookup (macro maybe)

2. How do I use that one drop down to populate any cell on sheet one?

can this be done without coding?

This will be an exercise for an excel class to teach vlookups, linked sheets,
making a dropdown, using a macro, formulas, IF statements............
The class is probably not ready for VBA which is why this needs to be a non-coded solution.

I am open to other solutions (related topic) that can bring together all of the above learning concepts

thanks in advance

[davesexcel]

Are you teaching the class?

[ducecoop]

Are you teaching the class?

yes - it is a beginning excel class

[davesexcel]

Here are some samples of Vlookup
http://www.excelforum.com/attachment...orevlookup.xls
http://www.excelforum.com/attachment...st-vlookup.zip
http://www.excelforum.com/attachment...up-example.zip
http://www.excelforum.com/attachment...her-sheets.zip
http://www.excelforum.com/attachment...6-14-26-22.zip
http://www.excelforum.com/attachment...6-14-26-22.zip
http://www.excelforum.com/attachment...e-vlookup3.zip

[ducecoop]

Here are some samples of Vlookup
http://www.excelforum.com/attachment...orevlookup.xls
http://www.excelforum.com/attachment...st-vlookup.zip
http://www.excelforum.com/attachment...up-example.zip
http://www.excelforum.com/attachment...her-sheets.zip
http://www.excelforum.com/attachment...6-14-26-22.zip
http://www.excelforum.com/attachment...6-14-26-22.zip
http://www.excelforum.com/attachment...e-vlookup3.zip

Dave, Thanks for the links. Although I have not looked at them yet I "think" maybe I was not clear on my intent.

I know very well how to do vlookups
I know very well how to do macros
I know how to make a drop down.

what I do not know (with out coding) is if I can use a single drop down to populate any cell (by that me mean can I make that drop down populate cell b6 or b7 or b8 or c6 11 or c15 - without coding).

If your answers are in the links and I jumped the gun, I appoligize, but from your reply (links for vlookups) I do not think I explained my self well.

thanks

[davesexcel]

Maybe supply a sample workbook explaining your question.
If you very well know how to do all those things, you would know already that you can't magically have something happen to another cell without an excel worksheet function or without using VBA code to manipulate that other cell.

[ducecoop]

Maybe supply a sample workbook explaining your question.
If you very well know how to do all those things, you would know already that you can't magically have something happen to another cell without an excel worksheet function or without using VBA code.

Dave,

The links were in deed helpful, As far as vlookups tied to a drop down.
as for the magic - did not mean to imply I was and excel "expert" - or I would not be here asking questions.

hope we are not getting off on the wrong foot.

will post a mock up excel as soon as I see how to post it.

Thanks again for the replies and links

[ducecoop]

Here is the file

Diet.xls

I am wanting to be able to populate the "food Values" in any column for any date from the data on sheet 2.

If this is not doable without VBA do you have any other suggestions - or am I just taking this excerize too far?

Thanks

[shg]

The days in col D and dates in col E don't agree. You could format col E as ddd mm/dd/yyyy and delete col D. Or change D2 to =E2 and format as "dddd", and then copy down.

Aside from that, do you want to have drop-downs in columns M-X that select a food (single serving assumed) and sum the day's calories in col K?

[ducecoop]

The days in col D and dates in col E don't agree. You could format col E as ddd mm/dd/yyyy and delete col D. Or change D2 to =E2 and format as "dddd", and then copy down.

Aside from that, do you want to have drop-downs in columns M-X that select a food (single serving assumed) and sum the day's calories in col K?

Column K should already be working.
I was hoping for a "Single" dropdown on the sheet that would populate ANY cell.

However as Dave has pointed out that is not going to happen without VBA (or appears not to).

drop downs in columns M-X would be a second alternative - I just don't see how to do it with out one in every CELL as apposed to one in every column

Suggestions appreciated.

Thanks shg

[shg]

A dropdown returns a value to the cell in which the dropdown appears.

I can't make another suggestion because I don't know what you're trying to do.

[ducecoop]

A dropdown returns a value to the cell in which the dropdown appears.

I can't make another suggestion because I don't know what you're trying to do.

Thats what I thought.

Thanks for trying

[MMULT]

where is the attachment? I didn't find it.

[ducecoop]

where is the attachment? I didn't find it.

Diet.xls


Post number 8

[MMULT]

Try this file, if you need any changes, let me know.

[Simon Lloyd]

Thats what I thought.

Thanks for trying

You can only get the single dropdown to populate another cell if the cell you wanted populating has the INDIRECT function coupled with VlookUp or HlookUp, or you could use nested IF's but you are limited to 7 if you are using a version of excel other than 2007.

[ducecoop]

Try this file, if you need any changes, let me know.

This will work

Thanks

Now help me understand what you did

Especially the look-up in every cell - Is that a "fill" process or did you do it manually?

EDIT: It IS a "fill" process - did not know you could do that with look-ups

[ducecoop]

This will work

Thanks

Now help me understand what you did

Especially the look-up in every cell - Is that a "fill" process or did you do it manually?

EDIT: It IS a "fill" process - did not know you could do that with look-ups

EDIT2: But it is VBA coded

Can I make those look-ups work with out coding?

I am looking over the VBA and don't really understand what it does

[ducecoop]

You can only get the single dropdown to populate another cell if the cell you wanted populating has the INDIRECT function coupled with VlookUp or HlookUp, or you could use nested IF's but you are limited to 7 if you are using a version of excel other than 2007.

I am now good with multiple dropdowns. I did not know you could "fill" drop downs

[ducecoop]

OK - I am guessing I am NOT going to get the vlookup result to populate the drop down choice with out VBA.
Unless I am wrong then it is off to another option.

at this poinr I am having trouble even getting a single dropdown in one column and a vlookup in the next cloumn to generate a correct result.

can someone please look ans see what I am doing wrong. (Top 2 rows in green)

Thanks

[VBA Noob]

See if attached helps. Helper columns for lookup are Y to Ak

VBA Noob

[ducecoop]

See if attached helps. Helper columns for lookup are Y to Ak

VBA Noob

It helped in 2 ways.
I helped me fix MY vlookup

and also gave me another approach to my original idea

Thanks much

[Simon Lloyd]

ducecoop, thanks for the feedback at every post, this is a well constructed and mannered thread and will be very helpful to people searching the same problem - keep up the good work!