Another Excel help thread

[nickerjones]

Lets say I have Column A and a Column B ..

Column A is a number ( X) where each row could be a different #
Column B is lets says 1000 - column A. or ( =1000-A1 )

The problem is I have 1000's of columns I need to do this for. so it would be =1000-A2 , =1000-A3, etc ... Is there a way i can make this happen without having to enter the code without having to enter a different A column in the formula or do I just have to go through an enter it every time ?

[Jacks]

I don't have Excel on this computer to check but I think you just enter the formula on the first cell, copy it, highlight all the other cells and paste. Or maybe I'm thinking of Quattro Pro?

[Northendzone]

seems to me like you should just be ab;le to copy the formula, down or across and your cell address would move accordingly. if need be you can make yourrow of column absolute using the $ to anchor the column or row

[Shawnski]

When you have entered the first formula, exit that cell, then put your cursor over the bottom right corner. It should change to a "+" sign. If you double click on that corner (while it is a plus sign) it will auto populate every cell down that has a value in the cell to the immediate left.

[me_dennis]

you could also do this:

pick a random cell that isnt part of your main table, and make that value equal to 1000 (for example let's say Z1)

then, for your formula, instead of going =1000-A1, =1000-B1, etc you can do this
(=$Z$1-A$1)

Then just drag the formula down and then to the right.

[nickerjones]

It wouldnt work to add another column but I used Shawnski's advice and it worked perfectly... thanks!

[nickerjones]

Ok one more question...

When I am inputting this stuff when Column B = 0 , I have to put " This area is full" into column C. When Column B is anything but zero , I have to put " This area is open" into column C. I know excel is mainly a calculator function but is there any formula that would be able to automatically put " This area is open or full " depending on what is in column B ?

Thanks in advance.

[Komskies]

Enter this formula into C2:

=IF(B2=0,"This area is full","This area is open")

If your sheet is set up like so:


Drag the formula down for the columns you need.

[nickerjones]

Wow .. Thanks...Ya'll just cut my "making reports" time in half...


If I ever had to make a note in one of the cell such as ... C2 is open , Last full in 2007 . Is it possible to have "Last full in 2007" in the same cell as the formula. I have tried inputting it in and it just messes the formula up.

[oilyfan]

Originally Posted by nickerjones

Wow .. Thanks...Ya'll just cut my "making reports" time in half...


If I ever had to make a note in one of the cell such as ... C2 is open , Last full in 2007 . Is it possible to have "Last full in 2007" in the same cell as the formula. I have tried inputting it in and it just messes the formula up.

You have to use the function "&" so you would change the formula where you want the phrase to appear to this: =IF(B1=0,"This area is full","This area is open")&" " & "last full in 2007"

[nickerjones]

ahh gotcha.. Thanks

[trew]

Originally Posted by oilyfan

You have to use the function "&" so you would change the formula where you want the phrase to appear to this: =IF(B1=0,"This area is full","This area is open")&" " & "last full in 2007"

Is "&" supported in Excel? I always use the CONCATENATE function.

[Shawnski]

Originally Posted by trew

Is "&" supported in Excel? I always use the CONCATENATE function.

"&" works just fine in Excel.

[Fozzie_DeBear]

I think we may need a dedicated Excel thread....