Excel N00b

July 4, 2007 5:14 AM

Excel: How can I get text to stick in the cells after it's been copied from another spreadsheet?

I built a purchase order workbook that copies the data over from the PO Data Entry Spreadsheet to a Master List of all the PO numbers. It works. I used formulae like =IF(B119=OrderNo, Date). But here is the problem: when I am done with PO #119 (for example) and I stary PO# 120, the 119 row of data on my Master List sheet goes back to FALSE because the formula is still there, linked to the PO Data Entry sheet. How can I make the data from 119 stick when I start 120 on the other sheet? Should I be using Nested IF Functions? If I do, how can I copy and paste the actual text in? Like:

=IF(B119=OrderNo, Date, IF(C118>0, "don't delete the text from that row", "damn it"))

posted by chuckdarwin to Computers & Internet (6 answers total)

Others may disagree, but I would say that you cannot base a formula in an excel sheet on data that is no longer present. Fundamentally, formulas are calculated from values that exist in other cells. Delete or modify the value, and you change the results of the formula calculation.

What you need is a macro or a small VBA function that will copy the actual value of the order number into the next available cell in the Master List.
posted by yoz420 at 5:21 AM on July 4, 2007

Best answer: I'm not sure I fully understand your situation, but this may help:

Once you have a value in the cell, you can copy it and Edit>Paste Special...>Values and it will replace the formula with the results. You can select a cell, copy it, and use paste special to put the value in the same cell.

You could use a macro to select all non-false cells in the sheet and replace them with their values. If you put a button on the data entry sheet with the caption "New PO" you could have it call the paste special macro as well as clear out the values in the data entry sheet. This way it would be part of the PO creation process.
posted by i love cheese at 5:29 AM on July 4, 2007

Response by poster: Thanks! You guys rocketh the house, etc, etc.

How do I select all non-false cells?
posted by chuckdarwin at 5:34 AM on July 4, 2007

Click in the top of the column. Go to Data > Autofilter. Then click the wee arrow that appears, and choose the value you want to select. This'll only work if you have no blank cells in the column.
posted by Happy Dave at 7:23 AM on July 4, 2007

Best answer: BEFORE FOLLOWING THE INSTRUCTIONS BELOW, MAKE A BACKUP COPY OF YOUR PO SPREADSHEET!!!

I'm not sure I fully understand what your spreadsheet looks like, but I'm going to try to describe a way to select and replace all non-false cells. You would be stupid to run this (or any) code that you find on the web on an important spreadsheet without first making sure you won't lose valuable data!

Okay, lets say that you have your if-then statements in Column A of a sheet called "PO Register", so that starting from A1 going down, the formulas are:

=if(B1=Ordernum,date)
=if(B2=Ordernum,date)
...

The value of A1 will be Date if B1 is ordernum, but if B1 stops being ordernum, then A1 will be "FALSE." What you'd like to do is run down the column and replace all formulas that have results <> "FALSE" with the results of the formula. Right?

If you go into the VBA editor (Tools>Macro>Visual Basic Editor), double-click on "This Workbook" on the left, and in the newly opened window paste:

Sub ReplaceFormulas()
Sheets("PO Register").select
range("A1").select

do
  if activecell.value <> "False" then
     activecell.copy
     Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  end if

   activecell.offset(1,0).select
loop until isempty(activecell.offset(1,0))
end sub

to run the subroutine, you can click F5 when you have your cursor in it. In Excel you can go to Tools>Macro>Macros... and choose "ThisWorkbook.ReplaceFalse"

I haven't tested this too much, but if you have data starting in row 1, it will continue down column A until it hits a blank cell looking to see if the value <> FALSE. If the value isn't FALSE, it will copy the cell, then do a paste special-values before moving to the next cell.
posted by i love cheese at 7:41 AM on July 4, 2007

Response by poster: Wow, i love cheese! I've been playing with macros and the VBA editor all day, but your solution looks better than anything I've come up with.

I owe you a beer.
posted by chuckdarwin at 8:38 AM on July 4, 2007