Ask Your Question
1

How to make 3 different cells in the same row enter pre-scripted text automatically [closed]

asked 2014-10-04 14:21:26 +0200

jokeronabike gravatar image

Hello. I cannot find a solution to this problem. Part of the difficulty I am having is even knowing how to word a proper search string. What I am shooting for is to select a billing code from my dropdown menu and then automatically see the other repetitive values that always correspond to that dropdown item appear in their respective cells on the same row.

The only luck i have had has been relative to numbers--I need these text descriptors to appear.

Thanks for any help...

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 19:17:51.722463

Comments

Please attach a sample file with what you want do. (ask voted to allow upload the file).

m.a.riosv gravatar imagem.a.riosv ( 2014-10-04 15:07:14 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2014-10-06 10:48:41 +0200

blindape gravatar image

Hi.

When working with lookup functions I find it easier to think of the last argument as match type. When you set the argument to 0, VLOOKUP requires an exact match.

If there is any difference between the value you are looking up, and it's corresponding value in the lookup table, a match will not be found.

Things to look out for when VLOOKUP returns an unexpected #N/A error:

  1. Are the ranges in the lookup table correct? This one can still catch me out on a hectic day if I have forgotten to add $ or a range name to the lookup table reference.

  2. Are you comparing apples with apples? Lookup functions don't recognize the number 1234 as matching the text "1234". When you need to match numeric codes make sure all codes (in both the lookup table and the report) have been converted to numbers or all codes have been converted to text. It doesn't matter which as long as the data types match.

  3. Watch out for orphaned spaces at the end of a lookup value. These can cause havoc with lookup functions as they prevent matches from being found and are invisible to the naked eye. If you suspect this may be the issue, the TRIM function can be used to remove excess spaces from text.

Hope this helps solve the #N/A woes.

Cheers,

John

edit flag offensive delete link more

Comments

Thanks to all who helped me out with this. I finally have a spreadsheet with some fangs...! I can't believe I waited this long to tweak this document that I've been using for the last 2 1/2 years. I would like to upload the final version for posterity in case any other noobs out there stumble on it but I am new to this site and I don't see the little paper clip icon for uploading files.

Here is a link to find it on dropbox.com :

https://www.dropbox.com/s/vj6nqy9xnd6...

jokeronabike gravatar imagejokeronabike ( 2014-10-06 20:45:27 +0200 )edit
0

answered 2014-10-04 15:22:17 +0200

Rugslug gravatar image

updated 2014-10-05 13:03:59 +0200

One way is using VLOOKUP. It looks through a vertical lookup list to select the corresponding cell.

=VLOOKUP(A1,$F$1:$I$3,2,0)

A1 is the value to lookup (billing code)

$F$1:$I$3 is your lookup list. It can be on a separate sheet (I recommend a separate sheet for better management of your list). Note the $ (Dollar Sign) to make your lookup list not become relative as you copy your formula down and right.

2 is the column to pick your lookup from (in this example, 1 corresponds to col F, 2 to Col G, 3 to Col H, 4 to Col I)

0 is if your lookup list is sorted. If omitted, default is True (sorted ascending). If you haven't sorted it, specify the zero. The lookup will find the first corresponding value in your lookup list. There are some finer details as to the way the lookup works. With a 0, you will get a N/A error if your item is not found in the lookup list. With a 1, the value returned will be the first value lower than your your value to be looked up. I would recommend a 0.

Edit/Addition: If the lookup table is on a separate sheet.

=VLOOKUP($A1,$Sheet2.$A$1:$D$500,2,0)

Sheet2 would of course be the name of the other sheet. I added a $ in front of the A1 ($A1) so that if the formula is copied across rows, the value to lookup will stay to Column A.

edit flag offensive delete link more

Comments

Thank you so much for the response. This is way deeper into spreadsheet voodoo than I am accustomed to going so I have a question right off the bat--if I use a separate sheet, do I name it "$F$1:$I$3"...? I will tinker with this this morning and see what I can figure out. Thanks again!

jokeronabike gravatar imagejokeronabike ( 2014-10-05 12:51:06 +0200 )edit

I am getting closer on this but still working through the syntax and how to deploy it exactly as I need. Here is a link to a screenshot of my spreadsheet. When I input the billing code (RED) I want the other 3 elements (YELLOW) to appear automatically.

EDIT: finally got it--95%. for some reason (cell Number Formatting issue?), i keep getting #N/A for the Amount column even tho I reference "11" in the formula.

https://www.dropbox.com/sc/le9hqlskqt...

jokeronabike gravatar imagejokeronabike ( 2014-10-05 15:38:14 +0200 )edit

Couldn't get your screenshot (err 404: object not found). BTW: A functional file is MUCH better than a screenshot.

You may also study an example I made for similar cases. (See answer by "Lupp")

Lupp gravatar imageLupp ( 2014-10-05 16:39:29 +0200 )edit
0

answered 2014-10-05 16:41:26 +0200

Lupp gravatar image

As advertised in my comment: Example attached. AssociateWithChoiceFromRange001.ods

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-10-04 14:21:26 +0200

Seen: 248 times

Last updated: Oct 06 '14