VLOOKUP Macro?

[blucajun]

I'm not sure what to use to get this result but here's what I'm trying to do:

My column L is a Total column that contains this formula (starting in L6):

=IF(C6="Mileage", 'Mileage Personal Auto'!$G$56,IF(C6="Bus. Meals & Ent.",
'Business Meals & Entertainment'!$G$56, ""))

Each cell in column C contains a dropdown field that could be any number of
choices.

Basically, my forumla says to look at cell C6 and if it says either
"Mileage" or "Business Meals & Entertainment", then go look at either the
"Mileage" or "Business Meals & Entertainment" worksheets (depending on which
it found) and copy the value from that worksheet's cell G56. Unfortunately,
for those line items that don't show Mileage or Business Meals &
Entertainment in column C, the user will have to manually type in a total in
column H. I'm concerned about someone accidently overwriting my formula but
because they need to be able to manually type in the other item totals, I
can't protect it.


I'm at the point of trying to figure out how to get this end result without
having to include a formula in column H:

If Cell L6 = any value, then copy that value to cell H6. If L6 does not
contain any value, then leave cell H6 empty.

Then I can hide column L, and I don't have to protect column H. I'm not
much familiar with VLOOKUP and Macros but I've been reading through this site
for something similar and I think this may be the way to go, even though I
don't know how to do it. Thanks in advance for your help!

 

[Pete_UK]

A formula can't "push" a value to another cell, it can only "pull" it
into the cell where the formula is. Thus to get a value returned to
H6, the formula would have to reside in H6, and if that is where users
can input alternative values then your formula will get overwritten.
Why don't you change your formula in L6 to this:

=IF(C6="Mileage", 'Mileage Personal Auto'!$G$56,IF(C6="Bus. Meals &
Ent.",'Business Meals & Entertainment'!$G$56, H6))

This way the users can input values into H6 and you will not need to
have a formula in that cell.

You can protect the sheet to prevent your formulae being changed -
just Unlock the cells that you want the users to have access to (using
Format | Cells | Protection tab) and then use Tools | Protection |
Protect worksheet (with or without a password). Now the formulae in
column L will not be accessible to your users, but they will be able
to type a value into column H if that was set to Unlock. I often set
up a bright yellow background to indicate to users that they can enter
data into those cells.

Hope this helps.

Pete

 

[blucajun]

Thanks for the direction and explanation. I ended up swapping the forumlas
-I put my original forumla from column H into column L and then I copied your
forumla into column H. So it looks to see what was selected in columns C
(mileage or other) and puts the value into column L. Column H looks to
column L and if there is a forumula, it pulls it in. Works great now!
Thanks so much!

 

[Pete_UK]

You're welcome - glad you found a way to do it.

Pete