Row Index Number Increasing Automatically
I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?
I am attaching a SAMPLE of data. The actual worksheet is much more larger.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automatically Number An Index Column
I am working with a group of individuals that will be passing around an excel spreadsheet to one another, and wanted to come up with a way to have the 1st column act as an index, with the key component requiring that the index column would automatically renumber itself, if someone entered a new row. A typical value in the first column looks like this: 85012005 Which in our case means that there are 4 series of number sets, separated by dashes. So the first set is 8, the second 5, the third 012, and the fourth 005. The available range for the sets are 7 or 8 for the first, 5 through 9 for the second, and 0 through 130, and 0 through 200, respectively. So the user can pick any of these ranges for when they decide to create a new record (row). Here is the way the spreadsheet columns currently look (always sorted by Tract_ID): Row1 Tract_ID Parcel_ID Row2 75065105 01245787 Row3 75112005 01245787 Row4 85012005 01245787 Row5 86030125 01245787 Now, here is the way I'd like to have the spreadsheet columns look with the Index_No (can be either Numeric or Text  depending on your recomendations). The sort order is based on 1st, the index number, then 2nd the Tract_ID: Row1 Index_No Tract_ID Parcel_ID Row2 1 75065105 01245787 Row3 2 75112005 0126A560 Row4 3 85012005 01005147 Row5 4 86030125 01000541 Then, let's say the user wants to enter a new value like say, 75105021. That value would need to go between Row1 and Row2, which, if they just inserted the value in the row of their choice, would screw up the indexing. What I need is a way to ALWAYS create an index (automatically), no matter where they decide to put the value in the spreadsheet, AND it would update all of the other Indexes as well (very important requirement). So the end result would be this: Row1 Index_No Tract_ID Parcel_ID Row2 1 75065105 01245787 Row3 2 75105021 00547419 Row4 3 75112005 5126A560 Row5 5 85012005 00005147 Row6 5 86030125 00001541
View Replies!
View Related
Formula To Incrementally ADD Increasing Number Of Columns
I have a worksheet where over time I add columns that need to be added in a "Total" cell. =M3+W3+AG3+AQ3+BA3+BK3+BU3+CE3+CO3+CY3 Above example, the cell being added is 10 columns after the previous one. Question: What formula can I use to automatically pull the value from every 10th cell starting with M3? Preferably every 10th cell till a value I determine... i.e every 10th cell but only for the first 15 occurrences. (Is this anything to do with the Series command?)
View Replies!
View Related
Increasing A Value In A Cell By Adding A New Number To A Connected Cell?
First I would like to say that I am not English nor very good to explain myself so hope the title is according to the forums rules. Now to my problem I would like to to put in for instance the number 100 in cell A and then the number should appear in cell B. I would like to remove the number in cell A without the number in cell B to dissapear. Then add for instance 50 in cell A to get the number in cell B to add up to 150 and so on. How can I do this? I would like to add that cell B is already connected to a different cell. And I am using Vista.
View Replies!
View Related
VLOOKUP  Automatically Change Column Index
Is there a way to automatically change the column index number in the VLOOKUP formula when copying the formula to columns? For example, when I copy a VLOOKUP formula from column A to column B, the cell references will change, but the column index remains the same. I'd like the column index to be increased by 1.
View Replies!
View Related
Get Sheet Index Number
Is there a straightforward way to find a sheetnumber based on the name of that sheet? Eg if I have 10 sheets and the fifth sheet is called "number5", I want to get a 5 based on an expression that uses the sheetname "number5".
View Replies!
View Related
Change The Index Number When Match
I have to do a vlookup in 12 sheets (named ranges) I use the formula '=vlookup(a1;choose(1;range1;range2; etc);2;0) In this case I have to change the 'choose index_num' every time to match. Is it possible to do the lookup without changing the 'choose index_num'?
View Replies!
View Related
Do Userform Controls Have An Index Number
The form that I have created has a number of controls that are created at runtime. If a user triggers the event that originally created the controls, I want to be able to delete all of the controls before recreating them. Is there a loop that can delete all of the newly created controls? I have 9 controls on the form that need to stay put but everything after those 9 controls I want to delete. Is there something like:
View Replies!
View Related
Vlookup Or Similar With Variable Col Index Number Value
i am looking to create a vlookup but with the ability to easily change the column number index so that i can use different columns. As an example. In a worksheet i have a table with the names of cars in column A starting row 3. Column B to m Row 1 is headed Jan to dec, row 2 same columns is a country name eg UK. Column N to Y row 1 is Jan to Dec again and row 2 of these columns is a diff country say Germany. This repeats for a few more countries. The data within Row 4 for these columns i.e per car is all prices. The table therefore shows the prices of cars per country per month. I then have a seperate worksheet for each country where the cars are again listed in column A and Jan to Dec is in column b to M but the data is hard coded being the number of cars. I would like to use column N to link to 1 of these months hard coded counts dependant on what month i decide to forecast on. The easy way being that if i wanted to use Jan count number i would link the count for that car type to =b4 etc. Is there an easy way to allow me to change the link should i decide i want Feb ? The second question is within each countries worksheet i want to bring into column p the countries related car price for a month i select. It may be that the count number differs from the price i select.
View Replies!
View Related
Find Column Number And Then Use Index Function
I have a database with over 100 products listed across the first row. Column a has a list of over 500 projects. Across each project various columns are marked with a number depending on how many of each products are being used on that project. For Example A B C D E etc. Products > X Y Z AA Proj 1 2 3 Proj 2 1 4 5 Proj 3 2 4 etc. I want to be able to create a report for any given product. The report could look like, Product Z Proj 1 3 Proj 3 2 So I need to lookup the product code across row 1 and determine the column number and then INDEX down that column and find all non blank cells and read the project names from column A. I am familiar with formulas with INDEX and V/H LOOKUP functions. I am not very good with VBA codes.
View Replies!
View Related
Return Array Index Number For Specific Date
I looking for a macro which will help to open a file with current week number in name. The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009. I've made two dimensional array (week number, weekday) with all the dates from that period. I have problem with code to search through the array for given key, return index and write it into variables. For now my code looks like: Sub week() Dim i As Long Dim j As Long Dim k As Long Dim week(51, 6) As String
View Replies!
View Related
Increasing A Cell Value By +1 Every Day
I'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for. I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.
View Replies!
View Related
SUMIF Formula With Sum_Range Based On Column Index Number
Following is a summarized example of my data and what I am trying to accomplish. [Column A] contains a list of account numbers. [Column B] contains current balances, [column C] contains balances from one month ago, [column D] contains balances from two months ago. Within the same spreadsheet I want the ability to type in the account number in one cell and then the column number in another cell. For example, If I type in the account number 1234 and the column number 3, I would get the balance from [column c]...if I typed in the column number 4, I would get the balance from [column D]. My first thought was to use a simple SUMIF formula that would compare the account number I type with the account numbers found in [column A]. The problem is getting a formula that can translate the number 3 to [column C] or the number 4 to [column D]. Note: the actual spreadsheet I am using extends out to column BI. This is simuilar to the Column Index Number used in a VLOOKUP formula.
View Replies!
View Related
Index Function (isolate One Number At A Time And Evaluate Usage)
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing. I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
View Replies!
View Related
Increasing Value Of Cell On Save
I have a worksheet that I need a piece of code for, Cell E1 is an amendment number that increases every time a new one is put out, so they can be tracked. Every time the sheet is saved the value in cell E1 needs to increase by 1.
View Replies!
View Related
Increasing Value Of One Cell Based On Another Incremental Value
I have cell F15 which is blank by default, and cell D14 which pulls a value from another sheet (D14's value is =Info!X20). For D14's properties I have it set to show thirds (Custom Property "# ?/3"). I want to make D14 increase by 1/3 for every increment of 60 that F15 contains. For example, let's say D14 is 12. If F15 is 59, it won't change. If it's 60, D14 will be 12 1/3, and if its 180, it'll be 13. I think I'm close, but just can't quite get it.
View Replies!
View Related
Macro To Print Rows That Keep Increasing?
I'm searching for a macro that will allow me to print rows that are constantly changing in number. Attached is a sample of my workbook. The workbook has worksheets by month. There are data from five sales people on each sheet so each sales person has his own section. I have a print button within each section so he can print only his section of the page. No problem creating that macro. However, at least twice a week the sales people are adding rows or moving a row of data from one month to another month, so the print area is constantly changing.
View Replies!
View Related
Macro To Work On Increasing/Descreasing Data
I have a spreadsheet with a worksheet for each month, so as a new month begins I add a new worksheet using a macro Each worksheet has 5 columns: A = Vendor, B = Date, C = Debits, D = Credits, E = Balance Row 30 contains the totals for columns C, D, & E, cell A30 contains the text Totals The problem I have is occaisionally extra rows are added so the totals may not be in row 30 Is there any way that the macro can be changed so that it looks for the word Total in column A and then reads the contents of the corresponding cell E? to transfer that total to cell E2 on the next worksheet
View Replies!
View Related
Increasing/decreasing; Rate Of Decrease  Spreadsheet
I’m trying to make this: Amount of money: 1000 (changeable) Beginning # of units: 0,1 (changeable) Delta: 200 (changeable) Rate of decrease: 50% (changeable) 0,1 unit should be added for every 200$ increase (1000+200=1200; 1200+200=1400; 1400+500=1900; 1900+256=2156 etc) 0,1 unit should be taken away after decreasing by100$ (because the rate of decrease = 50% (changeable)) After increasing of 1000 + 200, quantity of units should also increase for 0,1, so it should be 0,2 (1200), 0,3(1400), 0,4(1600)….. Amount of money can increase not only by 200, but for any sum. If that sum is (for example) 500, we should increase by 0,2 etc. But when this some decreasing we are using rate of decrease (for example 50%, (changeable): 0,4 units – 1600 0,3 units – 1500 0,2 units – 1400 0,1 unit – 1300
View Replies!
View Related
Listbox Increasing In Size As List Grows
I have a listbox on a worksheet which is linked to a named range, the named range is a results from a database query. Now the problem I have is everytime the query is refreshed the listbox expands in size. Is there anyway to stop the listbox from growing thus 'locking' the size??
View Replies!
View Related
Place In Sequence Increasing The Numbers Of One I Creak In A Cell
I would like to place in sequence increasing the numbers of one I creak in a cell. In the formula I determine it I creak and the corresponding frame number to the placed ones. The problem is that he is accumulated only the greater and not sequencia it. 1803456781101112 1134567810111280 but,8080 Function ordenar2(Myrange As Range, num As Integer) As String Dim Myorder As Double Dim X2 As String Dim n As Integer n = 1 Do While n
View Replies!
View Related
Formula To Decrease A Margin, In Connection With Increasing Basic Value
I'm trying to build a formula to form a pricelist. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B). Example: Basic value is $50, my price is $75 (50% margin) Basic value is $100, my price is $130 (30% margin) Basic value is $150, my price is $172,5 (15% margin) And so on... I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:
View Replies!
View Related
Create DropDown List Using Increasing/Decreasing Range
I have a list of jobs being displayed using the following code. All sheet names that start with AJ, CJ and PJ within the workbook are how the list is created. Sub ListSheets() Dim sht As Worksheet Dim lRow As Long Dim rCell As Range With Sheet1 Set rCell = .Cells(2, 12) End With For Each sht In ActiveWorkbook.Worksheets Select Case UCase(Left(sht.Name, 2)) Case Is = "AJ", "CJ", "PJ" lRow = lRow + 1 rCell(lRow, 1) = sht.Name Case Else End Select Next sht End Sub what i want to do is create a drop list within each job sheet within the workbook that will display the names of the jobs above. Now the thing is I cannot choose the range like normal from data  validation  list as I will not know how many job names will be displayed so I dont know how many cells to include in the range.
View Replies!
View Related
Calculate Future Value Of Monthly Recurring, Annually Increasing Payments
follows in paragraph 5  but first, background! I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate. Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))1),(1+Return_on_investment)^(TermROW(OFFSET($A$1,0,0,Term,1))+1)) (Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year  i.e. 19 increases  answer: $89,632 (rounded)) This formula assumes that the payment is made at the beginning of the period. Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis. Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof  maybe even from the person who supplied it to me (I cannot see who did!)  and then I can start fiddling with it myself if answers do not come. Two previous posts of mine that dealt with somewhat different issues on the same formula are: Determine Present Value From Future Value and [url]
View Replies!
View Related
Changing A Number Automatically
In the following sheet I'm tracking daily numbers against a monthly total. In the cell E4 for example I have a minimum per day number needed which is based on the monthly goal divided by the number of days availble to work. What I would like to be able to do is have the number autoadjust if a letter (i.e. V=Vacation, S=Sick, etc...) is used in place of a number on any given day.
View Replies!
View Related
Add Order Number Automatically
I'm creating an order form in excel. I would like to add a control there where when it's triggered, excel would automatically assign a number that hasn't been used before and save the file using that number. It's like an order number generator. For example, let's assume I'm entering a purchase order in excel. After I'm done doing that, I click on the "SAVE" control button. This will assign the number following the last saved order and save the file in a location I specify in this format: filename POnumber.
View Replies!
View Related
Automatically Move A Number To A Different Cell
i've got a worksheet with a column of unique numbers that i sort from smallest to largest; colA. these numbers are asset #'s of all items that i should have in inventory and all are barcoded. using a scanner i scan the barcode # into colB however these items are on the shelf in random order. as it stands i place my cursor in the first empty cell of colB (B2) the scanned # is entered into B2 and the cursor moves down to B3. What i'd like is to have the randomly scanned number automatically move to the cell in colB that is beside the corresponding # in colA regardless of where the cursor is prior to the scan. e.g.: 16 is in A16, cursor is in B2 waiting for first scan, first randomly scanned # is 16; so 16 is automatically entered into B16 instead of B2.
View Replies!
View Related
Transpose Automatically And Number Groups
1) I need to transpose about 2000 rows of 1 to 7 columns (A to G) of text/letter combinations of 1 to 5 characters into one column, each row under a group name (groups have one to three rows, so there's about 900 groups). The group name is in a row by itself, members are in the 1, 2, or 3 rows right underneath it. 2) Then I need to number the groups. There are no absolute common letter or number combinations within a group, so I can't use the first, mid, or last 2 or 3 characters to signify a common group. I do know how to use the copy and transpose feature, but would like to automate, if possible since there's so many. And automate the numbering task as well. An example of the data is: ABEGF AMMBG AMNBG ANBG1 ANBG2 BEFGL BEFG1 BEFG2 FG1 FG2 GH1 GH2 GH3
View Replies!
View Related
Text String And I Want To Add A Number Automatically
I have in a cell a text string and I want to add a number automatically in the following rows. With other words Product01 Product02 Product03 Etc Product is a constant How will I do this I know I can type the numbers and CONCATENATE it with the text part but I am too lazy to type out all the numbers to 1000
View Replies!
View Related
Automatically Number Table Entries
How do I automatically generate an Entry Number for each entry in a table? I have a sheet to build up a database of information and a Userform for the data to be input through. Each time the user selects to create a new entry with the userform I'd like to be able to automatically provide a number (i.e. Entry 1 of 12) in the Entry Number field which will then be transfered back to the sheet. In addition, if an entry is deleted from the sheet the entry numbers beneath the deleted entry will need to update.
View Replies!
View Related
Automatically Give Number Of Hours Worked
I'm working with a spreadsheet that works out how many hours someone has worked on a given shift. In cell C4 I have the formula =E4D4+IF(D4>E4,1) In cells D4 & D5 I have an entered number in time format (custom hh:mm) The problem with this is that when a time is entered in either D4 or E4 it automatically gives me a number of hours worked (upto midnight) without any input in the other. Aim: Not to show the number of hours worked in cell C4 until hours in both cells D4 & E4 have been entered. NB. Sometimes the start times are 20:00 until 08:00 (night shift) hence answer 10:00.
View Replies!
View Related
Automatically Determine A Dollar Figure Based On The Number Of Points
I have a spreadsheet that I am trying to automatically determine a dollar figure based on the number of points a cell has. A if the first set of a goal, B is their actual goal and C is the actual number of points. I am trying to determine the dollar figure for D. For each point between A and B the person gets .50cents. For every point above B and up to C the person gets 1.50. In the case below if the person doesn't reach B than they would only get the 50 cents from A up to C. A= 2537, B=3252 C=2820 I have attached my spreadsheet and everything is perfect except on one point. I can't figure out how to tell the spreadsheet if it is a negative number to give me zero.
View Replies!
View Related
Select An Item From Drop Down List And Automatically Fill A Number Of Cells
I am kind of new to Excel programming and would like to know if there is a solution to my problem. What I need is to have a dropdown list (I know how to make this) then select an item from the drop down, then based on the name i select, Excel automatically fill up the rest of the cells. For example, from the drop down list I select the company name then Excel automatically type the address, phone number, Fax number and the contact name of the company for me in corresponding cells. Is there a way to do this?
View Replies!
View Related
