I got a list with numbers on Sheet"Bookings"
8000
8001
8002
and so on.
When i fill in a name like commission on Sheet1"Freshbooks"
The number 8000 must be connected with that ..so that it will appear on my last sheet "Ouput"
So to summ it up Sheet1 I fill in commision on table H the number 8000 must apear on sheet"output"
Is this possible in a Formule or in a VBA line???
I am trying to "auto fill" a column with numbers in sequential order. (1,2,3,4,5...) I typed in 1,2,3... in the first cells and clicked on the black corner dot to drag the sequence down. It keeps putting in 1,2,3,1,2,3,1,2,3..... I have also went into the advanced options and clicked the "enable fill handle and cell drag-and-drop" and also Alert before overwriting cells" and it still fills in 1,2,3,1,2,3....
View 9 Replies View RelatedI need a macros to fill 2000 rows with unique random numbers from 1 to 19 in B2:T2001 area. Every row like this
14 11 12 7 18 13 19 5 6 16 9 4 8 15 1 3 10 2 17
Actually it's about random positions in rows for numbers from 1 to 19.
I have a button event coded to select and ultimately fill a range of cells with the intent of representing a timeline of payment periods.
Private Sub Timeline_Click()
Dim x As Integer, y As Integer, cell As Range
x = 1
[Code]....
The first row needs to be filled with numbers from 1 to the value of [D20]; E.g. if D20=10 then there would be 10 columns selected then filled 1 through 10.The second row needs to be similarly filled with ascending Y values; E.g. D13 = 4, D20=8: row 2{.5,1.0,1.5...}
I have groups of data in a spreadsheet, I need to assign a value in column f for each piece of data in column e. The value needs to be a relative value for the rest of the group. For example if one group of data has 10 rows I need values 1 - 10 in column F, 10 being for the largest value in the respective row in column E and 1 being for the smallest value.
Each group does not have the same number of rows.
If need be I can add blank rows so the space between each group is equal, (if you can tell me an easy way to do this I would appreciate it as theres quite alot of data).
the following issue:
I have a spreadsheet of questionnaire responses which range from 1-7
For example:
Respondent Q1
1 4
2 3
3 7
4 6
So each row is a new respondent and each column is their response from the scale.
What I need to do is code the responses into a different form. I need them to be represented as follows:
Respondent Answer1 Answer2 Answer3 Answer4 Answer 5 Answer 6 Answer 7
1 0 0 0 1 0 0 0
2 0 0 1 0 0 0 0
So that each number then represents the place on the scale from which it was chosen.
I tried recording a macro but I think this requires something a lot more complex.
I’m not even sure I can do this- but- I want to be able to have a few columns, 3 of them I want to be Item #, Item, Cost.
Is there a way to put the Item # (Number) in, then have the other 2 automatically come up?
I have a table of information on a worksheet and one of the columns determines whether the item in that row is active (there will be active and inactive items in this "Main List")
On a seperate worksheet (in the same file) I want to only display the active items in a new table (Overview) and always ensure it has no empy spaces other than at the bottom of the list, so if row 1/2 in the Main List are inactive items then the first row in the Overview List would display row 3 from the Main List.
Ideally I would rather do it in a formula than VB if possible.
I've attached a file showing what I am trying to explain, where the Overview tab currently has the data typed in it should automatically populate based on column "I" on the MainList tab.
example.xls
I want to be able to have a macro that will compare one cell to the rest of the cells in a collum. I have used the COUNTIF() formula and it's getting on my nerves a bit because it seems as though everytime i sort the data, it totally messes up the formula (as in it changes the cell refernces to seemingly randomly generated numbers, but that is a different matter! lol). here is the formula, and it will be followed by the formula that is in the cell directly below it: =COUNTIF(F2:F65536, F2) and the one below it: =COUNTIF(F2:F65536, F3). Only one number needs to change, but when i fill down in excell, it wants to add one to every number, even when i click COPY cells... oh well. So i want to write this macro that will do just that. That way, it has fixed reference parameters e.g. F2:F65536, and one variable parameter e.g. F2, F3, F4 and so on. I'm not too savvy with this whole VBA stuff... the university insisted that everyone uses C++ nowadays, lol. I would like this macro to output its data into an adjacent cell ex. if F2 was compared, the data will be placed in H2.
View 10 Replies View RelatedI m looking to fill down a column where I am assigning a number that maps to a certain week of the year. In essence after every 7th value the number should increment by 1.
Date Day ConsumptionWeek number
1/01/2009Thu 827 1
2/01/2009Fri 907 1
3/01/2009Sat 1,013 1
4/01/2009Sun 1,051
5/01/2009Mon 1,097
6/01/2009Tue 1,155
7/01/2009Wed 1,044
8/01/2009Thu 976 2
9/01/2009Fri 1,034 2
10/01/2009Sat 1,107
11/01/2009Sun 1,073
12/01/2009Mon 1,161
13/01/2009Tue 1,395
14/01/2009Wed 1,172
15/01/2009Thu 1,060
16/01/2009Fri 1,008
I fill mentioned data by Qty values in Sheet2?
Sheet1
ABCDE1NameLogQtyCodeZop2A140005RFG-13B150001RFG-14C980003RTH-1
Excel tables to the web >> Excel Jeanie HTML 4
Required result like this.
Sheet2
ABCD1NameLogCodeZop2A14000RFG-13A14001RFG-14A14002RFG-15A14003RFG-16A14004RFG-17B15000RFG-18C98000RTH-19C98001RTH-110C98002RTH-1
Excel tables to the web >> Excel Jeanie HTML 4
im filling out a worksheet.
on the rows i have this data ex. (123467.00) is there a way i can format the cell so the final product is this ex. (1234.67).
i've tried may things but what i get is (123467)
i have multiple worksheets like this just want something that can do automatically instead of manually.
How can you fill up a listbox on a form with data from a couple of cells.
For example you have the worksheet Colors and you have these values in the cells of column A
A1 = red
A2 = yellow
A3 = blue
Now I want red, yellow and blue to be in the Listbox.
In my worksheet I have a column with the number 1 in cell G3 ,I want to fill down the column with the number 1 as far as there are rows that have text in them. How do I tell Excel to fill down only as far down as the end of my table.
I would like to run this in a macro.
I have searched the site but cannot find an appropriate solution. I have a list of names in column a, some of the names are repeated if the name is repeated then at each occurence of the name I would like a '1' to be placed next to it in column B.
I am trying to adapt the conditional formatting formula:
= COUNTIF($A$1:$A$25,$A1)>1 and then I guess I need to add a '1' else ' null' command but I just can't seem to get the syntax correct.
I have a workbook where one worksheet is referencing cells from another worksheet. I need every third cell to pull from every single cell on the other sheet.
I have found on other threads that =INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1)) worked. However when I filled down it wont increase the row reference.
ie.
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+1))
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+2))
=INDEX('WORKSHEET!B:B, 1*(ROW($B$4:$B$4)+3))
[ATTACH]334142[/ATTACH
The other threads mentioned it should fill down fine, but not working for me.
I'm working on some data dealing with time fields, and I need to have them counted in 15 minute intervals.
For example, one of the entries listed in a column has an event starting at 02:25:00 and ending at 02:55:00 (30 minutes). Along the top row are times listed in 15-minute intervals, e.g. 13:00:00, 13:15:00, 13:30:00, etc. In the corresponding cells, if the entry took place within a particular interval, I need it to say "1", otherwise have it say "0" or "-" or something denoting no event.
An IF statement was used, but it didn't seem to work.
If that's a little hard to follow, check the example attachment for a better idea of what I'm trying to describe.
In the example spreadsheet, Row 2 (manually typed in) is what I'm looking for. Row 3 is the (failed) attempt to use the IF statement.
I have to validate cell text are (1, Absent, 2,leave, 3 present, 4 late 5 no report)
at the same time Absent should filled by, red leave by pink, present by green, late by yellow and no report by blue.
Preferably I dont want to use VBA. I am using excel 2007 but I am not excel expert.
Not sure if this can or would be done in vlookp??. In my example the print page needs to get data from a list where people set.
View 3 Replies View RelatedI am working on an inventory sheet. Its probably only complex due to the amount of data. There are several hundred locations we service and these are in a single column, and then there is the equipment we use that is several hundred columns.
Each location uses 3 rows and the 1st needs the formula to equal 2 rows beneath it. i.e. cell E28 has the formula =E30. I basically need to fill down, but only put that formula into every 3rd row, as the two rows beneath this row need to stay blank, or show number i manually put into either.
The second part of this, is there a way once that is done, to just select those cells, and fill to the right all the way? Again, i do not need to fill the two rows under that one.
I have attached part of the sheet .....
The green cells are the ones that need the formula, and those will be copied down, and right. the others need to be left alone. I have been sitting here for an hour manually entering in the =XXX for each cell.
filling an Array then I have used in this macro?
View 3 Replies View RelatedI have a userform with a textbox and a combobox
textbox input will be a house number
combobox will be a list of all the street names that have that house number in my database
column Q are house numbers, column R are street names
when a user inputs a number into the textbox and tabs to the combobox,
for each cell in column Q of workbooks("proposals.xls").worksheets("database") that has the textbox1.value I want the cell value of .offset(0,1) to be added to the combobox.
Is there a way to fill all rows of a column with the same formula without having to copy and paste it? My table is huge (about 6000 rows) and I need to perform the same computation on each row.
View 8 Replies View RelatedIf I have a dropdown menu in A1 which has the following data:
Chair1
Chair2
Chair3
and so on..
How can I create a funtion to B1 which would work so that if there is the text "Chair2" in cell A1 it would automatically give a value 200€ to B1?
Is there VBA color to change ".Fill.ForeColor.SchemeColor =" into NO FILL? In other words, keeping the existing Foreground color prior to the macro being activated.
I'm in a chart.
Im doing an assignment for my VBA class and im in a jam.I have a form with a listbox and numerous textboxes. The user is to select a name and then the information from this name fills the corresponding boxes.
First off, my teacher is horrible, i have to learn by just reading so that's why I'm so puzzled. I have an array i made by importing/parsing a text file. I've attached the .txt file for reference. I currently want to add just the names from the array to then when the user selects the name, fill the remaining boxes with the corresponding information.
This is what I have thus far...
Code:
Dim nValues As Integer
textFile = "C:UsersMattDocumentsemployeedata.txt"
Open textFile For Input As #1
[Code]....
need it to ONLY display the name, but then display ALL names in the array. What do i need to do?
I have done a V-lookup that auto populates my project report doc with different lead times/dates depending on the product selected. Problem I have it that the dates being pulled through are for all calendar dates and not just working week days.
I know you can select a selection of dates and fill as week days however all of the dates on my primary sheet either have the project start date minus lead time formula or the vlook up formula in it. Filling week days overwrites any of this.
I'm trying to fill some combo boxes on a userform with arrays stored as variant type (this is how I was told it's done). The problem is, I'm using exactly the same code in two subroutines and it works in one and not in the other. I have a button to reset the form and a subroutine that runs when the form is initialized. Here's the code for the reset button which works:
Code:
Private Sub Reset_Click()
App1Date = ""
App2Date = ""
App3Date = ""
App1Time.Clear
App2Time.Clear
App3Time.Clear
[code]....
At the moment I've got the 'Journeys' and 'TimeArray' declared as public variables for maximum scope, so that they can be used by both subroutines. But I've also tried declaring them localy in each subroutine and it still doesn't work! By using a breakpoint I can see that the line
Code:
carJourneysSaved.List = Journeys
is definitely running when I expect it to but the combo boxes remain blank unless I click reset!
I have a list of data in a column that has blanks along the way.However, I don't just want to fill it up with the one above, or the one below.
If the data is in column B, I want to fill it such that where the row above has the same criteria in column A, column B for the above row is returned.Otherwise, column B in the row below should be filled in instead.I have set up example data below to illustrate.
Initial Data Set:
A
B
1
Criteria
Number
2
a
1
I have a quick question in relation to filling a range with an array.
When I use the following code:
Code:
Sub justdoit()
s = Array(1, 2, 3)
Range("A1:C1") = s
End Sub
It works fine. but when I try to populate the range in rows instead of colums, the array value stays as the first value.
Code:
Sub justdoit()
s = Array(1, 2, 3)
Range("A1:A3") = s
End Sub
I'm experimenting with arrays and would like to get better at them, but this has me stumped for the moment. How to get the values to work going down, like they do going across.
The above code is a small sample of data, I am wating to replicate with using over 80K lines and cell by cell is too slow.