Home » Forums » Open Source Learning Center » Question and Answer

question

Submitted by cw1 on 30 July, 2013 - 18:12

help needed please -
cells A1:A50 need certain tasks entered and I want cells B1:B50 to autofill with specific codes when entering the recurring tasks
eg
A1 work B1 ab123c
A2 rest B2 de456f
A3 play B3 gh789i
A4 rest B4 de456f
etc
- the less typing the better.

‹ VBA for a loop calculation How to return a range object that is just the first colmn of a named range covering multiple columns? ›
»

Hi, Easiest way is a

Submitted by mec1981 on 8 August, 2013 - 17:07.

Hi,

Easiest way is a vlookup.

Type all your scenarios into other cells such as C1 = Work; C2 = Rest; C3 = Play; D1 = ab123c; D2 = de456f; D3 = gh789i.

In Cell B1 use vlookup formula =vlookup(A1,$C:$D,2,0)

You can also assign drop downs to column A by setting this column to be a list by highlighting column A, select Data then Data Validation and choose type as list and in source type $C:$C. this will prevent any errors through typo's

Regards
Mark

»

Thanks for your reply Mark.

Submitted by cw1 on 19 August, 2013 - 14:22.

Thanks for your reply Mark. It didn't work but pointed me in the right direction. What did work is =vlookup(A1,C:D,2,(FALSE)). I get the same result with or without the $ signs in the formula. Works ok except when A is blank B shows #N/A. Any idea how to keep B blank when A is blank?

cheers
cw

»

Hi

Submitted by Jon on 19 August, 2013 - 16:01.

There are various ways you can avoid getting the #N/A error, and some depend on your version of Excel. Here is a formula that should work in all versions

=IF(ISERROR(VLOOKUP(A1,C:D,2,(FALSE))),"",VLOOKUP(A1,C:D,2,(FALSE)))

Hope this helps

Jon

»

brilliant Jon, thanks a lot,

Submitted by cw1 on 21 August, 2013 - 08:27.

brilliant Jon, thanks a lot, and the same to Mark - superb.

»