Is it possible for excel to monitor Row J for a letter "Y". If a "Y" is put into a cell in row J a macro runs. If the "Y" was in column 6 I need the macro to copy A6 B6 C6 D6 E6 F6 etc to other cells on a different worksheet.If "Y" was in column 7 it will copy A7 B7 C7 etc to the same other cells on a different worksheet.
View 14 RepliesI am creating a spreadsheet to manage Block ID conflicts when adding mods to a Minecraft Server. Example spreadsheet is attached.
Description: Column A contains a list of available numbers (e.g. 1-20 and 31-50). Column B contains a list of numbers a new mod tries to use up by default (i.e. 1-25). A number in Column B that is not in Column A must be changed to the nearest number that is in Column A. In this example, numbers 21-25 in B are not available in A and must be changed to 31-35, respectively. Column C should be the result of changing Column B (i.e. 1-20 and 31-35).
Issue: The main problem is that as a given cell in Column B changes, that number needs to become unavailable in Column A for the next change. For example, when 21 in B returns 31, 31 can no longer be used for the next number in B (22).
I have been able to use VLOOKUP or INDEX/MATCH functions to lookup the numbers to be changed.
I've 2 worksheets, Summary and Sales. I need to get the total sales figure from the "Sales" worksheet onto the Summary worksheet. Sales data are pasted onto the worksheet monthly. Tried using vlookup but problem is, the column containing the total sales figure may vary and I need to update the vlookup formula. Another problem is that sometimes the Total sales (the lookup value) is not in the first column. Tried using the Index and Match combo but cannot get it to work. I've attached a simple mock up to hopefully illustrate my case better. I believe the experts in this forum should find this rather easy!
View 14 Replies View RelatedI have a series of numbers in one column. Is there some way I can divide all the numbers together by, say 10, without doing it manually?
eg.
12
15
15
25
20
should become
1.2
1.5
1.5
2.5
2.0
Is there a way in Excel to do that?
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
I have a worksheet that has a column of numbers entered as 1234567. The person that input these numbers should have done this: 123-4-567. Is there a macro that could do this automatically so I don't have to go into 1150 cells and manually change the format of each of them?
View 2 Replies View RelatedI have a excell spreadsheet that consolidate information from several other excel spreadsheets generated daily. To make this consolidation I have to open each daily excell file and copy some columns(always same ones) to my consolidate spreadsheet, but each daily information goes into a different column in the consolidated file.
So, I need a way to convert the columns names into numbers to be able to use loops on the VBA code where I can specify in each column the paste has to be done.
Anyone has the VBA code that can make this column name conversion into numbers?
I would like to have a macro which adds all the numbers in a column. The problem is that sometimes there are less sometimes there are more numbers. What I would like is, that the macro to sum the numbers right under the last cell which contains a number.
View 2 Replies View RelatedI have a simple spreadsheet for keeping track of gas mileage which I have been using for over two years with no problems. I have five columns A through E and right now I'm on row 72, that's where the problem started. When I enter the starting mileage in A and tab to B it changes the mileage number to a date and as my mileage number I type in changes the date also changes as I tab to B. See the sample below.
A B C D E
15357155968.491 28.14745024239
9/12/19420:00 10.44423.36269628244
The part that puzzles me is it does it in row 72, 73, 74, 75, and 76 then it goes back to working normal, and even though it puts the date in A and zeros in B it does the math right. I looked at column A and B for those rows and there isn't any formulas in them.
I Have a formula in column D =(B72-A72)/C72 and column E is just =B72-A72. Other then that the only other formulas I have is in column B row 177 it gives me total miles, in 178 it gives me total gallons, and in 179 it gives me average MPG.
When I run a report, for some reason our system imports certain numbers which have errors, as a consequence when I try and do a pivot table I get a lot of returns showing #div/o!. If you look at the spreadsheet the errors start on line 103 sheet 2 and finish on 2631. Sometime the report can be huge and takes a while to go through these manually to convert to numbers.
View 3 Replies View RelatedI have a large spread sheet with about 10 tabs. Is there any way to convert all the formulas to numbers in just one macro.
View 3 Replies View RelatedI have received an excel file from an external source. Every time I change a number in a cell it reverts to a 2 decimal number. eg. I type in 8710 and it converts the value to 87.10. I have looked at the number formats, cleared the numbers format but I keep getting the same result. I have also e-mailed the file to someone else and everything is ok on their computer. Is there a property in the excel program that I need to change?
View 3 Replies View RelatedWhat is the formula to cover multiple different text that could come up? I could have an answer that is NA, Yes and also a blank cell and if I could I would like to have it set up as follows:
NA = -
Blank Cell = 0
Yes = 1
I generate a range of random numbers in column C. Then I perform another task and then copy the randoms numbers in the range c4:c(some row) into another sheet.
But the two columns are not the same? I want to copy the same random numbers into the other sheet.
Code:
'Generate random numbers
Lastrow1 = Range("c65536").End(xlUp).Row
ActiveCell.FormulaR1C1 = "=RAND()"
Range("c5:c" & Lastrow1).FormulaR1C1 = "=RAND()"
'Finding a specific row number and copying random numbers
[Code] ........
Is there any formula or easy way to automate the changing of a numeric input into words, eg if you input 100 in one cell, the output in another would be "one hundred"?
View 9 Replies View RelatedI have never written or used a macro before and I have a simple macro task to complete:
I have Column L (L4:L10) of seven scroll bars that move according to number changes in column F (F4:F10). I want to create a form control command button that resets the changes on the scroll bars according to static column of numbers in Column E (E4:E10).
I don't know if it matters, but I'll add that I plan to add a second button that does the same thing with a different column of data. I assume I'll simply replicate whatever procedure I use in the first button - is this okay?
I was trying to decrease the decimal places of the data figures that I'm currently working on my report, however, it keeps rounding-up the decimal numbers. I wish to keep the original numbers and just decrease the decimal places.
For example:
The original figure is = 7260.12903225806
Upon decreasing the decimal figures to just 2, the result became = 7260.13
Is it possible for me to just have this result = 7260.12 instead of 7260.13?
I've tried using the TRUNC formula butit does not work if the 2nd decimal value is 0.
For example:
The original figure is = 227161.905808985
Upon applying the formula, the result became = 227161.9 instead of 227161.90
To convert numbers (975,095.97) to thousands (975.1) without physcially changing each one?
View 2 Replies View RelatedWhen opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.
The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.
I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?
I've got a main workbook (excel 2007) with a main spreadsheet where i group all the information (text and values) about brands and models linked to 10 workbooks (brands) with several spreadsheets (one spreadsheet=one model).
On the main spreadsheet I've got 16 lines for each model which they have the follow formulas, wich are linked to other workbook / spreadsheet (Mar/Gato)
1)
IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE));"";VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;3;FALSE))
and will return text values
2)
IF(ISERROR(VLOOKUP(V844;[Mar.xlsx]Gato!$A$3:$CE$78;82;FALSE));"";VLOOKUP(V844;[Mar.xlsx]G!$A$3:$CE$78;82;FALSE))
And will return number values
3)
IF(M844<>"";HLOOKUP($V$3;[Mar.xlsx]Gato!$A$2:$CE$78;2;FALSE);"")
And will return number values. The return value (;2 goes from 2 to 16.
This process repeats to all the 10 workbooks e and 250 spreadsheets
4) Table Array of the model spreadsheet
With regard to formula 1) and 2)
The problem is that the table array (on the model spreadsheet) could have diferent dimensions (becouse the launch year of the model) like this:
A$3:$CE$78 (2009 - Launch year)
A$3:$BR$78 (2010 - Launch year)
A$3:$BE$78 (2011 - Launch year)
A$3:$AR$78 (2012 - Launch year)
A$3:$AE$78 (2013 - Launch year)
And the return column (value) for formula 1) is always ;3;
And the return column (value) for formula 2) are like this:
;82; (2009 - Launch year)
;69; (2010 - Launch year)
;56; (2011 - Launch year)
;43; (2012 - Launch year)
;30; (2013 - Launch year)
;17; (2014 - Launch year)
The difference between above values is 13, it corresponds 12 months and the total column (12+1)
For the formula 3) the table array its the same, the only difference is instead of starting A$3, starts in A$2
5) The update of the formulas 1) 2) 3) for 2014
I'm doing this manually... The formulas are updated like this,for 2013, I've got table array A$3:$AE$78 and the return value (;17, then for 2014 i will have a table array A$3:$AR$78 and the return value will be (;30, and so on
5) Change the network drive
For other reasons I've to change my files to another network drive, something like this:
Q:KPI2014Main File
6) Main Goal
I was thinking using Excel's regular Find and Replace feature to change:
- The network drive designation
- the table array area
- The return value
But I don't know if this process ( Find and Replace) is safe and clean. I'm doing this manually and I've got on the main worbook/sheet 4000 lines with 3 formulas its 12000 operations!I'm on the 350 line....!
The main goal is to update the all formulas (links) to "brand" workbooks and inside "model" spreadsheets automatically with a macro.
I have been given a spreadsheet with standard configuration with column headers etc, but when you scroll down the sheet the actual column headers change from the normal A-Z display to be the column headers that have been entered into the cells.
I know this can be achieved similarly by setting Freeze Pane but how this other option has been switched on. I have attached the spreadsheet (which has totally fictitious content).
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
This is what I need:
Columns B, C, D & E are all populated with 3 digit numbers.
I would like column F to automatically populate with any of the 3 digit numbers that share two numbers, i.e.
F2 might look like this (using 00 as the pair):
001, 040
F3 might look like this (using 01 as the pair):
701, 051, 110, 001, 120
F4 might look like this (using 12 as the pair):
123, 721, 281, 912, 112, 120
etc...
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies View RelatedSay column A has either numbers or text in each cell, I need a macro to only get each number in each cell that is not a duplicate of a number in any previous cell and list each number found down column B.
View 5 Replies View RelatedI have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.
View 3 Replies View RelatedI want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies View RelatedIs it possible to delete a column of numbers then replace those numbers from adjacent columns and the adjacent columns are then filled with the numbers adjacent to them
View 9 Replies View RelatedI'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?
View 1 Replies View RelatedI'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location):
D2 is 17 (the row)
B4 is 2 (the column)
therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!