I have a long spreadsheet that is formatted and organized with six digit codes on the left, and then several lines of information on the right, in order to sort and compare to another report, I was trying to replace the blanks in the digits on the left....
View 7 RepliesHow do you delete rows automatically even if there are not an equal amount of rows in between the rows I want to auto delete?
View 3 Replies View RelatedI want to be able to replace the bolded and underlined areas of this javascript i have pasted in a cell with the value located in another cell. This means that those numbers would be replaced by a similar thing located in another cell. how can this be done? The way I would think you would do this by defining that say =b8 (an example of where the data could be coming from is ann excel formula, but not sure how to do this ...
View 6 Replies View RelatedHave a column of cells (521 rows), on the column each cell contains a group of number
Example:
27, 539, 484, 485, 506, 594, 523, 502, 505, 525, 541, 526, 527, 529, 542, 533
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 530
28, 540, 493, 494, 506, 594, 523, 502, 509, 525, 541, 526, 527, 529, 542, 533
Let's say I would like replace 27 to 163 but when I find and replay the "527" also changed to 5163. How can i filtered out 527 and just change the 27. I tried Match Entire Cell Content, it doesn't work.
I created a custom ribbon with tickmark button. The problem is that at sometimes, i will need 2 tickmarks in the same cell.
Actually in using the simple line :
ActiveCell.Value = "a"
And when i click on a second tickmark, it replace my "a" by ei. "b"...how to make it "ab" instead ?
OS=XP Pro
XL=2003 SP3
I need to move data within a cell. Is there a formula that can accomplish this? Here is an example of what I'm trying to do..
Column A
/ TN14YP244f
/ TN15YP245j
/ TN16YP246h
What it would need to do is take the last character in each cell and move it to replace the P in that cell. It should also capitalize the letter. ie: / TN14YP244f would become / TN14YF244
The letters at the end of the cell are always different, however the letter they replace is always the same (P).
I have a spreadsheet with around 1000 rows on data. in each row of data there is a number e.g. 2673 (all in their own cells) so in column A you would have a list of just numbers in another workbook, the number thats in each row corresponds to another value e.g. 2673 = CE103. So this workbook has 2 columns. In column "A" you have the numbers(2673) and in column "B" you have what that number matches with (CE103) i need a way of replacing all the numbers with what they equal
Workbook 1
A B C
2673 XXX XXX
513 XXX XXX
5107 XXX XXX
604 XXX XXX
Workbook 2
A B
2673 CE107
513 CDR57
5107 QV906
604 MNT57
There is an example of what i have (hope its understandable) and what i need is, the data from workbook 2 put in to workbook 1. So instead of having 2673 in workbook1, i would have CE107. And the same for the others e.g 513 would be CDR57.
1) What function can I use to replace a specific cell in spreadsheet 2 with what I type in a specific cell in spreadsheet 1 (e.g. type red in spreadsheet 1 in A1 and it will replace a blank cell A1 in spreadsheet 2 with red)? I have around a hundred spreadsheets which I need to name by typing a name in cell A1. Rather than doing this, I already have the names of the spreadsheets in a column.
2) Is there anyway to control the attributes of cells in another spreadsheet? E.g. change to bold, red and specific size font in cell A1 in spreadsheet 2 using cell A1 in spreadsheet 1.
I have a row of different dates in a speadsheet all in the same column e.g A1 to A10 What I am trying to achieve is that when it reaches todays date it will automatically change that cell to read Expired.
View 1 Replies View RelatedI have a data starting from column D. And the list goes like below:
D1: TO
E1: CC
F1: BCC
G1: Body
H1: Text1
I1: Text2
J1: Text3
I would like to obtain the final body in column K after replacing the text with column H, I, J.
Below is how my data looks and How i would like to obtain the final body.
To
CC
BCC
Body
Text1
Text2
Text3
Final Body
I have code that was working with cell references. I replaced one of the cell references with a Defined Name that I applied to the cell that was previously referenced in the code. The code does not work with the Defined Name now. In the code below, the Name "FeeType" was previously where the cell reference was. I have attempted to fix the reference by changing the use of " 's ....
View 9 Replies View RelatedRight click menus are a well known topic but I am struggling to find information on what I want to do with them. I have seen it done so I know its possible. What I want to do is completely replace the right click menu with my own custom one on a worksheet.
I do NOT want to add things to the existing right click menu I want to effectivly disable the existing one and put on my very own one in its place. I have looked at the code for adding a menu to a textbox etc which seems sound but I can't trigger it with an event. I don't have any code to show Im afraid as I am just playing with other people at the moment trying to get it to work.
I have two worksheets, one contains lots of data and the other references this data to form a summary.
The problem is that having set up all this I have found that not all the references on the summary sheet will always reference a cell with data in it and so bring back a value of 0.
Since this summary sheet will be used to enter data into a database I would prefer a blank cell over a 0.
I know I can use an IF ISBLANK to do this, but dont fancy going through each cell and re-writing the formula, so- Does anyone know an easy way of changing all the cell references to include the IFISBLANK statement?
I need some direction on how to proceed with dismantling an address cell. I have a workbook that one field is an address label field. What I mean is the entire address is entered in one field just as it would appear on an envelope. I need some suggestions on how to break this apart and create individual fields for the individual parts.
View 6 Replies View RelatedI would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I'm running Excel 2010.
In a field containing this: Bergamot, Orange*(11,16) I want to put a space before the asterisk. There are hundreds of these in the spreadsheet with other text before and after the text to be replaced. I made sure the field is defined as a text field.
I searched on "*(" and said replaced with " *(" and ended up with " *(11,16)" - the Bergamot, Orange was removed from the field.
My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.
I'll try to be as clear as possible:
tab1 has relevant cells "a" and "b", same row
tab2 has 3 relevant cells "c", "d", and "e", all on the same row
All cells are text values - some digits, but should be treated as a regular String.
The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.
I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.
View 7 Replies View RelatedI've got a range of data in Column D approx 50,000 rows long and I need to go down this range and when theres a blank cell copy the info from the cell above. I've got some code which loops through this but I need to make sure I put "EOF and the bottom of the info to stop the loop. Is there a slicker way of writing this code?
Code:
Sub TestBlankCell()
Range("D5").Select
Do
[Code].....
Here is what I am trying to do with no luck so far.
If I type RS23U1R109000 in a cell A1, I want B1 to read the 5th letter or
number and fill B1 with E86.
Example
A1= RS23U1R109000 B1=E86
A1= RS23V1R109000 B1=E87
A1= RS23R1R109000 B1=E84
As you can see in my example, the 5th letter could be U,V,R or whatever, but
I need cell B1 to read that letter and populate B1 with E86, E87, E84 or
whatever.
I have names in cells in this format.
Smith John
I would like to switch that around so the name in the cell looks like this
John Smith
Is there an easy way to do this?
I have about a 100 sheet workbook for a project my company has upcoming. All the sheets are numbered 1-100. I also have another sheet that has the description of each pay item on ROW B of the sheet. Well here's my question. I'm trying to get a function that i can just copy and paste in each sheet instead of changing the number on each sheet.
For instance...
I would be using =Descriptions!B2 for sheet 2, and then =Descriptions!B3 for sheet 3 and so on. My question is does anyone know how i can get the sheet name so then i could just copy and paste one function that would be like =Descriptions!B(SheetName)
So that way i can just copy and paste instead of having to change it each time. I found this function to get the sheet name but can't seem to incorporate it into the =Descriptions!B
Is it possible to extract certain info from one cell. Example in cell A I have a description of a product plus price I only need the price, The price has a $sign in front of it. Example below
I have over 2000 products so I was hoping I wouldn't half to go through one at a time. Is is possible to extract just price and place it into cell b.
1 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $22.50 Stock Quantity: 107 YD
2 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $28.50 Stock Quantity: 107 YD
3 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $19.50 Stock Quantity: 107 YD
4 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $119.50 Stock Quantity: 107 YD
5 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $67 Stock Quantity: 107 YD
6 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $89 Stock Quantity: 107 YD
7 "Damask 100% Silk Color: Creme Approx Width: 55"" Approx Rpt: V. 16, H. 13 Reproduction Circa: Late 16th Century Renaissance Revival Price $25.50 Stock Quantity: 107 YD
8 and so on
I am trying to get info from a cell/Row of the height just like "=CELL("width",A1)" Only I need Height info returned, is this possible?
View 10 Replies View RelatedI need a macro that will, on a button click, save a workbook and naming it what is in cell B3 and B5.
View 9 Replies View RelatedI have a cell that has address and city with postal code.
I need to be able to extract the city and postal code on its own.
I know i could do Data - Text to columns but i would like to keep the cell intact and not have to split it up.
Here is an example of the cell in question:
1234 Parc street, Montreal H1A 2N2
What I'm trying to put together is a sheet where i work out how much margin i will be making on a sale (which i done). The bit I'm stuck with is the recording the data on a separate sheet.
What i have done so far.
I created a Macro button to insert and copy cells C6, C7 & C8 (the bits i need). Which works but it takes the formular with it so everytime i chage the figure on sheet 1 it changes in sheet 2.
So what i'm asking is there a way to mirror what is display rather that copy?
I created a small block of cells on my worksheet where i have in separate cells, the day of the week, the days date and the month. I want to design a special memory calendar for my mom in early stages alzheimers.
There are cells with specific info which i do not want to re type for the whole month/year.
I want to be able to copy all the info into another block of cells and have the day of the week and date change also the month.
Whenever I get information from finance.yahoo.com or from my job and put it into excel all the information seems to go in one cell and numerous rows. (I think its b/c I am putting information that isnt' meant to be in excel and forcing it to open up there.)
For Example in cell A1 I will have
"AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78"
Cell A2 I will have
GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1
What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2 - GOOG and cell B2 508.9. I don't even want the rest of the stuff.
Is there any way or formula I can use to just pull the information out of cell and have it separated for me?