Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Macro?
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Macro?
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 On Jun 27, 10:43*pm, blucajun wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Macro?
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" wrote: 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 On Jun 27, 10:43 pm, blucajun wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Macro?
You're welcome - glad you found a way to do it.
Pete On Jun 30, 9:48*pm, blucajun wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Macro? | Excel Worksheet Functions | |||
Macro - Vlookup | Excel Discussion (Misc queries) | |||
Vlookup Macro | Excel Discussion (Misc queries) | |||
Vlookup Macro? | Excel Discussion (Misc queries) | |||
vlookup macro | New Users to Excel |