I have 1 row of user entry cells (A1:Z1). To keep it simple, let’s say A1 is always 0 and Z1 is always 100.
The user can enter any positive number in any of the cells. For Case 1, let’s say ‘20’ in M1.
I want a set of formulas in A2:Z2 that fills in the values with straightlined values from 0 to 20 from A2:L2 and 20 to 100 in N2:Z2.
For Case 2, the user enters ‘10’ in M1 and ‘60’ in T1 and the formulas in A2:Z2 fill in the appropriate values that connect the empty cells (i.e., 0 to 10, then 10 to 60, then 60 to 100).
Basically, a user enters 1 or more numbers in 1 row, and the second row fills in all the non-entered cells with #s that are straightlined, thus ‘connecting the dots’.
I think a bunch of nested IF statements might work, but it’ll be hairy and nested IF statements are a resource hog. Any better ideas? I’m think maybe some INDEX and MATCH functions.
I am planning on creating a map of a badminton court. I am hoping to record each hit of the shuttlecock with a dot on the map. Of course I want this to be inputted by data rather than manually inserting a circle shape and placing it in the correct location each time.
Cells E39:E53 are conditionally formatted to fill red if cell $AB$13=the cell 3 to its left .. eg: cell E39 would fill red if cell B39 (the cell 3 to the left of E39) is equal to cell $AB$13
Only one cell in the range will meet the condition at any time.
I also have the same range conditionally formatted to fill black if cell $AF$13=the cell 3 to its left .. eg: cell E39 would fill black if cell B39 (the cell 3 to the left of E39) is equal to cell $AF$13
Ranges F39:F53, and G39:G53, H39:H53 etc etc etc (all the way up to X39:X53) are all conditionally formatted the same way.
What I would like is for the cell that fills red in the first range to have a line connecting it to the cell that fills red in the second range, etc etc etc.
And the same for the cells that fill black.
Creating a chart from my data is not an option for other reasons, so I'd like to explore this possibility.
I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?
I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.
I've tried using RIGHT, LEFT, MID formulas unsuccessfully
I have 14 ranges like this on the same spreadsheet and would like to print all of them based on the result in the target cells for the range of each to print. I currently have a macro button to print each range as you can see. Is it possible to connect these? What I have tried is cutting the End Sub and then the Dim r As Long but get errors.
I need to connect my two sheets using the value in a column...
Is it possible in Excel?
Actually in one sheet i have only the empno number and his performance report.
(in tabular form)
but in other sheet i have full information about that employee, So I wanna click on any empno and jump to other sheet to see details about that employee...
I cannot use hyper link as i have 50,000 employees and it will be very tedious to hyperlink all of them one by one...
I have a some problem with connecting between VBA Excel and web site.
I wrote a macro to insert the data from excel but there is one problem with displaying the correct view.
I use this site to get the price of the bonds: [url]
it is the site of russian exchange
First of all I write the ISIN code, for example RU000A0E6X12, in the "Quote Search" field and then press Enter for getting the data. After it I can choose the History information or Online from the last field in the left margin. I want to chose "History" and after it the dates from and till are appearing automatically. The automatization of it is my task. And I have only one problem in the end.
I have a data and it has been plotted into a graph. Here's what i have: I have 2 points, point A and point B with caps, 1.00 and 2.00 respectively. So now i need to draw 3 parallel lines, i.e from the caps and the main point itself. How do i do that? Manually will be of course selecting a line from the shapes. Any suggestions?
I need to connect to an Oracle 10g database using vba. Google has loads of snipets of code but I don't seem to be able to get it to quite work correctly.
This is what I have:
Code: Sub ADOExcelSQLServer() Dim Cn As ADODB.Connection Dim Server_Name As String Dim Database_Name As String Dim User_ID As String Dim Password As String Dim SQLStr As String
[code]....
When the connection attempts to open (red code) I get the following error:
I would like to do a sort of lookup function, but am having trouble.
What I would like to do is:
In one column, list all financial years (ie 2006-07, 2007-08 etc - listed in cells A2 - A10). In a particular cell, I will have a date, lets say cell B2. Then in cell B3, I would like to know which financial year that this date in B2 fits in. So for example, if the date was 10/10/2007, it would return the following "2007-08".
I want to make simple Combobox with two dropdown lists throwing me back cells values, but I never made that so I've got a little difficulties.
The sample combobox should look like this:
Clipboard01.png
For the left Dropdown list there's defined name of the range "ITEM1", and for the second - range named "ITEM2".
After user will choose something I want to have numbers of the items selected from the lists in cells "A1" and "A2", and run a macro after clicking OK. How can I do this?
I have a table and I need it to filter on the basis of a value entered in a different cell - but i want it to show all rows greater than and equal to that specific value.
Sub Testmacro() filtercriteria = Range("H3").Value Sheets("Report 1").ListObjects("Table1").Range.AutoFilter _ Field:=4, Criteria1:=">=filtercriteria" End Sub
Due to one of our business requirement I have to fetch so many users Alias name through Outlook.
I am following below steps to achieve that Open new mail, giving user's name in to list, press ctrl+k, then right click properties. In properties I use to find alias name of user. I want to automate this process through excel sheet. I want to provide names in one column as Input to excel sheet and want to fetch alias name in next column of excel sheet.
I have an access database: 2007.mdb I have an access table: All Data - Pre07
I have an excel worksheet which connects to 2007.mdb and selects data from table All Data - Pre07 using a 'user defined' filter via an input box. This filter sets the group which is one of the fields on the All Data - Pre07 table.
The code which performs the above function is shown below.
I wondered whether it was possible to perform a Count function in vba (rather than a Select Function) which will just count the data in access and then return the count rather than importing all the data.
For example, I would like to count the number of rows in the Access table - All Data - Pre07 which have a group = 'AA' and then return the count to WorkSheet 'AA'.
Is it also possible to perform multiple count functions in one connection or do I need to reconnect (i.e. write the code again).
Application.ScreenUpdating = False Dim mygroup As String Dim datinput As String mygroup = InputBox("Enter Group", "Please Enter", "AA") datinput = "'" & mygroup & "'"
I have a list with different categories and several characteristics per category. My goal is to identify the 10 strongest growing characteristics (I guess by sorting them in descending order) and then show them in this order, but organized in the respective categories. e.g.: if "Psychographic">"Demographic" and "geographic" and "behavioralistic", then put "Psychographic, including its subordinated characteristics that are included in the top 10, first. Follow this order until all Top10 members are included.
I am using a spreadsheet for a weekly football league. I would like "Sheet 1" to have the picks for the current week with the point total at the bottom for each team (I have that set up), but on a different "Sheet" I would like to keep a running total for each team. I know how to creat a formula for the total points, but how do I get that formula to attach to the corresponding column on the same row.
EX: A B "Team Name" "Formula for total points"
I would like A and B to stay connected when I auto arrange them in numerical order.
I am running Excel 2007 and connecting to an Oracle DB.
I have created a VBA macro to connect to an oracle DB and retrieve data then place it in a table. The code works, but it uses DSN entries which are specific to my computer. I want to be able to distribute this Excel spreadsheet to others in my company and have them be able to click one button and update the data. I figure the most logical way is to connect to the DB using an IP address that should work for anyone on the intranet.
How do I modify the connection info below to have it connect via IP?
I tried "Data Source = 10.1.1.10orcl" and "Data Source = 10.1.1.10", both of which VBA dislikes.
Here is what I have now:
Code: Sub Create() Dim Servername As Range Set Servername = ActiveWorkbook.Sheets("Summary").Range("B16")
Set StartDate = ActiveWorkbook.Sheets("Summary").Range("B3")
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..
When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..
So I have an excel workbook that is connected to an Access database I use. When the workbook opens, it refreshes the data from a couple queries in Access. However, every time it refreshes the data, it modifies several named ranges pulling from areas of those tables.
Example: $A$2:$A$50000 becomes $A$2:$A$50002 if there are two new lines entered into the table.
Because of this, some of the lookups and arrays I use are mismatching and giving errors. How to prevent the ranges from updating when the table refreshes?
I have multiple data tables in one sheet...and each one has a chart...in that sheet. To better allow the viewer to coordinate quickly which data table corresponds to which cart...i want a line between them. Excel already has the draw line function, but if you move the chart around, the line doesn't move.
