Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
i have 2 cells of numbers that i want to merge together, for example
A B 1 4 2 11 3 7 etc but i want the result to be "A"."B" so row two would be... 2.11 but how do i get row one to be 1.04??? and row three should be 3.07 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
Hi Fiona,
It's not really the format you want changing, it's the value. When you have a "1" and a "4", the "4" you really want it to mean 0.04. I think all you really wanna doo is divide the "4" by 100 and add it to the "1". So, if you had 1 in cell A1 and 4 in cell B1 you could type in cell C1 = A1 + (B1/100). That should do it. Gloria. "fifi" a écrit dans le message de news: ... i have 2 cells of numbers that i want to merge together, for example A B 1 4 2 11 3 7 etc but i want the result to be "A"."B" so row two would be... 2.11 but how do i get row one to be 1.04??? and row three should be 3.07 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
thanks Gloria I was going bout it the whole wrong way!
do you know if you want this to be done for all your rows do you have to set up a macro for it to loop through the rows?i have over 100 rows of this to be done. im really new to all this :( Gloria Thrurk wrote: Hi Fiona, It's not really the format you want changing, it's the value. When you have a "1" and a "4", the "4" you really want it to mean 0.04. I think all you really wanna doo is divide the "4" by 100 and add it to the "1". So, if you had 1 in cell A1 and 4 in cell B1 you could type in cell C1 = A1 + (B1/100). That should do it. Gloria. "fifi" a écrit dans le message de news: ... i have 2 cells of numbers that i want to merge together, for example A B 1 4 2 11 3 7 etc but i want the result to be "A"."B" so row two would be... 2.11 but how do i get row one to be 1.04??? and row three should be 3.07 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
After entering the formula in C1, just copy the formula down the col as far
as required by dragging the fill handle* of C1. *the solid square at the bottom right corner of C1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "fifi" wrote in message ups.com... thanks Gloria I was going bout it the whole wrong way! do you know if you want this to be done for all your rows do you have to set up a macro for it to loop through the rows?i have over 100 rows of this to be done. im really new to all this :( Gloria Thrurk wrote: Hi Fiona, It's not really the format you want changing, it's the value. When you have a "1" and a "4", the "4" you really want it to mean 0.04. I think all you really wanna doo is divide the "4" by 100 and add it to the "1". So, if you had 1 in cell A1 and 4 in cell B1 you could type in cell C1 = A1 + (B1/100). That should do it. Gloria. "fifi" a écrit dans le message de news: ... i have 2 cells of numbers that i want to merge together, for example A B 1 4 2 11 3 7 etc but i want the result to be "A"."B" so row two would be... 2.11 but how do i get row one to be 1.04??? and row three should be 3.07 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
but can this be done through a macro? so that you can just click button
n it does it automatically? Max wrote: After entering the formula in C1, just copy the formula down the col as far as required by dragging the fill handle* of C1. *the solid square at the bottom right corner of C1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "fifi" wrote in message ups.com... thanks Gloria I was going bout it the whole wrong way! do you know if you want this to be done for all your rows do you have to set up a macro for it to loop through the rows?i have over 100 rows of this to be done. im really new to all this :( Gloria Thrurk wrote: Hi Fiona, It's not really the format you want changing, it's the value. When you have a "1" and a "4", the "4" you really want it to mean 0.04. I think all you really wanna doo is divide the "4" by 100 and add it to the "1". So, if you had 1 in cell A1 and 4 in cell B1 you could type in cell C1 = A1 + (B1/100). That should do it. Gloria. "fifi" a écrit dans le message de news: ... i have 2 cells of numbers that i want to merge together, for example A B 1 4 2 11 3 7 etc but i want the result to be "A"."B" so row two would be... 2.11 but how do i get row one to be 1.04??? and row three should be 3.07 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
You can copy it down using the mouse and that little cross thing but i never
do it like that. You can zip around in Excel without using the mouse. For moving around, and copying, the mouse is mostly a waste of time. This long to explain but really fast to do. You should really take some time to learn function keys and the keyboard shortcuts. Real, real timesavers. Type in the "a1 +(b1/100)" formula in c1 press enter. press F5 and type into dialogue box the reference of the last cell in the column. In this case it would be c100. Excell will position the cursor on c100. In the C100 cell, type a zero. with your left thumb, hold down the Ctrl key. with your right index, press the Up arrow (not the Page up !) Excel should position the cursor back in C1. In fact Excel goes up and slams the brakes on when he reaches a not-empty cell and, as your formula is in C1, then C1 is not empty, the cursor will stop there. Now with your left thumb, hold down the Ctrl key. with your left index, hold down the Shift key with your right index, press the Down arrow (not the Page Down !) Excel will move the cursor down and slam the bakes on when he hits C100 ! Why ? because you held down Ctrl, and because of the zero you put in there makes c100 the first not-empty cell. But this time, he not only goes there but he selects all the cells on the way down. Why? because you held down the Shift key while you pressed the Down arrow. Now press Ctrl +D to fill down the selected cells with the formula from C1. This is long to explain but really fast to do. Your hands don't leave the keyboard and there's not "footerin around" with the mouse. Have fun Fifi ! Gloria. "Max" a écrit dans le message de news: ... After entering the formula in C1, just copy the formula down the col as far as required by dragging the fill handle* of C1. *the solid square at the bottom right corner of C1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "fifi" wrote in message ups.com... thanks Gloria I was going bout it the whole wrong way! do you know if you want this to be done for all your rows do you have to set up a macro for it to loop through the rows?i have over 100 rows of this to be done. im really new to all this :( Gloria Thrurk wrote: Hi Fiona, It's not really the format you want changing, it's the value. When you have a "1" and a "4", the "4" you really want it to mean 0.04. I think all you really wanna doo is divide the "4" by 100 and add it to the "1". So, if you had 1 in cell A1 and 4 in cell B1 you could type in cell C1 = A1 + (B1/100). That should do it. Gloria. "fifi" a écrit dans le message de news: ... i have 2 cells of numbers that i want to merge together, for example A B 1 4 2 11 3 7 etc but i want the result to be "A"."B" so row two would be... 2.11 but how do i get row one to be 1.04??? and row three should be 3.07 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
Fifi !!!!!!!!!!!!!!!!!!!
Stop talking about macros. Macros have nothing to do with it. You just want to copy a formula from one cell to another. You can do it with the mouse as Max suggests. Or with the keyboard as I suggest. You need to get more familiar with the basics before getting on to macros. You're trying to do the triple-salto before the cartwheel. Gloria. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
Ya i know its real simple to do that way but its for a report so they
want to be able to click a button n hey presto...it does the job for them i get the other ways and thanks for that Gloria Thrurk wrote: Fifi !!!!!!!!!!!!!!!!!!! Stop talking about macros. Macros have nothing to do with it. You just want to copy a formula from one cell to another. You can do it with the mouse as Max suggests. Or with the keyboard as I suggest. You need to get more familiar with the basics before getting on to macros. You're trying to do the triple-salto before the cartwheel. Gloria. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
merge 2 cells and change format
If you want a macro then start recording and follow these steps.
In C1 enter the following formula =A1+B1/100 Copy down to bottom of list. Copy column C and right click on A1 and select "Paste Special" select values and ok. Delete columns B and C Stop recording you now have a macro that will do this automatically. TIP to start recording a macro select Tools-Macro-Record new Macro to stop recording select Tools-Macro-Stop Recording "fifi" wrote: Ya i know its real simple to do that way but its for a report so they want to be able to click a button n hey presto...it does the job for them i get the other ways and thanks for that Gloria Thrurk wrote: Fifi !!!!!!!!!!!!!!!!!!! Stop talking about macros. Macros have nothing to do with it. You just want to copy a formula from one cell to another. You can do it with the mouse as Max suggests. Or with the keyboard as I suggest. You need to get more familiar with the basics before getting on to macros. You're trying to do the triple-salto before the cartwheel. Gloria. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change format of cells (bold, italic) based on one cell's answer | Excel Discussion (Misc queries) | |||
why would the merge box in the format cells menu be greyed out | Excel Discussion (Misc queries) | |||
how do I format cells to change date and time to just date | Excel Discussion (Misc queries) | |||
Change decimal format of cells depending on conditions? | Excel Worksheet Functions | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) |