Can Excel auto delete data from one Wsht when the same info is entered on another Wsht in the same workbook?

Question

Can Excel automatically delete data from one worksheet when the same data is entered on another worksheet in the same workbook? I have an "In Inventory" on one sheet and an "On Order" on another sheet. Can Excel automatically delete the entry from the "On Order" sheet when the same information is entered into the "In Inventory" sheet? And if so, how or can you point me to instructions?

Can Excel automatically delete data from one worksheet when the same data is entered on another worksheet in the same workbook? I have an "In Inventory" on one sheet and an "On Order" on another sheet. Can Excel automatically delete the entry from the "On Order" sheet when the same information is entered into the "In Inventory" sheet? Once the information is entered as "In Inventory" it is no longer needed on the "On Order" sheet. Therefore, I would ideally like Excel to delete the duplicate information on the "On Order" sheet once it is entered on the "In Inventory" sheet. And if so, how or can you point me to instructions?

Answer 1

Excel won't automatically do it, but you can make formatting and formulas do it for you!

There is a simple thing you can do with formatting to make it look like the number you have On Order is deleted, instead of showing zero.

On the On Order sheet instead of just entering the number, enter the number minus the number In Inventory. Actually the cell where you enter the number In Inventory. That way you only have to enter the In Inventory number one time.

Here's how: I will assume you have a form with a list of items for In Inventory and there is a duplicate list of the same items in the same cells, but it is a list of On Order items.

If an item is On Order, the In Inventory will be 0 for this example. In the cell you have recording the On Order, before you put in the number ordered, press +. So for 6 on order, you enter +6, then before you press enter, Press - (minus), then click the tab of the In Inventory items and click on the cell that has the 0, then press enter. This sets a formula that shows how many you ordered, minus the 0 you have In Inventory. The zero that is in the cell you put in the formula. My formula looked like this: =6-Sheet2!B4 because I entered 6 for On Order.

When the item comes in and you enter the 6 you received, the formula you entered in On Order goes to 0 because 6 - 6 = 0.

Now in formatting, choose the On order cell again and do Format, Format cells (or Ctl-1.) Set the format for a number format that you like, with commas and decimals places, etc. Then under category, click Custom. Here you can tell the formatting what to do for positive numbers, negative numbers, zero and added text. These formats are separated by the semi-colon.

Here is what you do to make it blank when zero is the number, thus it looks like it was deleted. Mind you, this leaves the row in the worksheet. My format looked like this in custom: #,##0. I change it to #,##0;-#,##0; -- This keeps the minus sign for negative numbers but the last semicolon, with nothing after it means zero should show as blank.


You can make the title or any other items in the row disappear if zero is the number using an if statement. Here's my example you can use to customize and test on your sheet.

=IF(B4=0, "","Shirts"), where B4 contained the formula that evaluates to 0. Shirts is the text item.

There are a lot of flaws with this because your received amount may not be the same as your On Order, so it will still be there until it goes to zero. Existing Inventory Levels at the time you Order will keep it from being zero. If you enter a number in the On Order cell the formula will be lost and you will have to go back and re-create the formula, but you see it isn't too hard to do.
You have to remember to set the proper format with the blank zero thing in the Custom formats.

I hope this helps you.

Answer 2

Excel cannot pro-actively delete data in cells. A macro is required to act on the cell contents and not just update the value in a cell. However, there is a work-around. You could:

(1) Use Conditional Formatting to "white out" the cell based on a formula which compares the value in the cell to values in the other sheet using the VLOOKUP function. However, the cell would still have a value.
(2) Create a dummy column next to where you enter the data that will show the data when the data does not appear in another place. =IF(ISERROR(VLOOKUP(enter location), cell keeps value, else cell is blank)
(3) Write a Macro that runs through the list and assigns null values to cells that appear twice.

Range("starting point").Select
While (ActiveCell.Column < xxx)
While (ActiveCell.Row < xxx)
If (ActiveCell.Value(what you want)) Then
ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Wend
ActiveCell.Offset(-top + 1, 1).Select
Wend

Answer 3

It shouldn't auto delete it. If you have the same information on all the worksheets, same set up and everything. You can change the information on all sheets at once by selecting each worksheet. Press and hold shift and select the three tabs at the bottom where you name your worksheet. Then just enter your data or delete and it will automatically change it for you on all three worksheets. Hope this helps.

Answer 4

You can write a bunch of different formulas that can alter the appearance of your data, but it will not delete it unless you write code. Unless you really know what your doing with code, I'd suggest to avoid it. Would manipulating the appearance of the data work? If not, I'd suggest using Access, in there, it is very simple.

Answer 5

It can be done with an 'IF" formula. But without looking at it hard to tell you how to doit.