# Use Row Number Stored In Cell As Formula Row Reference

Aug 21, 2007

Column A contains numeric values that refer to a row number in column B. I need the value in column B to be outputted in column C. eg. if the value in Row 1 Column A = 55, the value in row 55 column B will be outputted in column C

## Use Row Number In Cell For Reference Formula

Dec 31, 2009

I have five workbooks, one for each week of the month, and in a cell M9 in workbooks 1 to 5 I reference the value of a cell in another workbook called counter.xls.
The cell I need to reference changes each week by one row.

At the start of a month I need to have a macro that will go and find the first empty ...

## Use Cell Number In Formula Row Reference

Oct 5, 2006

In cell A2, I have the number 10. In cell G10 of a different sheet, I have a number I want in a third location. Is there a way to do something in the third cell like =G(A2) so that the value of cell A2 is entered into the formula, referencing the value of G10 in the third cell?

## Formula To Get Closest Number Above And Below Reference Cell?

Jul 18, 2013

i have a group of numbers in A1:C2 and I need a formulas to find the closest number above and below a reference number in cell A3.

so if my numbers were 12.52, 12.02, 12.98, 12.64, 12.64, 10.83 and the reference cell was 12.62 the formulas would return 12.52 as closest below and 12.64 as closest above.

## Drag Down Formula But Cell Reference Number Not Updating?

Jan 27, 2014

I am trying to drag down a formula (see below). the cell reference A6 and E6 is not updating to A7 and E7 etc etc when i drag the formula down using the corner thing. it remains constant at A6 and E6.

im pretty sure the problem comes down to the hashtag and quotation marks that i added, as the formula properly increased the cell references when i drag down without the hashtag and quotations. however, i sort of need it because adding those seems to be the only way i can get the hyperlink to link me over to the other sheet and to the correct cell containing the hyperlink.

part 2 of problem: the hashtag and quotations is currently a workaround method for me. the original intention was for the hyperlink to directly link me to a website, which is referenced from a separate sheet. however, without the hashtag and quotations, i get a "Cannot open specified file" error.

## Sheet Name Stored In Cell - Refer To In Formula

May 31, 2006

Sheet names in Col B
B2 : Sheet1
B3 : Sheet2
Etc

I tried

=INDIRECT(B3,\$J\$58)

at C3 where \$J\$58 is the cell I would like to reference on 50 different sheets

Formula returns #REF!
Col C ref's \$J\$58
Col D ref's \$L\$58
Col E ref's \$N\$58
Col F ref's \$P\$58

So as I copy the 4 formulas down, the ref to the correct sheet should update but the cell ref's should remain

## Stop Formula Changing Cell Reference But Change Sheet Number When Dragged?

Feb 8, 2010

need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?

## Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

## Converting Number Stored As Text

Mar 12, 2012

I have a spreadsheet which has over 30,000 records. One column is a row of numbers stored in a General format. Some of these numbers are however stored as Text and have the following Error Check:

"The number in this cell is stored as Text or preceded by an apostrophe"

As a result, when I try to import this sheet into Access, the numbers are not imported and appear as blank cells in the Table.

Is there a way either manually or programatically, format the column so all cells in that column are stored as a number and do not have the issue of being stored as text?

## Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

## Determine Next Invoice Number Stored In Range

Oct 9, 2007

I am trying to add a number to invoices (invoice numbers) on a UserForm that is used to make and/or modify scheduled service records for a cleaning business. We fill out the schedule well in advance of when the jobs are scheduled to be done, some customers are on a set schedule such as once a week, everyother week or once a month. Some just call in when they need cleaning. Each day has 27 rows set aside for possible customers (most are not used). We make out as many invoices in advance as is possible and fill-in the rest as they call-in. This leaves a lot of gaps between days

I have an auto-advancing formula tha takes a helper column to give me invouce numbers when there is a customer listed in column B and blanks space where there is no customer listed. This does make for volitile invoice numbers.

Formula in row 3 of the invoice coulmn "A": =IF(B3="","",H3)
Formula in row 3 of helper column "H": =IF(B3="",H2,H2+1)

The helper column only advances if there is a customer listed in column B of that row otherwise it repeates the last number. The invoice column only displays this helper number if there is a customer listed in column B of that row. I can't figure out how to get this to work on a UseerForm with vba.

## Reference A Sheet In A Formula By Number, Not By Name

Dec 13, 2008

I have the following formula: =IF(ISBLANK('WP'!D4), "", 'WP'!D4). where WP is the name of the sheet. I need to reference the sheet not by name, but by it's number (for example WP could be my second sheet, but tomorrow the name of my second sheet could change to WT).

## Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

## Cannot Convert Numbers Stored As Text To Number Format

Dec 14, 2009

There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:

"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.

The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.

## Number Stored As Text - Make Column As Numeric Permanently

Nov 29, 2011

I have sent a data sheet template to my client in which they paste their data, based on this data sheet analysis will be done automatically in next sheet "Summary".

Whenever they paste product code, vlookup is not working since the product code contains few are numeric (shows: "Number Stored as Text"). I advised my client to go with "Text to Column" and "Delimited" to change it as numeric. But my client feels this method is tedious one. Is there any alternate way to make that column as numeric permanently?

## Formula For Largest Percentage Related To Reference Number

May 15, 2014

I have data one sheet which is

Date Ref No Type %complete
Monday 1 Erect 75
Monday 2 Erect 50
Tuesday 1 Erect 95
Wednesday 1 Erect 100

On the next tab I am consolidating all the reference numbers and want to capture the latest % complete

I have been trying =Max(if ref no=1 and type = Erect, % complete),1

