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 created a couple of conditional formatting rules on the first row that fill the whole row of the table based on whether there a number in one cell or another. (Fill the whole row green if cell E3 has a number in it, or fill the whole row red if Cell F3 has a number in it).
I want to apply this to each row of the sheet but when I auto fill it down, the conditional formats fill but the reference cells stay as E3 and F3. This is causing the entire table to be filled instead of just the individual rows.
I don't want to have to put in conditional formatting rules manually for each row. Is there a way to auto fill down with the reference cells changing to suit each row like a normal formula would?
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 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.
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???
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.
I'm trying to enter some in a website and submit it, I'm using this code:
Code:
Sub LoginToSite2()
Dim IE As Object
Dim IEdoc As Object
Dim Password As Object
[Code]....
However I can't get to work for this website, I'm getting Run-time error "91" Object variable or With block variable not set I can only post the source code of the website (below). Also it contains frames, maybe that's part of the problem.
I'm simply inputting zeroes and ones down Column A. Basically, I either put a "0" or a "1" in A1, then A2, then A3, etc. However, I want to create a Macro that allows me to simply type in a string of 1's and 0's and it automatically takes each 1 and 0 and fills it in down the column. This website has the basic idea (Automatically Moving from Cell to Cell when Entering Data (Microsoft Excel)) but I need only one number per cell and for it to move down the column, not across the row. Here's what I have so far:
Sub SAMPLE()
Dim str As String
Dim x As Integer
Dim y As Integer
str = InputBox("Enter string")
y = 0
For x = 1 To Len(str) Step 4
ActiveCell.Offset(y, 0) = "'" & Mid(str, x, 4)
y = y + 1
Next
End Sub
*I can't get it to only break down into one number per cell down Column A. I think the Mid function or something needs to change.
I am creating an excel file for my company. This file contains 18 headers (from Columns A to R), all of which are mandatory to be completed.
This file is used by several other colleagues with the intention to update our records almost on a daily basis, and saved by them daily before being checked by me.
I would like to know if there is a macro to be used in order to prevent them from saving the file if all the fields are not completed. (ie. A5 to R5)
This function should only run right up to the next blank row.
Eventually, I would like the users to complete all cells from A5 to R5.
Should a single cell not be completed, an error message will pop-up.
When the next person accesses the file to enter their data underneath, it will again, not allow them to save until all the mandatory fields are filled out.
I have looked at another very similar link but it does not work and I do not know how to tweak it.
The example given is for when the data is in Column A.
The headers on my file are on Row 1 (Column A to R).
Mandatory Cells and knowing to fill in the next row!
I have blank spaces between data that I wanted to fill up with the same info as a group. the problem is it is not in a fix format.
For example 1
___A_______B________C________D
1_aaaa____data a____data a____data a
2_________data a____data a____data a
3_________data a____data a____Total
4_________data b____data b____data b
5_bbbb____data b____data b____data b
6_________data b____data b____Total
I need to fill A1 & A3 with aaaa and A4 & A6 with bbbb
Is there a way I can do that? the data is seperated only by the "Total"