I have a worksheet with 3 columns of data: description, price, qty. I want to output the Description data in 2 columns so as
to use fewer sheets of paper like this:
Desc Price Qty (Empty column) Desc Price Qty
the Qty column is empty...the customer fills in values for that on a paper form. Price is filled in by hand by the user.
There is also a "separator column" D just for looks.
And so, on the "Output" worksheet, I copy into A1 of Output
the value in A1 of the Product worksheet, using =Product!A1, and copy this formula 49 more times down the A column. It works:
the output sheet picks up the values from the Product worksheet.
Then, in E1 (that is to say, the 5th column over,
to repeat what I had in cols A,B &C and keep a 4th separator column) of the Output worksheet, I enter the formula
=product!A50. That would be the next value down from the Product worksheet.
In other words, take the value in A50
from the Product worksheet and put it in cell E1 of the Output sheet. Then, if I copy that 49 times, I should have 2 columns
covering 100 items.
Now back in A51 of the Output sheet, I'll copy the value from A101 of the Product sheet...etc.
Back and forth until everything is copied
Excel, however, doesn't see the formula as a formula in the "2nd" column
of the output sheet....it puts the formula in the cell as though I entered it as straight text and obviously doesn't do the
copy. I try formatting the column as "general" and sometimes it works (making the formula act like one) and sometimes it
doesn't -it just sits there as a text value in the cell.
What is very strange is that if I copy and paste the
formula on the output sheet left-to-right (which defaults to columns 4 to the right of A on the product sheet) Excel doesn't
screw it up...it grabs the value from the D column of the product sheet (which of course is empty -Excel returns a 0). When I
edit the formula to refer to the correct source column (A), then Excel simply reverts the formula in the cell as text.
This particular workbook has about 20 identical product sheets which were made by copying one worksheet
multiple times to get the 20. The 20th is the "product" sheet, which sums the values in the qty column of the preceding 19
sheets. All 20 sheets have the products as text in them (they are not derived as formulas). The 21st worksheet is the output
This has happened occasionally on just one worksheet -Excel just shows a formula as text, even when
properly entered with a leading = or + sign. But formatting the formula column as "general" has always resolved the