But when I try it for reference number 2, it just says 100% aswell, when that should be 50%.

## VBA To Insert Formula And Then Fill Down Number Of Rows In Reference Column

Apr 8, 2014

I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:

Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!\$A\$48:\$B\$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!\$A\$2:\$C\$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!\$A\$2:\$D\$10000,4,FALSE)"

[Code] ........

## Copy Formula With Absolute Reference & Increment Row Number Every X Cells

Mar 25, 2009

I am creating a spreadsheet which creates a bulk of data from a front sheet.

The question is:
Is there a way to automatically copy a fixed formula for 100 cells using one fixed cell reference such as \$A\$1 and then automatically after 100 cells replace \$A\$1 with \$A\$2, after another 100 with \$A\$3 and so on?

Explanation with Example:

For example, Sheet1 contains the words "Green Tree" in Cell A1 and Sheet2 will then place "Green Tree" into 100 different sentences such as:

Plant a Green Tree
Grow a Green Tree today

This would be created with the formula ="Grow a "&'Sheet1'!\$A\$1& " today"

After 100 different variations using the formula I want to change that formula to reference cell A2 on Sheet1.

I know if I place "Red Tree" in Cell A2 and use the formula ="Grow a "&'Sheet1'!\$A\$2& " today" I can do this manually using find and replace for the 100 cells, but I want to do this for 100 different variations of Green Tree to create a 10,000 different sentences so I'd need to find and replace 100 times!

## Index Or Match Formula: When A Reference Number Is Used - It Popluates Cells From A List

Jun 6, 2006

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)

## Excel 2007 :: Formula To Return First Cell Reference In Another Cell Formula

Oct 1, 2011

Version: Excel 2007 WinXP

I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.

Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:

Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)

I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.

Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's

Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.

## Correct Syntax To Add Workbook Name That Is Stored In Variable Into Formula?

Feb 24, 2014

I have the following code, which executes just fine:

[Code].....

The problem is that I need to run this again in a different column, and the data is held in another sheet that I have given the variable StepTwo to.

I'm not sure of the syntax to do this. Basically - this is what I need the code to do:

[Code] ....

Understandably this is totally incorrect, but it's the best I can do to explain what I'm actually trying to do....

The two parts of SUMIF are both held in a workbook that I've given the variable "StepTwo" to. Both sets of data are also held on Sheet(1) of that workbook.

## How To Reference A Variable Stored Within Another Variable

Jul 27, 2009

how to achieve what I'll call "parameter driven" code where the "parameters" are variable names.

For example, in the following code... I'm looking for an answer to the question posed...

Sub Test_Sub()
Dim vX, vY, z
vX = 10
vY = "vX"
For z = 1 To 3
'This next line generates a Run-time error... But...
'=====================================
MsgBox z * vY '

## Substitute A Number Into Cell Reference?

Oct 28, 2012

I have the formula in cell A2 =average(A100:A1100). However the range is not always the same so need a formula that will allow me to enter '200' in cell B2 and that will make the formulae in cell A2 calculate =average(A200:A1100). Likewise, if '300' is entered in cell B2 the formula in A2 will calculate =average(A300:A1100).

## Adding Number To Cell Reference Not To Contents

May 28, 2006

I want to create a formula that will refer to the cell I want by adding a number to the cell reference, not to the contents of the cell. For example, lets say that the cell C11 holds a value of 22. I want to add 4 to C11 to get my formula to reference cell C15, I don't want it to add 4 to the contents of C11 which is 22 and would give me a result of 26. I have a large spreadsheet and I can't just fill down the way I have it now and I don't want to have to retype the formula every time.

## Match Function For Cell Reference Row Number

Sep 5, 2007

I am trying to acccess a cell value from a seperate worksheet. The cell reference needs to be generated by a seperate formula. In this example

=DSInfo!C28 // provides the correct result i.e the contents of cell C28 on worsheet DSInfo =MATCH(A4,DSInfo!C1:C35,0) //provides the correct row number - in this case 28

However on trying to combine the two =DSInfo!C&MATCH(A4,DSInfo!C1:C35,0) // provides only a formula error. I'm guessing this is a simple syntax error on my part but after hours of trying various ways I'm still having no luck.

## VLookup (or HLookup) Cell With Reference Rather Than Actual Number

Oct 13, 2011

I am doing a vlookup on a cell range where the value I'm trying to lookup (a date) exists, but it's not an actual number in the cell...it's a reference to another cell with that value (somewhere completely different).

So, I'm trying to vlookup(date(1/1/2011),A1:A12,2,false) to get the B column value.

A B
1/1/2011 #
2/1/2011 #
...
12/1/2011 #

However, the A column is not the actual date. It is a reference to another cell somewhere completely different that has the actual date 1/1/2011.

When I do a vlookup trying to find 1/1/2011, it can't see it there unless I overwrite the reference in A1 (for instance) with the actual date.

Can I do a vlookup and keep my cell references?

## Convert Cell Number Reference To String Address

May 18, 2007

I have something like the following:

If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then
..do code (I know the cell location of interest at this point)
End If

Then I End up needing To Do something Like the following
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("RawData").Range("L1:N1")

�L1:N1� above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn�t do me much good when range wants something like �L1�. I don�t know how to convert that (12,16) to a L16 for example.

## Counting The Number Of Non-empty Cells Beneath A Reference Cell

Aug 18, 2009

I'm trying to count the number of non-empty cells beneath a reference cell and would be most grateful for a few pointers. The code I currently have (that doesn't work) is:

## Converting Times Stored In Date Format To Number Format?

Mar 31, 2014

One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.

## Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.