XL2003: Pulldowns usu. to populate 1 cell, can we have entries for 3 cells?

[StargateFan]

I was wondering about something. This would revolutionize things
considerably; I have a spreadsheet that I created 5 years ago that I
use repeatedly. The difficulty is that if I could expand its
efficiency, then it would be much more useful and would cut down on my
work tremendously.

When I enter items into the hidden worksheet that provides the
pulldown info, it is usu. data that populates 1 cell in the column of
the target sheet that is pulling the data. 1:1, in other words. Is
there a way to have several pieces of information that populate
several different cells in the target sheet?

For example, the data that is needed is this type of thing here below,
made-up info only (except for the 1st line in brackets; that
information is contained in the header row of the target sheet):

(code page item # description price unit)

(row A empty)
32658 15 C Binders, 1" $7 ea.
48BC 405 G Post-It notes $6.50 pkg.

This data doesn't change but once a year when a new catalogue comes
out.

Once the info is entered, after that, it would be a question of just
entering the quantity of the item(s) being requested for purchase.
That's all that I would have to do. This would cut down my work by
about 98-99%! This hidden sheet would only need a major overhaul once
a year when the new catalogues come out which would just mean changing
the data once to reflect the new prices and new page and item #, etc.,
and renaming the sheet to reflect the new year.

Yes, I could code the above all in the one cell, but then the target
sheet could not be effectively sorted by different categories; it
would lose a lot of its value without that. That's why it's important
to have the source data in different cells.

Can this be done, though?

 

[StargateFan]

Sorry, subject line should have read, "for more than 1 cell". Thanks.

 

[Gord Dibben]

I would suggest VLOOKUP formulas in the target rows of the hidden sheet.

For more on VLOOKUP se help or check out Debra Dalgleish's site.

http://www.contextures.on.ca/xlFunctions02.html

Note the sample file download.


Gord Dibben MS Excel MVP

 

[StargateFan]

I would suggest VLOOKUP formulas in the target rows of the hidden sheet.

For more on VLOOKUP se help or check out Debra Dalgleish's site.

http://www.contextures.on.ca/xlFunctions02.html

Note the sample file download.

Thanks for this! And extra thanks for pointing out the file download.

I had a real tough time with this. I googled the ng looking for this
post today but didn't remember subject name so did generic search.
Came up with very easy-to-use flash video reference that went through
the process step-by-step! Perfect.

I ran into one problem, though. Well, had a #N/A problem that seems
to crop up, in this case when 1st cell is blank, but I cheated with
conditional formatting till I can find a programmatic solution so that
the #N/A is coloured out.

What I couldn't fix is the small degree of inaccuracy that can occur.
The flash video alluded to the possibility and the narrator was
careful to show us how to change source and target ID fields to text,
something I needed anyway since the ID value is mixed letters and
numbers. But I found that every so often, the data from a different
row would be pulled.

I've had some troubles like this before in other areas and in those
cases found that playing with the cell formatting would correct it.
Same thing here. I kept switching between general, number and text
till the correct data popped up. I was very careful with the setup
and referred back to the video, but don't see anything wrong in what I
have. So needed to ask the experts here.

The formula for this type of thing turned out to be very simple once I
got the step-by-step guidance. Here's the first one in the sheet to
do the vlookup magic <g>:
=VLOOKUP($D2,GrandToy2008,2)

D2 is the reference cell, of course, the named range in another sheet
is called "GrandToy2008" and the column it's looking at there is
column 2.

But sometimes a code like, say, 22150, which should have been a set of
folders or something, brought up a lamp today. That lamp kept
appearing, for some odd reason. But it did happen to 2 other items,
too.

Anyone know how to fix this type of thing?

Thanks! D

 

[StargateFan]

Anyone?

I'd really like to fix the problem. When you type in a product ID #
and instead of the expected value, info from another product ID is
dumped in. By fiddling with the cell format, eventually the right
info popped in but I'd like to avoid the problem. 4 entries came in
wrong out of about 15. Too high an incidence of error. Would like to
know how to finetune sheet so that no errors occur like this.

Thanks!

 

[Gord Dibben]

1. Add the FALSE argument to your formula to ensure that Excel does return the
closest match.

=VLOOKUP($D2,GrandToy2008,2,FALSE) will return #N/A if no match found.

To not display the #N/A add a trap to your formula

=IF(ISNA(VLOOKUP($D2,GrandToy2008,2,FALSE)),"",VLOOKUP($D2,GrandToy2008,2,FALSE))

2. Your data must match exactly.........no extraneous spaces if text strings.
Use TRIM function to clear spaces from values

If numbers make sure the decimal point formatting doesn't mask an extra DP or
two.

i.e. 2.0000123 formatted to 2 DP will show 2.00 but Excel knows it is
2.0000123


Gord

 

[StargateFanNotAtHome]

1. Add the FALSE argument to your formula to ensure that Excel does return the
closest match.

=VLOOKUP($D2,GrandToy2008,2,FALSE) will return #N/A if no match found.

To not display the #N/A add a trap to your formula

=IF(ISNA(VLOOKUP($D2,GrandToy2008,2,FALSE)),"",VLOOKUP($D2,GrandToy2008,2,FALSE))

Gord, thanks! This works perfectly! Brilliant.

2. Your data must match exactly.........no extraneous spaces if text strings.
Use TRIM function to clear spaces from values

If numbers make sure the decimal point formatting doesn't mask an extra DP or
two.

i.e. 2.0000123 formatted to 2 DP will show 2.00 but Excel knows it is
2.0000123

I actually used data validation on the product ID so that I'd get a
pulldown. It seems to be working just fine. Don't know if that's
"illegal" but this way, all I have to do is keep updating the product
list that this is all based on and inputting will be breeze.

In fact, when I get this fine-tuned, I will have to share this one.
It seems like it will be a possible good choice for anyone needing to
prepare a supplies order form before inputting to their online
ordering system. The request form we have from employees to us is
cumbersome and comes to us in the form of emails. By inputting into
the Excel spreadsheet in this, the easiest way of doing this, I end up
with a standardized form with complete data that is needed for
reconciliation afterwards and that will hold up under audit. Attached
to the original email requests, this along with packing slips will be
darn near perfect. It will now take hardly any time to prepare and
then I can go online to order. I then just make note of the total
amount expended and the P.O. and Order numbers assigned to it then
know that I can then use the very same printed output as a reference
to check against when product comes in that will also allow me to
efficiently deal with back orders and that will hold up under audits.

This one is a miracle. Thanks for helping me fine-tune it. D