If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
formatting columns
Thanks everyone, but let me explain my situation.
I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. |
#2
|
|||
|
|||
formatting columns
I'm not really sure I understand the problem. If the purpose is to avoid
dragging down, you can highlight the range you want, enter a formula in the active cell, and then hit Ctrl Enter to fill the entire range with the formula. -- Vasant "sue" wrote in message ... Thanks everyone, but let me explain my situation. I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. |
#3
|
|||
|
|||
formatting columns
It would be better for you Sue, and the rest of the folks who are willing to
assist you if you would stay with your original post. In that way, all would know what was already suggested, and what perhaps didn't work for you, so that they wouldn't duplicate suggestions that you might have already rejected for one reason or another. Could you tell me what didn't work for you when you tried the Choose formula that I had suggested three days ago? Even though the formula I posted only showed four choices, I did mention that there was the ability to utilize the function to return 29 choices. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "sue" wrote in message ... Thanks everyone, but let me explain my situation. I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. |
#4
|
|||
|
|||
formatting columns
Sorry. I'm not familiar with posting on these boards.
Both formulas worked to a point. If I did the drag down method the cells filled in perfectly. I'm just trying to avoid that as I will be doing a lot of data entry during a clean up process, and preparing to use Access. That is why I wanted the column to be formatted to autofill depending on the number I put in the first column. Thanks again for all the help. -----Original Message----- It would be better for you Sue, and the rest of the folks who are willing to assist you if you would stay with your original post. In that way, all would know what was already suggested, and what perhaps didn't work for you, so that they wouldn't duplicate suggestions that you might have already rejected for one reason or another. Could you tell me what didn't work for you when you tried the Choose formula that I had suggested three days ago? Even though the formula I posted only showed four choices, I did mention that there was the ability to utilize the function to return 29 choices. -- Regards, RD ---------------------------------------------------------- ----------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------- ----------------- "sue" wrote in message ... Thanks everyone, but let me explain my situation. I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. . |
#5
|
|||
|
|||
formatting columns
If your main concern is simply the filling or copying down of the formula,
there is an almost instantaneous procedure that can be employed. If you first enter all your data, in say column A, and if there are no blank rows, simply enter your formula in say B1. Now, select B1. Hover the cursor over the "drag handle" until it changes from a fat white cross to a skinny black cross ... And just *double click*. The formula will be *automatically* copied down column B, as far as there is data in column A. This works for columns either to the left or right of the cell to be copied. If the cell to be copied is between two populated columns, the length of the left column takes precedence. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Sue" wrote in message ... Sorry. I'm not familiar with posting on these boards. Both formulas worked to a point. If I did the drag down method the cells filled in perfectly. I'm just trying to avoid that as I will be doing a lot of data entry during a clean up process, and preparing to use Access. That is why I wanted the column to be formatted to autofill depending on the number I put in the first column. Thanks again for all the help. -----Original Message----- It would be better for you Sue, and the rest of the folks who are willing to assist you if you would stay with your original post. In that way, all would know what was already suggested, and what perhaps didn't work for you, so that they wouldn't duplicate suggestions that you might have already rejected for one reason or another. Could you tell me what didn't work for you when you tried the Choose formula that I had suggested three days ago? Even though the formula I posted only showed four choices, I did mention that there was the ability to utilize the function to return 29 choices. -- Regards, RD ---------------------------------------------------------- ----------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------- ----------------- "sue" wrote in message ... Thanks everyone, but let me explain my situation. I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. . |
#6
|
|||
|
|||
formatting columns
Thank you one and all. Both formulas work great. I'll
go with the CHOOSE as I have more than 7 listings. I found that I can not get the whole column to highlight and format the whole column using the Ctrl/Enter, but if I drag the formula down a few rows, from there on it does what it is supposed to do. Thanks again. -----Original Message----- I'm not really sure I understand the problem. If the purpose is to avoid dragging down, you can highlight the range you want, enter a formula in the active cell, and then hit Ctrl Enter to fill the entire range with the formula. -- Vasant "sue" wrote in message ... Thanks everyone, but let me explain my situation. I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. . |
#7
|
|||
|
|||
formatting columns
Thanks for the feed-back.
Were you not able to use the "double-click" copy method? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "SUE" wrote in message ... Thank you one and all. Both formulas work great. I'll go with the CHOOSE as I have more than 7 listings. I found that I can not get the whole column to highlight and format the whole column using the Ctrl/Enter, but if I drag the formula down a few rows, from there on it does what it is supposed to do. Thanks again. -----Original Message----- I'm not really sure I understand the problem. If the purpose is to avoid dragging down, you can highlight the range you want, enter a formula in the active cell, and then hit Ctrl Enter to fill the entire range with the formula. -- Vasant "sue" wrote in message ... Thanks everyone, but let me explain my situation. I am preparing to set up a database in Access2002. My old database is Clarion. It was already antiquated when the department had ITS set it up 7 years ago, and I have to export to Excel any time I need to pull reports or due mailmerge with Word docs. That's why I'm setting up the Access database. However I'm having a hard time convincing my not so computer savvy boss to let me use Access. He knows how to use Excel on a limited basis...a very limited basis. It will be a few months before I will have the time to set up the Access database so I must do my data entry into Excel. There are already 8800 entries. Therefore I need the entire column formatted for the one formula. AutoFill takes care of most of the other 36 columns of information. I'm just trying to get the two columns to work together, as well has having 4 other columns automatically have something in them, that I can overwrite if needed. This last part I know how to do... I think. So... If I type a 1 in Column A, dogs should appear when I tab to B. If I type a 2 in the next row, cats should appear when I tab to B. But... I need this for all 16000 plus rows in the worksheet! Is there no way to highlight the entire column when I put the formula in... in other words, is there a formula that would automatically include every cell in the column, instead of my typing the formula in the first cell, and then having to drap it down the whole column. Also, if creating the spreadsheet ahead of time, and then protecting the one column...can I copy the current spreadsheet, with all the info in it from the Clarion database? Basically what I need is a formula that will work for the whole column, for 8 different choices. I've tried the =IF(A1:A16000,"dogs", .... that didn't work. . |
Thread Tools | |
Display Modes | |
|
|