Copy a column worth of formulas and text

Hello,
A1 is a number: 5
A2 is a number: 6
A3 is a formula: A1+A2 (shows 11)

B1 is a number: 2
B2 is a number: 3
I want B3 to sum as A3 does.

If I copy A1:A3 and Paste Special "formulas only" into Column B then I get the desired formula in B3 but cells B1 & B2 are overwritten with the values of A1 & A2.

I have a P&L spreadsheet with over 1,000 rows. There are numbers in most cells but at the end of each category, the numbers are summed. I've gone through the rows in column 1 skipping over numbers and autosum each category. I don't want to have to do this for every column. Thanks

Hi shannydaw,

You can simply copy A3 and paste to B3 or from B3 to Z3 if needed... no paste special is needed...

Excel is "smart" enough to copy formulas and keeps it's referential integrity (fancy words which means Excel know to "move" the formula to where ever you paste it)...

If you need to "fix" certain cells, then we use $s (dollar signs)... see absolute references...

Hope this helps,
Dennis

Dennis,
That's not it. Thanks though. Let me explain with an example. A1 to A9 are values. A10 is the sum of the values. This goes on with no pattern for 1,000 rows. So I'd have to manually copy about 100 formulas scattered throughout column A to the same rows in column B. So B1 to B9 have different static values but I want those summed up too on B10

Sorry, shannydaw...

Are the formulas the same? If so, there's a way to this using Go To Special (F5)... Highlight range area, say B1:B1000, press F5, Special, Select Blanks... this should now highlight only the "blank" cells where your formulas will go... while still highlighted, type your formula, for example, =SUM(B1:B9), then instead of just ENTER, use CTRL-ENTER... this will copy the same formulas to all the blank highlighted cells...

Are the formulas all summations? If so, have you tried the Subtotal feature? Data, Outline, Subtotal?

If you have different formulas... then maybe we can "isolate" all the formulas first and hide all the static value rows... highlight column A, press F5, Special, Select Constants, this will highlight your "non-formulas", then Format, Row, Hide… you should be left with just the formulas which you can copy across…

Thanks. I may be almost there. I need the paste to skip any hidden rows. The copied formulas from B12,B23,B25, paste goes into consecutive cells J12,J13,J14.

P.S. Formulas are all different. B12 is a sum, B23 is a percentage of B12, etc

Hi shannydaw,

I think I figured it out... although, I'm not sure if Excel 2003 (which I'm assuming is what you are using) has the fill handle. It's the little black box on the bottom right corner of the cell cursor.

If you do, then use a filter on the very top row to include all the rows... if Col A has the formulas, then in Col B, filter out the "blanks" where you want the formulas to fill in... once you have it filtered, then highlight the cells with the formulas in Col A... then use the fill handle (bottom right of the range) and drag it across to the other columns...

From a lot of experimenting, this seems to work on my end... then again, I'm using Excel 2010...

Let me know if this works out for you...