Incrementing Cell Reference In Column For Data In Row?

Sep 7, 2012

Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example numbers 50,60,70,80 are put in cells A6 to D6 (horizontal)

What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6

View 1 Replies


Incrementing A Cell Reference

Nov 25, 2008

I am having trouble with the following situation:

on the sheet PANEL in cell A2 I want to reference the information on sheet 480 in cell B5.
Then on sheet PANEL in cell A3 I want to reference the information on sheet 480 in cell B54.

'PANEL'A2 needs to equal '480'B5
'PANEL'A3 needs to equal '480'B54
And so on...

I need the reference to skip 49 cells each time so that I can just copy the formula on down the column.

View 6 Replies View Related

Incrementing Reference Numbers

Mar 11, 2009

I'm putting together a document where I'm trying to automatically create a reference number for each entry. The reference number needs to consist of a site ID (found in column A), an underscore, and then an incrementing number dependent on how many entries the reference number appears.

For example, in column A, the first time the site ID: 12345 appears, I'd like the reference number 12345_01 to be created. The second time it appears, the reference number would be 12345_02. And so on.

If possible, as illustrated above, the first nine references need to have a 0 before the number - i.e. 01, 02, 03, etc - when it hits the tenth instance the reference becomes 10, 11, 12, etc.

I've tried to solve this with COUNTIF, but cannot get it down.

View 2 Replies View Related

Create Formula In A Column That Increments Cell Reference From Data In Row

Sep 8, 2012

I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.

Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)

What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6

View 1 Replies View Related

Incrementing A Variable For A Column

May 27, 2006

I have a sheet with all of my data sorted into columns. I am writing a macro to select and copy the value from column A if there is any value in column B on the same row. The output would be sent across consecutive columns.

As an example;if B3 is not blank, copy A3 to I5
if B4 is blank, do nothing
if B5 is not blank, copy A5 to J5
if B6 is not blank, copy A6 to K5
and so on.

I cannot figure out how to increase the output column identifier. Everything else I can get

View 4 Replies View Related

Incrementing Formula By More Than One Column Vertically?

Apr 10, 2013

I have a formula that i'd like to "click and drag" down but while i do i want it to increment through columns



in cell A1 i'd have the formula

VB: =max(c1:c5)

and it will spit out 15, that's great but when i drag the formula down i want cell A2 to give the value 20

i'd like

VB: =max(c1:c5)

to somehow turn into an equivalent

VB: =max(g1:g5)

by only dragging down, not to the side

View 5 Replies View Related

Adding Incrementing Text In Column

Mar 17, 2014

I've got a sheet with 3 columns A, B and C headed: "Surname", "Forename", "Membership No" with then a variable number of rows containing the data. There are no blank cells (the macro already checks for this).

In column D I have "Group Name" which I need to populate with GrpA for the first 250 members, GrpB for the next 250 and so on down until it stops with the first blank cell in column C. At the moment I'm happy for it to cope with up to 2000 members, i.e. 8 groups, though may need to extend it later (no danger of getting past GrpX though!).

View 8 Replies View Related

Reference To Cell Column Letter By Reference To Another Cell Value

Apr 25, 2014

Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.

View 9 Replies View Related

Incrementing Pairs Of Data?

Oct 4, 2013

I need to complete what would seem a fairly simple incrementing task but I'm not getting the results that I'm looking for. I don't know much about macros so would prefer not having to use one but I'm willing to try if it's not too complicated.

I'm using Excel 2010 on a PC. For several projects, I need to increment cells that contain text and numbers. The column contains data in a cell (A1), followed by a blank cell (A2), then cell A3 contains new data followed by a blank in A4, etc. What I need to do is 1) copy the exact data in A1 to A2 and then 2) increment the number used in A1/A2 by 1 in A3, copy that into A4, and the pattern repeats down the column.

Here is an example of what I have in column A:
[blank cell]
[blank cell]
[blank cell]

This is what I want as a result.

Do I need to save the data in the column as text, general, or numbers? Is there some function that would make incrementing the data possible as I've outlined above?

View 8 Replies View Related

Asymmetric Fill (fill Out Column B Referencing Column A, But Only Incrementing By 1 Row In A Every 2 Cells In B)

Feb 1, 2010

Is there some easy way to fill out column B referencing column A, but only incrementing by 1 row in A every 2 cells in B?


Column A:
A1 = 1
A2 = 2
A3 = 3

Column B (I would like to fill this, referencing column A):
B1 = A1
B2 empty
B3 = A2
B4 empty
B5 = A3

View 4 Replies View Related

Incrementing A Value In A Single Cell

Sep 28, 2009

Is there a way to count how many times a value is changed?

Cell A1 can be either “YES” or “NO”. Its' value is controlled by validation…

When Cell A1 is changed from “YES” to “NO” I want to increment the value in B1 by one.

When Cell A1 is changed from “NO” to “YES” I want to leave B1 as it is.

