If=Then from one column cells to next column equivalent cells

[innkeeper9]

I need to have what I think is an if then forumula so that when x is placed in a cell in column C then y will automatically be place in column D in the corresponding cell.



So, if Smith is put in C5, then 123 is put into D5. Originally I made a dropdown list for Column C with Names and then for Column D with addresses, and that does work, of course, but it would less drop downs if I can just have a list in C that will populate the corresponding cell in D with the correct address when a name is chosen from the list. So I thought it would be a kind of if then statement, but I have no idea how to create anything like that. Thanks for any help.

Ruth

[jeffreybrown]

Hi Ruth,

It sounds like you are looking for a Vlookup type scenario...

http://www.contextures.com/xlFunctions02.html

If you select Smith in C5 you want the address for Smith to automatically show up in D5?

If so, create the lookup table and then put the Vlookup in D5 and copy down as far as required.

[innkeeper9]

Thank you for your help! I sort of got it to work, but I can't find out how to have it not put a #NA in a cell that is empty. The only way I could figure out how to do it, was to remove the vlookup from any empty cell, but that means having to put it back if I end up with a name in the cell. Isn't there a way to include cells that if they are blank nothing gets put in the corresponding one?

Ruth

[jeffreybrown]

Hi Ruth,

As an example using the vlookup...

=IF(C5<>"",VLOOKUP(lookup_value,table_array,col_in dex_num,range_lookup),"")

[innkeeper9]

Thank you. I got it to work on one cell, and am playing with it now.

Ruth

[innkeeper9]

Hi,

I have got this to work, except for one issue. I did find a solution to the issue, but it requires removing 3 items from the table and making a separate list and then doing a minor change to the layout so that these items would be at the bottom of where I used the vlookup, and then putting the list in the cells. And, I was wondering if this was the way to go on this. I've detailed the issue below more clearly, I hope

In that vlookup table, which is on a separate sheet, there are 9 items: mortgage, water, trash etc. in one column, and then their corresponding payees in the other, these are fixed payees for 6 of the items. However, for 3 items, as an example, improvements, there can be no corresponding fixed payees since, for example improvements might be a new stove, or having to replace, not repair a window. If I leave those items with no fixed payee in that table, when you are on the worksheet in column B and you choose Improvements from the list, it automatically fills in a 0, whereas my friend is actually going to have to type stove, or John's Window Replacement or something like that. So would it be better to do it with a separate list as I have it now and not having the Vlookup in those corresponding cells at all? Very long explanation for a maybe short answer!

Ruth

[jeffreybrown]

Hi Ruth,

Maybe an example is best. You could honestly go in so many directions that this sounds like a style more than what works best. Again, many things would work good, but what meets your needs.

Take a look at this sample and see if it makes sense. Again, just one thought...

Select an item in column A that comes from the budget list in column I.

Column E is using the vlookup to retrieve the fixed values.

In Column F you would input the variables values along with a description in column C.

Column J is the budget with column K telling you what you have spent so far.

Column L give you an idea of what you have left.

[innkeeper9]

Thank you. I will take a look and play with your book. I think the issue [s] are that I am unaccustomed to working with something that doesn't actually have a layout in which I then work, since every tutorial I've glanced at begins with enter your data, or after entering your date. But, I actually have something that works for me, with the last [before this one] post, and since it is only for me, and it works and it does what I want it, and it looks like I want it, that's pretty perfect

I actually like working with the formulas, well asking for how to and then figuring out what the formula is doing more or less. So, thank you for the Vlookup stuff and all your help and input.

Ruth

[innkeeper9]

Hi,

Just wanted to let you know I am working with what you gave me, and thank you. I have no knowledge of formulas so it is taking a lot of time, but it is very helpful because I have to research a lot of things and so I'm learning things. I was even able to change a formula so that if a cell was blank it would not put anything in the formula cell, instead of putting something like a zero, since it had number.

Thanks so much, and also thanks for the vlookup which is working great.

Ruth

[jeffreybrown]

You are very welcome Ruth and thanks for the feedback. Best Wishes for the rest of your project.