Refer To Cell Without Offset
Aug 31, 2012
I have a selection that I am going through with a 'for each' and then performing actions if the cells meet a certain criteria. I have been referencing nearby cells with the offset function, but now my sheet is too big and I need to change the code often and the offset function is difficult, is there anyway to refer to cells without the offset function, preferably by column letter
PHP Code:
For Each Cel in SelectionIf Cel = x Thenvalue1 = Cel.Offset(0,39).Valuevalue2 = Cel.Offset(0,5).ValueEnd IfNext CelÂ
View 9 Replies
ADVERTISEMENT
May 31, 2014
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
View 3 Replies
View Related
Dec 19, 2012
I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.
View 2 Replies
View Related
Dec 31, 2013
In conditional formatting, which formula do I use to refer to another cell? Trying
=(ISBLANK(K1))
without luck.
View 9 Replies
View Related
Jun 6, 2003
How can I use (with or without macro) cell text to refer to different worksheets inside a formula. For example I've formula =INDEX(Mary!B9:E17...) but I wanted to write that 'Mary' or whatever sheet name to a cell in one sheet and sheet refenrence in this index-formula would change accordingly.
View 5 Replies
View Related
Oct 4, 2011
I want to know all times this one presents the number 3 in the last 10 cells of the A column
For a static range I use this function
= COUNTIF(OFFSET(A16;-10;0;COUNT(A:A));3)
If I add a new cell which is the function for a dynamic range?
I can refer to the cell with this function = ADDRESS(MATCH(300;A:A);1)
but not as integrate it.
View 1 Replies
View Related
Feb 18, 2012
I have a cell containing a 'bullet' symbol (•) and a cell containing text.
If I make the bullet red and the text is black is there a way I can combine them in the same cell and the bullet remains red and the text black?
Essentially the situation Im in is I have a load of text I need to add coloured bullets to and I dont want to individually paste in and colour format all the bullet points because it would be too time consuming.
View 1 Replies
View Related
Feb 8, 2013
Basically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex
View 2 Replies
View Related
Aug 31, 2007
macro which can paste the value from A1, to another B1 and when A1 is edited again, pasted it in B2. This will probably go on and on until B30. I have tried the if else then in Macro, and even the if elseif, but both don't go futher than B2. E.G A1=1 B1=1 A1=4 B2=4 A1=3 then nothing happens to B3. If there is a way to make this work, it will be great. The way i am tryin to get it is to have =if(B1="","",1) for each cell from C1:30 and with C31 the formula "=count(C1:C30)+1". Then i formated C31 to a custom and make it so that before numbers, there is an B. This means that if B1,2,3 and 4 and numbers in them, then C31 will read B5. That will be the cell i want my next record to go. I would prefer it if there is just a code that i can enter that will go to the next cell when it knows the current cell has a digit in it but if i have to, the count way will do,
View 9 Replies
View Related
Jun 28, 2007
is it possible to refer to a cell by a variable cell number? For example suppose i want to refer to a cell on column B, by a value which is in cell C1, so the if C1 has the value 7, i would refer to cell B7, abd if it has the value 87 i would refer to cell B87.
View 2 Replies
View Related
Aug 10, 2006
I use macros to print pages, depending on the number of entries I have. If I have 1500 entries, I have to have 1500 If statements. Is there a way to write VBA in a macro to where I can refer to a cell and use the value of that cell to print the range.
View 5 Replies
View Related
Feb 17, 2009
I want to write a formula that inserts a column and then does a vlookup in that new column using the cell just to the left of it as the first variable. The problem is, this could be in any random column so I can't do a static reference to $A2 as an example. So, my question is...
Instead of using =vlookup($A2,Sheet1A1:B50,2,false)
How do I replace the $A2 part with some code that says, "the cell to the left of here..."
View 7 Replies
View Related
Mar 4, 2009
I need a list in my form, simple "Name/Number" list (only two options) but i don't want it to refer to any cell in the worksheet.
I want to input a text in a textbox and with the selection in the list above i want to have multiple choices at how to approach the text (if i input a name i want it to be different than if i input a number). All the info i came up on the web refers to lists made upon a range of cells.
How can i make a list without involving ranges of cells?
View 6 Replies
View Related
Oct 24, 2011
I'm using this macro to import txt files into excel.
Code:
Sub Bring_Articles_Into_The_File()
Dim sPath As String
Dim iRow As Long
Dim strString As String
[Code]....
Id like to replace the folder URL string ("C:Articles") and refer it to the cell "E5" instead. (So I don't have to open the code and change the folder every time)
View 3 Replies
View Related
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
View 2 Replies
View Related
Dec 8, 2012
I have 2 excel files A & B. In cell A1 of file B, I use an index formula to refer to a row in file A. "INDEX('[A.xlsb]A'!$10:$10,1,2)" is the formula used (referring row 10 in file A).
If I update the row number in another cell of file B, is it possible for this formula to refer to that cell to get the row number from file A?
View 1 Replies
View Related
Jan 20, 2012
Is it possible to refer a floating text to a specific cell in a worksheet?
View 3 Replies
View Related
Jan 29, 2010
At the moment, I have a project where everyone is putting work data on seperate workbooks. Each months work is placed onto another worksheet within that workbook.
The supervisor has his own workbook, that grabs data from each of the workers books. Getting the data is easy enough, however because the supervisor has barely any knowledge of computers, I would like to make it as easy to set up for future months.
Right now, data is collected in each cell using the following formula:-
='[otherusersworkbook.xls]December 2009'!$C$620
Is there a way of getting that phrase to substitute the part that says "December 2009" with whatever is put into column A on the same row?
View 3 Replies
View Related
Oct 31, 2008
I have the following codes in which I am to refer a file and folder to do some further processes.
myfilename = "C:Documents and settingsacsMy DocumentsEntrymyfile1.xls"
folderPath = "C:Documents and settingsacsMy DocumentsEntry"
I want to enter these two paths in a sheet in my excel report file (For example, I am running the report from Report1.xls, in which there is a sheet name "Filepaths". In this,
in B5, I would like to enter the Filepath (B5 named as "FILEPATH")
in B6, I would like to enter the Folderpath (B6 named as "FOLDERPATH")
So that, if I am copying the folder to another area, I no need to change the code every time in the VBA editor. I can do changes in these cells and it will be taken as the path to run the code further.
View 2 Replies
View Related
Jul 15, 2009
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
View 3 Replies
View Related
Oct 26, 2008
I have attached the .xls file.
The basic setup is as follows:
(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)
(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.
For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc
As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.
(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.
NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.
OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.
I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.
HOWEVER, what i WANT is the output as shown in columns Y-AF.
So as you will notice, there are 2 problems:
(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.
How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?
(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.
Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.
View 7 Replies
View Related
Jul 2, 2009
I have data similar as shown in the following:
A1A11
B2AA1122
C3B22
D4C33
D44
The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this
A111
AA1122
B222
C333
D444
the third column always will have at least the same letters as the first column, but new letters/entries can occur.
View 3 Replies
View Related
Nov 1, 2006
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
View 9 Replies
View Related
Mar 4, 2009
I am trying to copy a value to a ref contained within another cell.
BF3 is where the formula is to copy
BJ1 contains the cell ref of "H6"
BI10 is the next cell to select
I think the problem is activecell = Mid(BJ1,1,5) which is the cell to which i want to paste the value. BJ1 contains the ref in text format of "H6" I am therefore trying to copy the value in BF3 to H6
View 4 Replies
View Related
Jul 17, 2009
Sheet1 is a list of names, then a few columns of numbers.
Sheet2 contains calculations based of the numbers in sheet1.
Each sheet from 2 on is names after the names in sheet1 column A...
A large cell at the top of each sheet contains the formula to display the sheet name:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
So what I want to do now is...
in cell, look for the cell in sheet1, and display the value in the cell offset by X so i can bring the numbers from sheet1 over.
In plain english the cell would say like...
"look in sheet1 for "Name" then display the cell next to it"
I know to look in the next sheet its "=Base!B2" if i wanted b2 on sheet base.
I know to use "=OFFSET(Base!B2, , 1, , )" too look at B2 and go right 1 column.
But the issue is i dont want Base!B2.. i want whatever cell in Base column A that has the text that A1 has.
View 2 Replies
View Related
May 4, 2009
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
View 7 Replies
View Related
Jan 29, 2010
For Each C In Sheets("data").Range("A2", Range("A2").End(xlDown))
With C
If IsDate(.Value) Then
.Offset(0, 3).Value = Format(.Value, "yyyymmdd")
Else
.Offset(0, 3).Value = Right(.text, 4)
End If
End With
Next C
which is supposed to read the cell offset 3 to the right, and then set to either YYYYMMDD or YYYY depenig on the value in the cell. I'm not sure how to specify the first part of this line - .Offset(0, 3).Value = Format(.Value, "yyyymmdd") - it keeps reading the cell in column A, rather than the offset cel.
View 3 Replies
View Related
May 21, 2008
how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!
the macro will look at the value I entered in cell A1, then find all instances of it in Column A
in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)
View 7 Replies
View Related
Feb 13, 2014
I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double
Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2)
Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
View 1 Replies
View Related
Dec 24, 2013
I have a formula in cel B1 : =SUM(A2:A100) / A1
I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.
So I would need something like (literally):
=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)
View 3 Replies
View Related
