Excel Formulas for Auto Populate

Once you make the formulas, you can then "Hide" those columns.

Kilowatt,

Thanks for answering.

When I plug in your formulas I get the correct badge # for a result, just like I did when I used =MID($C281,2,5). The problem is that the vlookup function (=VLOOKUP(D282,Rep,2,FALSE) fails and reports a N/A.

I am assuming the the MID command does some kind of formating of the result that make is unusable with vlookup. If I use the formula =LEFT($C281,FIND("-",$C281)-1) I get a usable result. Go figure

rlindey

N/A could be because your system is looking for a number but when you use the LEFT or MID functions, it returns a text value. Try multiplying the LEFT or MID output by 1 and then see if the VLOOKUP resolves itself.

Bob,

betomachine has it right. You are probably saving your Badge Numbers as values and the MID or LEFT String functions leave a string (even though it looks like a number).

You could use the VALUE function with the MID function to get a value result.

=VALUE(MID($C281,2,5))

VALUE turns a text string into a value (as best as it can). In your case the string number would become a numeric value and would match the column of values in the Badge column of your table.

Calvin

Sorry Guys, neither of those suggestions worked. I still get the N/A

Bummer, I thought that would fix it. I'll hunt around and see if something makes more sense.

OK, So while I played with formating before and did not get the reuslts to work i just tried formating the =(MID($C281,2,5)) cells as numbers and the results came in just as I wanted them. I had played with formating the cells in the lookup table array thinking that was the cause, not the actual cells.

Any way thanks for your replies.

Bob

This is the result that I got (Upper left corner is A1). One: All of the above cells are formatted as "General". Two: Note the "$" usage in the vlookup formula; since it is a table range, the start and end points must be fixed. Otherwise when you copy and paste the vlookup formula into other cells, it automatically readjusts them accordingly.

I have built a spreadsheet very close to this, programmed with VBA... uses an activeX flexgrid.. but works well for filling in the blanks.. extraneous data is on a second sheet.

send email if interested... or if there is enough interest, I can post the instructions and code, explaining how to tailor it to your needs.

please make sure your application descriptions are thorough, as I have many examples I've built over the last few years.. and can provide more value if I know more thoroughly what your requirements are.

Chris