Trying to match two colums if critera is meet, but leave formula out if not.

[tpollock]

I am trying to make a sales order forum that i can import into my software without manually typing everything in. I have created every from the database and so on. I can not figure out though how to make this work. When the x is entered into the box for bill to is same as shipping to care over the value. If the x isnt there i need the lines on the shipping side to be empty without a formula. I did try the IF formula and had it working all for leaving lines blank if x wasnt selected.

[K m]

Why you would have uploaded a sample that was password protected?
==================
In any case in cell K11 put this formula and copy down
=IF($G$10="X",C11,"")

[tpollock]

sales order.xlsxThis is how I had it set up before. Problem is when the X isnt selected the forumla still exist in the ship to colums. When users goes to type ship to in it will earse the forumla. trying to be safe and not count on customers to save a copy and then save order as a new customer. I will upload a new one that isnt password protected.

[K m]

You could set up a separate areas for input and another for output. That way the formulas are not overwritten
==================
A second way: You could set up a macro to both preserve the formula and allow editing

- The macro would copy the address formula to another part of the worksheet or on a different worksheet
- Set up references to that new area.
- and restore the formulas

[tpollock]

Since i have never worked with macro or even know where to start with it. How would i set up the input or output in another cell??

[K m]

As far as the macro goes, one easy method is to turn on the macro recorder and do the steps that you want Excel to remember. At the end, turn off the macro recorder. What happened inbetween turning on and off the macro recorder is your "macro".

In your example, you would copy the data, that was entered to another part of the worksheet. Then go back to the "send to" part and enter a IF statement, ie =IF($G$10="X",J11,""). This assumes that you copied what was input to J11. Change cell references as appropriate
=======================
The second method

You would have a separate worksheet where the person enters the data. A similar looking worksheet would be used to print the data. This is clean as the "Input worksheet" contains no formulas to be overwritten. The "Output worksheet" is used for printing.

BTW: A separate worksheet is not required
=====================
Third Method - Let the user overwrite the formulas

When the file is opened, a macro runs automatically restoring the formulas that may or may not have been overwritten last time the file was saved

[tpollock]

Thanks i will give this a try and hopfully i will figure macro out. thanks for all the help got me out of a big bind.

[tpollock]

sales order.xlsx

Okay I pulled the information out on the bottom of the page now how do i send it to the ship to without putting a formula in the ship to boxes?? Also yesterday when i was messing with this i figured a way when using IF formula that if nothing was present it wouldnt show the 0. pretty sure it was isblank but need a refreasher. Thanks.

[K m]

Look at the attached workbook:

I took your original file and added an autoexecute macro that restores the formula that may previously overwritten by the user

Notes:

- This uses an autoexecute macro

- To work, the user must allow macros. If they say no to macros, this won't work

- The file uses an area to the right of the print area to "store" the formulas.
The area is formated in white font so it will not be seen

- When the file is opened, the "Ship To" area is reset so what the user entered before is erased.
However, if the file is opened without macros enabled, the user data is not overwritten

- You need to reset your password protection as this file is not password protected

[tpollock]

Thanks again and that is awesome. Where is a good place to learn how to use Marco on my own time. Planning on going back to school to get microsoft certified but would like to try learning this on my own.

[K m]

If this problem is solved change the title to [SOLVED]

Click on the star below to give feedback

[K m]

I just thought of a very easy solution
==============================
Make the workbook with the appropriate formulas and save it as Read Only with Password. Name the file, something like "Template"

The users will open the "Template" file as Read Only and save under a different name. The underlying formulas are never altered as the file is saved under a different name. - No macros needed.

[tpollock]

I like how you set it up with the Marco. Problem is i just noticed that when you carry over the zip code it goes into the state and nothing goes into the zip code cell. Can you help me fix this since i dont know how to redue the marco.

[K m]

The problem is the "Bill to" and "Ship to" do not share the same structure. The City in the "Bill to" is in two Columns (C,D) and the "Ship to" is in three columns (K, L,M). This throws off the output. It is not obvious as you have turned off the column and row headers. This could easily be fixed but the approach below is recommended

MY RECOMMENDATION
I recommend that you set up the file as you wish with your equations.
- Save the file as a read-only file with a password that only you know.
- The user is forced to saves the file as another name and does not overwrite your equations.
- No macros required and it is much cleaner.
- The user would pull up the "template" file and save as whatever name they want.

[tpollock]

Thanks i will adjust it and once again thanks a lot.