to create a macro that insert [url] for column a infront of the data currently there.
So in coulumn A, it might have default.asp
I wish to insert [url] infront of it for column A from range row 1 to 10.
I am working on a project for a monthly report. My back sheets have (or will have) 12 columns of info for monthly income/expense for multiple companies. The front sheet will have 2 columns, one for the past month & one for cumulative.
Obviously the cumulative is an easy one to pull off, but how can I make the correct column show up for the current (last) month I am working on.
Rather than make it tied to the actual date of input, I have used an approach in the past with a "sum if" formula for a single reference where =sum if(b6:b6,a2,a6:a6) but I'm not sure how to make it work for 12 references. Can this be done?
Need a function that would insert a letter or a number in front of numbers in a cell for example
column A
3245
I want to insert the prefix "S" in front of the nummbers 3245. so i would hopefully end up with
Column A
S3245
Is there a way to delete excessive carriage returns at the beginning of data. I pull reports from a SharePoint list where submitter entries often often contain multiple carriage returns before the text or data. I use ASAP utlitily to clean out excessive spaces before and after data, but I don't find a way to remove these multiple carriage returns so that the text entries are easily viewable when row-height is less than gigantic. Cells with extra carriage returns before the text entries end up looking like the following:
___________________________________________________________________
| |
| |
| |
| |
| Product page for CQ1-14047.LA is missing Swindex section (Core Drivers). |
|__________________________________________________________________|
I have an Excel chart graphing two data records. As the data points cross, one is in "back" and one is in "front". I would like to reverse the current layout. Is this possible?
View 1 Replies View RelatedHow to move the data from the back column to the front as below
A
B
C
2
3
1
3
1
2
to
A
B
C
2
3
1
3
1
2
what's the fastest way to add a word or phrase in front of the data already in a column of cells?
Example:
cell contains product description and I need it to show
company name product description
I can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies View RelatedWhy is Excel so back-***wards on this? Is there a VBA solution to having a new sheet inserted after, not before, the current sheet that can be attached to an icon?
View 3 Replies View RelatedIs there a way to insert a blank row between every row with data? This is for very large spreadsheets when adding blank rows individually is not an option.
Example:
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Needs to look like this:
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
There 2 columns, 1 in which you input data and the other which has a formula which uses the data to do a calculation. There are about 30 rows already filled in.
When I go to insert a new row of data, the formula just appear after I type in the input column. If I click on the cell where the formula appeared before I typed in the data, there was no formula there. It is almost like an autofill, excel knows that I am going to copy down the formula. (It also copies down the formatting)
I have an excel spreadsheet set up as shown in the attachment. When I insert data by using the macro (Insert farm), for some reason it puts a NA in a cell (C17).
I then select the cell click into the formula and click enter and it works. how can this be fixed. Look at the excel spreadsheet to be able to understand.
i have a list of numbers in column A and in column B every now and then a * will appear next to a number. The first number will always have a * next to it. Basically i'm looking for a way to say...that either by copying to another tab or inserting rows on this tab, do the following for every number.
Insert a row above that says
[pf6]
put a " next to the number so that it reads as below
"408425
and then the following in new rows as below.
[enter]
[tab field]
[tab field]
[tab field]
[tab field]
"1.5
[field+]
[pf5]
I need code that can work down a column and insert a blank row when it notices the data value has changed. i attached a file to show what i am looing for.
View 4 Replies View RelatedI need to insert data as following pattern :
1 1
2 1
3 1
3 2
4 1
4 2
4 3
I am having an excel file in which there is information about 100 products. Every product has around 10-15 features. This way I have 1,000 rows. Every product has its own unique ID i.e. every 10 rows have the same ID thereby making 100 ID's. Now I want to insert a blank row every time the product ID changes so that I can discriminate better between the products. Is there some formula?
A
A
A
A
A
B
B
B
B
C
C
C
C
I want to insert blank rows before B and C. If the number of features were constant, it would have been easy for me.
Im trying to find the best way to insert data into table.
source of data is workbook range and/or array the obvious one is
Code:
With Sheets(1).ListObjects(1).ListRows.Add.Range
Cells(1, 1).Value = xxx
Cells(1, 2).Value = yyy
Cells(1, 3).Value = zzzz
end with
but if its lots of data it could get very slow.
Q1. is there a way how to insert multiple records at once, something like
Code:
'does not work
With Sheets(1).ListObjects(1).ListRows.Add
.Range(Cells(1, 1), Cells(1, 3)).Value = arrXXX
End With
I found it faster to store data into array and then paste them
Code:
rw = ListObjects(1).HeaderRowRange.Row + ListObjects(1).ListRows.Count + 1
range(cells(rw,1),cells(rw,3).value = arrXXX
but I had issues with it especially if the destination table has some filters applied. I'd also prefer not to clear the filters during the sub
Q2. are there any other options that would be fast and reliable?
What I'm really looking for is to box in data as the data changes using the border feature.
Column A would contain the data to look at however there may be additional data in columns B, C, D and so on that would need to be "boxed in" with the group of like data.
I can't get the HTML to work so we'll have to go sans spreadsheet on this one.
I have rows of data with each row going from column A to column P
They are sorted by column A (account No.)
I need to insert 2 blank rows between each change in account No.
I have a list of consecutive numbers (1 to 150) in column A. Sometimes some of the numbers are missing e.g (1 5 6 9 10) and therefore there are less rows. I would like to insert rows to make up the missing numbers so that I always have 1 to 150.
Need the right direction, I have looked through the 150 threads regarding "insert row"
I want to insert data validation to a cell by VBA. When i record the macro to put a list (value1;value2;value3) to a Cell the following code is generated and the validation works OK.(dropdown of the 3 values)
Sub Validation_by_VBA()
Range("A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="value1;value2;value3"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
If i run this code from VBA I have only one value in the dropdown (value1;value2;value3) How can i change this code so it will work from vba as well?
The list of data is shown in sheet1
In sheet2 I have shown the data that is copied
I have manually shown comments on 2 cells in sheet2 that shows detailed description on how was the total taken.
I need the comment on all the cells from D8 to G11 (not taking the total column) that shows on how was the total taken.
When any entry in sheet1 is inserted or deleted the comment in sheet2 should change accordingly.
I also do not want to freeze the cells D8:G11 as when new entries are made or deleted the columns would increase or decrease.
All blank cells I do not need any comment.
Attached is a sample of what I need completed.
Monthly, I have to do a chart just like this except slightly more complicated.
In the Sample download, there are three charts, "Sample Chart", "Sample Input", and "Desired Result".
"Sample Chart" has a list of accounts from different companies, The first column being their number, the second being their name, and the third being the money they spent.
The "Money Spent" Column is always blank when I start for ALL companies.
I have another chart, "Sample Input", which contains the prices that I'm supposed to put in "Sample Chart, Money Spent" column.
The thing is, "Sample Input" only has the companies with prices listed.. Not all companies have prices, so this means the "Sample Input" is always a shorter list than "Sample Chart".
What I need is the prices from "Sample Input" to be put in the correct position in "Sample Chart". The "Desired Result" chart is what I want it to look like.. exactly like that!
When I do this monthly, I have to scroll through several thousand accounts doing this.
Suggestions:
- Possibly have a macro or formula take the Account # in "Sample Input".. Sample it in "Sample Chart", then copy the price and paste it in the right location.
- Possibly make "Sample Input" have blank rows inserted in the places where it should have the account with no prices.
I have a column with intervals(every 30 min) and with some values. My problem is that if there is that if the value is 0, the interval is deleted. Is there a way(maybe macro? ) for the excel to see that an interval is missing and add it with value 0?
Attached File : intervals.xls
what I need is to insert the data in a cell, and instead of copying this same data to other cells in the same sheet or in other sheets, I need a way so that once I insert the data in one cell, it will be copied to the other cells at once.
For example, if I insert data in the cell [Sheet1,A1], it will be copied to [Sheet2,A4] and [Sheet3,E8] and [Sheet3,I7]
I got a table, some columns are variable data you have to put in by your own and I got some columns with only formulas. After entered the last variable data I want excel to add a new row with the same formulas and format as the other rows in the table.
View 2 Replies View RelatedI have posted in a existing thread at mrexcel as i believe my problem and possible solution is very similar to that already detailed there. However i have not recieved a response there yet and was looking to draw upon more minds here ...
View 9 Replies View Relatedi've got supplier list on the excel file and need to insert two blank rows under each supplier name. the two blank rows need to be inserted no matter whether the supplier may be occupying one row or 100. for example
from this
a ltd
a ltd
b ltd
b ltd
b ltd
x ltd
m ltd.........
Hello, I have a task that would take me hours to complete and I'm sure there's got to be a way to do it in Excel 2007. I need to get part numbers from one master cross reference spreadsheet to individual spreadsheets. The master cross reference spreadsheet has our company 7-digit number AND the manufacturer part number, while the individual spreadsheets only have the manufacturer part number. I need to have the individual spreadsheets match manufacturer part numbers and then insert our company's 7-digit number line-by-line.
Master spreadsheet (snippet):
7-digit PNMFG PN
2206422A04-04L
2209624A04-04S
2203436A04-054
2208761A04-06A
2208897A04-06C
2208942A04-06D
2209292A04-06E
2209108A04-06F
2209627A04-06S
2208764A04-08A
2208900A04-08C
Individual spreadsheets (snippet):
7-digit PNMFG PN
?A04-604
?A04-605
?A04-606
?A06-604
?A06-605
?A06-606
?A06-608
?A06-610
?A08-606
?A08-608
?A08-610