Microsoft Excel help!

[Oryan77]

Ok, I know I could post this question on an Excel forum. But that would involve me registering on the website and creating a username/pword. I'm lazy today (and at work )

Is anyone here good with Excel? I have Excel 2003 and I have multiple tabs in 1 workbook. On tab A I have a list of info with dollar amounts in a column next to the list. On tab B I have the same info but I used the formula ( =tabA!G1 ) so it shows the same dollar amounts in the column on tab A. The rows are also in a different order than they are on tab A.

My problem is, I would like to copy and paste that info from tab B into more colums on tab B. But when I copy and paste, the new colums formulas change the formula to ( =tabA!D1). However many columns over I paste, that determines what column in tabA the formula is set to.

Is there a formula to use so no matter where I paste the cells it will always keep referring to the same column/cells in tab A?

---------------------------

I also have another question. I would like to be able to type a number in a cell and have the cell automatically add the letter x infront of the number after I'm done typing. Is this possible?

Thanks for any help

 

[Marius Delphus]

Try using dollar signs in the cell addresses. This is the "absolute" marker as far as Excel is concerned.

Thus, if you want a formula to always refer to cell G1, use $G$1. If you want to let the formula vary as to row, use $G1; as to column, use G$1.

 

[steev42]

My problem is, I would like to copy and paste that info from tab B into more colums on tab B. But when I copy and paste, the new colums formulas change the formula to ( =tabA!D1). However many columns over I paste, that determines what column in tabA the formula is set to.

There are two ways to do this, depending on what you're trying to do.

If you're trying to keep the formula the same, put a "$" in front of the column like so=> =tabA!$D1
This will "lock" the D so that it always remains the same. You can do the same for rows by putting the $ in front of the number. I'm guessing this is what you want.

If you're trying to keep the values the same, regardless of whether the originals change or not, copy, paste special (under the edit menu), values.

I also have another question. I would like to be able to type a number in a cell and have the cell automatically add the letter x infront of the number after I'm done typing. Is this possible?

Format->Cells. Under Custom, change the text box (It should say "General" when you go to it) to <<"x"0>> without the brackets. The quotes must stay. Mind, if you put in a negative number, it will come as "-x1". If you want it to look like "x-1" you'll need to add a <<;-"x"0>> to the string.

 

[Oryan77]

You guys are great. I knew I'd get help here, thanks!

 

[Oryan77]

I have another question

Maybe you guys know of a better way for me to accomplish another task.

I have rows of numbers that I frequently copy and paste into another workbook. Whenever a row has a 0 in it, I do not want that row to copy & paste over into the other workbook.

I have been using the "hide" function but the problem with that is that it's not easy for me to visually see what rows I'm not copying over. It's also hard to tell what rows are hidden.

Ideally I would like to be able to see the rows that have a 0 in them, but just not be able to copy and paste them when I select every row. I would be changing the numbers in the row frequently back and forth to 0 and copying/pasting quite often. Is there an easy way to set a row so it can't be selected?

If that's not possible, isn't there a "reveal" type of button you can insert to the left of the row so I can click it to reveal or hide the row and easily notice that a row has been hidden?

 

[Frostmarrow]

Do you want cells with 0 in them to look blank? If so you can use conditional formatting and state that the text color should be white on white if the cell equals 0.

 

[Oryan77]

Do you want cells with 0 in them to look blank? If so you can use conditional formatting and state that the text color should be white on white if the cell equals 0.

Well my main concern is that I'd like to be able to select every row from top to bottom, copy them, and paste them into another workbook or a text file without it copying and pasting certain rows. I can accomplish that if I hide the rows I don't want copied, but then I can't actually see those rows.

It would be nice if I could still see those rows but it's not a must. The problem is that when I hide a row, it's hard to tell what rows are hidden. I would be changing those rows from hidden to unhidden quite often as I need to copy and paste them, so however I do this would need to be done in a way that I can easily change the status of a row so it could be selected for copying or not.

 

[Oryan77]

I've seen on these forums how there is a sort of "spoiler" type of button that when clicked, it drops down the hidden text. Isn't there something like this for Excel? It would be nice if the hidden rows instead had a small button to the left that actually let me reveal the row.

The way it is now, a hidden row just has a thick line on the left in the actual row number section. That thicker line isn't so obvious to me to see that there's a hidden row.

 

[steev42]

Yeah, there isn't really an easy way to do that. You could write a macro to hide the rows you don't want to copy and hit the control V, but you'd have to be somewhat familiar with Visual Basic programming to accomplish it very well.

The closest thing to a 'spoiler' button is when using SubTotals. Depending on how you set them up, there'll be +/- buttons on the left side of the screen that'll show or hide the rows that are making up the subtotal.

Yes, there is a way to make it so a cell cannot be selected. When you have that cell selected, format the cell, and go to protection. Ensure "Locked" is checked. Then, protect the worksheet (Tools->Protection->Protect Sheet), and uncheck the "Select Locked Cells" button. There's no quick and easy way to do this, though.

 

[Oryan77]

Yes, there is a way to make it so a cell cannot be selected. When you have that cell selected, format the cell, and go to protection. Ensure "Locked" is checked. Then, protect the worksheet (Tools->Protection->Protect Sheet), and uncheck the "Select Locked Cells" button. There's no quick and easy way to do this, though.

I tried this way before. But say I do that and lock a few rows. If i want to unlock them later in the day, don't I have to unprotect the whole sheet, and then reselect all the rows to lock all over again and protect the sheet again?