Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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 re-number itself, if someone entered a new
row.

A typical value in the first column looks like this: 8-5-012-005

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):

Row-1 Tract_ID Parcel_ID
Row-2 7-5-065-105 01245787
Row-3 7-5-112-005 01245787
Row-4 8-5-012-005 01245787
Row-5 8-6-030-125 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:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-112-005 0126A560
Row-4 3 8-5-012-005 01005147
Row-5 4 8-6-030-125 01000541

Then, let's say the user wants to enter a new value like say, 7-5-105-021.
That value would need to go between Row-1 and Row-2, 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:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-105-021 00547419
Row-4 3 7-5-112-005 5126A560
Row-5 5 8-5-012-005 00005147
Row-6 5 8-6-030-125 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
Match Index: The Value Yes To Return The Row Number
in one column I am looking up the value Yes to return the row number.

=MATCH("Yes",C:C,0) in this case it returns a 2

I want to use this row number in a sum...

i.e. =sum(b2:b&x) where x is the row number from the formula above, but it just errors out.

View Replies!   View Related
Find Index Number Is The Color Of Red
I am editing code that changes a cell's forecolor and I can't seem to find the number for red. What number is red


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
Index/Match & Sum: Returns The First Number
I have the following formula that returns a number, however it returns the first number it comes to when the criteria is met,

View Replies!   View Related
Select Column By Index Number
How can you select a column by it's index? For example, if I do the following, I get an error.

Dim Counter As Integer

Counter = 157

Columns(, Counter).Select

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
Autofill; Copy Down It Doesn’t Automatically Update The Cell References Because It Want To Update Them By Column Number Instead Of Row Number
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?

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 Numbers In Each New Cell
in a1 I have a number 1, a2 is a number 2. I want to keep on counting down to number 7691. Just do not know how to do this.

View Replies!   View Related
Index Or Match Formula: When A Reference Number Is Used - It Popluates Cells From A List
I am looking for a formula or something - that when a reference number is used - it popluates cells from a list. Attached is a sample spreadsheet - 2 worksheets are being used - 1 is Purchase List and the 2nd is Fax Commitment. When reference no is filled in on the Fax Commitment sheet and it = the same reference no as on the Purchase List - I need it to populate the appropriate fields (in this case I have colour coded)

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
Index And Match Formula: Return The Correct Serial Number Based On Both The Matching
I'm trying to create a formula in cell f13 of my attached spreadsheet "Sample 1" that will search the 2nd attached spreadsheet "Sample 2" and return the correct serial number based on both the matching PO # (located in cell E10 on Sample Sheet 1 and in Column 5 on Sample Sheet 2) and Product # (cell A13 on my Sample Sheet 1). My current formula is not returning the correct result and I'm not sure why.

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
Chart Based On Changing/Increasing Data
I'm facing a charting problem and i can not find any solution with a search here, anyway this is the problem: When I choose a week I would like to see the results of the 5 previous week also.

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,80-80

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 price-list. 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 Drop-Down 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)^(Term-ROW(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
VBA To Automatically Generate The Next Number
I have build a form in Excel. I would like to make one field (ID) to automatically generate the next number.

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 auto-adjust 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
Formula To Automatically Add New ID Number To Code
I would like to add something (formula) to the following code to automatically add a new ID number with each new entry. Some of the entries will have the same name but each time a new entry is added I want the ID number to increase by 1. Assume that the first entry is 0001.

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
Changing Sheet Number Automatically While Dragging
I have worksheets named 1 to 100. In a different worksheet "Summary" I have a summary of data of those worksheets. I want to change the worksheet number automatically in a row by dragging.
For example, in worksheet "Summary" ....

View Replies!   View Related
Automatically Increment Column Number In Vlookup
I have the following formula. How can I change it so thst when copy/drag the column number automatically increments by 1

IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))

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 =E4-D4+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 Highlight Cells That Have Text Or Number Below X
How can I use conditional formating to produce the following:

if cells are between 0 and 10 OR they have text(or an error) = red
if cells >10<25 = orange
if cells > 25 = green

View Replies!   View Related
Automatically Convert 4 Digit Number To Time Format
Is there any way that I can have a column of cells change the entered 4 digit to a time format? E.g. when somebody enters 1212 in a cell it will automatically convert the entered numbers to 12:12

View Replies!   View Related
Move A Number From A Group Automatically And Post In A Different Colume
I would like to move a number from a group, automatically and post in a different colume. Like remove the 4 from 439 to a different colume.


View Replies!   View Related
Index Matching: Formula To Give "name" Associated With The Largest Number In A List
INDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))

I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.

View Replies!   View Related
SUM MAXIMUM Or Index/Max: Count Number Based On The Maximum Time??
I was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).

=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)

Would a Index/Match/MAX function be more efficient?

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
Copyright © 2005-08 www.BigResource.com, All rights reserved