View 6 Replies View Related

Incrementing Cell References

May 3, 2006

Here is what I have at present:

At the beginning of a loop, my cursor is positioned on a row. I want to test two cells on that row.

Depending upon the result of the two cells, I want to make the row a certain color.

After that, I then want to move down to the next row, test the same two cells in THAT row, color accordingly and loop again until I reach the bottom of the spreadsheet.

My problem is this:
If I can use a loop that lists various conditions, along with the corresponding statements that color the row accordingly, how can I when finished increment the cell references so that the conditions change to refer to the cells the next row down?

If a loop is not possible, any suggestions you have for solving this problem would be appreciated. I have about 200 rows so am trying to condense my code; this may be hampering my ability to figure out the solution.

View 9 Replies View Related

Cell Shading With Incrementing Values

Jul 13, 2009

can i make a cell dark green if it is value 1 and very light green if its 10 and all varying shades of green in a linear fashion in between?

View 9 Replies View Related

Stop Date Cell Incrementing On Drag / Fill

Feb 8, 2008

I have a sheet where users enter a date and other details on each row.

The number of rows where data is entered can be upto 20 at any one time.

I wish to allow users to drag the cell date down for the number of rows they wish, without the date incrementing.

View 4 Replies View Related

Reference Every Nth Cell In A Column

Aug 26, 2009

I have a data entry spreadsheet that contains 120 data entry panels each of 10 rows. Each panel is separated by 3 rows (1 of which is hidden). When entering data to the panel the user is required to enter a date in column F of the data entry panel. The number of used rows in each data entry panel can vary from 1 to 10.

The entry of a date in the first Column F cell in each entry panel stamps today's date in Col F in the first row below each data panel (the hidden row) and that date is then linked to a separate summary sheet to show the date of data entry. The code I'm using for the date stamping routine is below, and although it works it is painfully slow and I'm sure very inefficient. I've had to break the code into the three sections as shown below because there seems to be a limit to the number of individual cells I can reference in each of the range statements. (Is there a limit or is the length of the range statement causing some other problem?)

View 4 Replies View Related

Worksheet 'Change' Event: Show Which Rows Of Data Have Been Changed By Incrementing A Revision Attribute

May 13, 2009

I want to show which rows of data have been changed by incrementing a revision attribute. For example, if a user changes the contents of a cell anywhere between rows 2 and 13 and col 1 and 9 then the revision attribute in col 10 would increment from 1 to 2 (for the affected row). If another change affects the same row then the rev attribute would increment to 3, and so forth. I don't care which cell was changed only that something on that row was touched.

I thought the CHANGE event was a dead ringer for triggering some VBA code to control this but, since part of the change event code writes the revision value, this triggers another CHANGE event causing an endless loop until something (??) kicks in and stops it after 220 iterations. Is there a way to inhibit the change event just prior to updating the cell containing the version attribute?

Rather than post the code here I have submitted the workbook that includes the whole setup and code. I should also mention I looked at all the other worksheet events and I do not see any "triggers" that would fire each time a cell content is changed. As a side note, is there a way to step into the code of a change event? F8 does nothing.

View 3 Replies View Related

Average Of A Series Of Data (advanced) Column Reference

Aug 14, 2009

i need to write a formula that spits out the average of a series of numbers when (if):

every time "CAD" appears (column A) it takes the numerical figure of the cell directly to the right of it (column B). the data is arrange vertically, e.g., the range of "CAD"s is all vertical (column A), about 200 entries.

is there a formula/function that takes data from the columns completely adjacent to one another?

View 11 Replies View Related

Multiple Sheets All Reference The Same Column(s) To Match Data?

Mar 7, 2014

I've created 12 identical work sheets for the year, each has 6 columns of data and 87 rows all containing different information (letters, numbers, dates and characters).

I've also set up a "Conditional Format" rule so that any identical information triggers a strikeout (line through the cell entries) of the entire row for both lines of offending duplicating data. To do so, I created a use cell off to the side (in column AT) with the command =CONCATENATE (u41,v41,w41...) which condenses all data into 1 cell.

Afterwards, I went into Conditional Formatting and created the strikeout command with =COUNTIF($A$T$29:$A$T$116,$AT29)>1; where the $AT29 reference simply identifies the cell to the left and then continues down.

This has worked well but I'm stumped. Now I want the reference to extend to the previous month, lets say I'm in February and want to extend the search to Januarys AT29:AT116 review.

simply use a reference in the COUNTIF formula in the Conditional Formatting rule that takes the search through to the January work sheet, then I can simply duplicate the command for the March sheet which will then review all 3.

View 1 Replies View Related

Keep Reference To A Cell When Column Is Deleted

Nov 28, 2007

I have a spreadsheet in excel with scheduling information, each group of data (job) is made up over 2 columns and 6 rows and is in a block (this is so it is easy to view and manouvre)

I have a link in another worksheet that extracts information from the cell if a criteria in the group is met!

At the end of each day I need to delete the columns (B and C, which make up that day) in the schedule.

When I do this all the references in the other worksheet are messed up, even though the next day is moved to columns B and C.

I would like the link to the other worksheet to automaticaly recognise the new values in column B and C as the reference.

View 9 Replies View Related

Layout Cell Reference Column

Jul 8, 2009

I am not sure how this is possible without doing copy pate transpose or individually refereing each cell to source. I would like the heading to be presented as in sheet1 which is sourced from Source sheet but if anychanges are made to source it updates the Sheet1. I have attached an example to illustrate. (Headers are not fixed number)

View 3 Replies View Related

Column Reference To Cell Transfer

Jan 18, 2010

I am trying to get information from 1 sheet to another. What i need to do is take the information from sheet 2 column H2:H26 and put it in sheet 1 cell I4. i already have the vlookup information in the other cells that i need but for what i need this for is different than a vlookup. basically i want to be able to type in a name (from sheet 2) in cell I4 (sheet1) and all the information that i need auto populates for me like i already have. I was able to do this with a drop down menu but that wont work as the information will change weekly,

View 11 Replies View Related

Reference Changing Column On Specified Cell?

Dec 9, 2013

I have 2 Sheets in Excel: Sheet 1 & Sheet 2

Sheet 1 has some basic data (numbers) in a column, say AB1, AB2, AB4, AB15 (specific cells)

Sheet 2 basically references the data in a nicer template, with the basic forumala: ="Sheet 1"!AB1, etc..

Now...Sheet 1 has a new column inserted and new data added weekly, so AC1, AC2, AC4. AC15

How do I ensure that Sheet 2 recognises the new Column Insert (incremental letter) and references it automatically?

View 6 Replies View Related

Function For Reference Of Cell Row/column

Mar 5, 2009

I have a column of numbers, on which i would like to perform subtraction, in a way that the cell in the (i)th row will be subtracted from the one in the (i+x)th row, while x is a parameter that the user can change as he wishes.

My problem is how to refer to the cell address and still use this conditional reference...
If anyone has an idea how to realize this calculation (which should be peanuts in Matlab, but apparently more problematic in Excel),

View 11 Replies View Related

Change Column And Cell Reference Name?

Apr 25, 2014

I have a massive excel sheet with 300,000 rows and 100+ columns. When reading back through my formulas, it can get very confusing. Example: "=(AZ9*5 + 1)+BH9/2 +(AP9*0.75)" I then have to figure out what each column letter is representing. Instead of this, could I rename a column so I can reference the variable name? Example:

Instead of =CONCATENATE(A4, " ", B4)


=CONCATENATE(FirstName4, " ", LastName4)

View 3 Replies View Related

Reference Column To A Single Cell

Jan 11, 2007

I'd like to reference Column A to a single cell in a different sheet.


Returns the value of A2 in the sheet called StoresServiced to another sheet.

What I want to do is, the values from A2 to A102 to show in one cell, separated by commas. So if theres value in A2 (8009) and A3 (8010), I want the active cell (ex. M43) to look like this -> 8009,8010 -- so on and so fort

View 9 Replies View Related

Change Reference Cell From Column To Row

Jul 25, 2008

I am using this formula


It looks for the sheet based on A5
It finds the max number from the sheet in Column D

This is what i would like to do please....

On this found sheet - Column A has dates in this format

I would like to enter a date in a cell (say for example AA5 on main sheet)

Can the formula above be adapted to:

Look for sheet based on A5
Look at date entered in AA5
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
Return value from Column D

View 9 Replies View Related

LOOK For The First Full Cell In Column That Is In Reference To Cell On The Left?

Sep 4, 2013

I have got this working so far:

=LOOKUP(D9,'Financial Snapshot'!$H$5:$I$95)
D9 = 1.01
D10 = 1.02 etc


View 3 Replies View Related

Reference Cell In Macro Using Row & Column Numbers From Cell

Aug 11, 2008

I'm trying to reference a location on a spreadsheet in a macro by using the values in two cells (the idea is to then paste to this location):

D2 is 17 (the row)
B4 is 2 (the column)

therefore the location is B17 (R17C2), but can I get this to work...? The values in these cells can change depending on selections made which is why I want to use the cell references rather than the absolute!

View 3 Replies View Related

Using VLookup (But Reference A Cell As Column Index)

Aug 10, 2012

How can I do the following....if for example I have the following vlookup;

=vlookup(A12, 'sheet 2 $A$2:$ID50$, 3, false

How can I change the column index i.e. the 3, to reference to a cell.

=vlookup(A12, 'sheet 2 $A$2:$ID50$, H1, false

The above example doesn't work but I'm sure something can be done using TEXT or VALUE

View 3 Replies View Related

Stop Formula Column Reference Changing On Insert But Not Row Reference

Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved