How To Check Particular Position In A Cell

Oct 9, 2013

have an assignment to complete and one of the tasks I found impossible to complete. First condition is that it should be completed with a formula (not VBA). So here is the task - I have different cells with IBAN codes in them. The 14th position in the IBAN is used to identify the currency of the account. There are 3 possible numbers - 0, 1 and 2 and each is for different currency. So I have this table where I need to lookup the 14th character in the cell with the IBAN, from there the formula needs not only to check what the number is (0,1 or 2) but also to compare it to another table from where to decide which is the currency. I tried various combinations with FIND/SEARCH but can't find a way to do it.. and I can't find other functions/formulas that can isolate a particular character position in a cell..

View 3 Replies


ADVERTISEMENT

Position Cell In Top Left Position

Jan 7, 2009

Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)

View 3 Replies View Related

VBA Check If A Cell Is Empty - Move 7 Cells Over And Check Again (Loop)

Aug 10, 2012

I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.

Code:
Sub Tester()

Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range

Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)

[Code] ......

View 1 Replies View Related

Checking Cell Values And Position On A Row And Setting Summary Value In Another Cell

Apr 8, 2014

I have a tracking sheet that is used to show where a specific project is within the lifecycle and would like to automatically set a summary value depending on the last data entry within a range of cells in a row and also set it to RAG status depending on the value.....

View 2 Replies View Related

Maintaining Cell Reference According To Its Physical Position From Formula Cell?

Jan 29, 2014

I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.

Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.

I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.

View 3 Replies View Related

VBA To Move Shape From Current Cell Position To Cell 1 Row Up

Nov 11, 2008

I require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3

View 5 Replies View Related

Position Of Third Comma In A Cell

Sep 25, 2008

How to know the position of third comma in the same cell.

View 14 Replies View Related

Find Position Of First Lowercase In Cell?

Jan 9, 2013

I have in the first column something like this:

GLOBAL DESCRIPTION
GLOBAL DESCRIPTION1 Particular description1
GLOBAL DESCRIPTION1 Particular description2
GLOBAL DESCRIPTION2
GLOBAL DESCRIPTION2 Particular description1
GLOBAL DESCRIPTION2 Particular description2

I want to to have in the second column:

GLOBAL DESCRIPTION1
Particular description1
Particular description2
GLOBAL DESCRIPTION2
Particular description1
Particular description2

So far I have this: +IF(EXACT(A1;UPPERCASE(A1));A1;+RIGHT(A1;LEN(A1)-(FIND(X;A1;1)-1)))

X standing for the position of the first lowercase, which I don't know how to get.

View 9 Replies View Related

Measure Cell Position From Right Of Screen

Jul 26, 2009

I once used a Function to Measure the distance of the lefthand side of the active cell to right of the screen.

But now I can find it anywhere.

can anybody remember the function Name?

View 8 Replies View Related

Referring To A Cell For The Row Position On A Formula

Jul 8, 2008

I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.

I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?

View 9 Replies View Related

Add Position Shape To Cell Location

Jan 15, 2008

i need to loop through a column of values and get each value

Dim LastRow2 As Long
Windows("SCFOutput.xlsm").Activate
Sheets("Q2SCNeg").Select
Columns("A:B").Select

ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select
LastRow2 = ActiveCell.Row - 1
For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells
MsgBox ActiveCell.Value
Next c

i did this but it selects the first blank row (row 15) and gives me an empty message box 14 times (which is right, but i need 14 values) the reason i need the loop is because the number ov values will always change

View 2 Replies View Related

Referencing A Cell Based On Position Of Another Cell

Jan 23, 2009

Right now cell B7 is referencing cell D3. However I want this to be dynamic in the sense that suppose I change the Phase 2 (which starts from ww3 (cell D3)) to ww5 (cell F3) then I would like that B7 should also get updated to F3 instead of D3.

Basically the Development phase of "Support" Project starts only when Phase 2 of "Project 1" starts.

ww = Work Week.

In short cell B7 should always be in sync with the ww of Phase 2 of Project 1.

View 3 Replies View Related

Adding Up Cell Position Not Values In Cell

Mar 30, 2009

I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.

The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.

How can I tell excel to grab these values located every 144 rows below and create two new columns?

View 9 Replies View Related

Resize Shape Based On Cell Position?

Oct 13, 2013

I want to resize a shape and set the height to a specific cell. How can I achieve that?

View 7 Replies View Related

Obtaining Varying Position Cell Value From Different Sheet?

Jan 21, 2014

I want to automate the process of updating my football stats and have delved into getting my data from the web. I have managed to import a football league onto my "standings" sheet but I want my "strengths" sheet to read it's values from this new "standings" sheet.

The "standings" sheet is broken down into 2 tables (home & away) one above the other.

The values I want are Home Games, Away Games, Home Goals For, Away Goals For, Home Goals Against And Away Goals Against from both the home table and the away table. The main problem I have is that the cell information I need is likely to change as I update the league tables from the web. So say one week Team A's data is in row 7 the following week it could be in row 8 so i need the data to correlate with the team name cell in my "strengths" sheet.

So at the moment Atromitos Home games (cell B8 on my strengths sheet) needs the value from cell C8 on my "standings" sheet but by next week it could need the value from C9.

Ps I am assuming that I can just just hit the refresh button on the ribbon and the data from the web site will update. Is this correct?

View 4 Replies View Related

Formula For Cell Value Based On Reference Or Position

Jun 23, 2009

HTML Imagine the following table (Ax, etc is the cell position):

A1=John C1=Name D1=Date E1=Version F1=IP
A2=10/2/2008 C2=A1 D2=A2 .... ....
A3=Client version 7.01 C3=A1+5pos D3=A2+5pos
A4=172.24.3.5 C4=A1+10pos D4=A2+10pos

A6=Michael
A7=10/2/2009
A8=Client version 7.03
A9=172.24.3.80

A11=Susan
A12=2/2/2007
A13=Client version 7.02
A14=172.24.3.200

I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;
Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;
C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :

View 4 Replies View Related

How To Get Latest Position Of Certain Character In Excel Cell

Aug 6, 2013

Let's say we have a text string in the excel cell: 12.123.12.1

Is there any way to get the latest dot's position using excel's functions?

View 2 Replies View Related

Calculate Screen Pixel Position Of Cell AA1

Jul 7, 2014

I have an image overlapping a range of cells. I want this code to scan the pixel at the center of the cell, find out what color it is, and set the entire cell to that color, for each cell. Unfortunately with the GetPixel function I have to specify the x and y coordinates in terms of pixels from the top left corner of my monitor. The "CurrentCell.left & CurrentCell.top" method is not giving me the right location (Should start at cell AA1). If you would like, open up the file, go to sheet 2, insert an image, and click "Generate Field from Image". I set the cursor to follow the current position so you can see that it is selecting pixels from the corner of my monitor rather than cell AA1. Is there a way I can calculate the screen position of the top left of cell AA1?

Domino App.xlsm

[Code] .....

View 7 Replies View Related

Current Cell Position For A Non Active Worksheet

Dec 15, 2009

I need the cell address where the cursor is (in a non active worksheet).

View 5 Replies View Related

How To Show Position Of Specific Character In Cell

Mar 9, 2014

How to find and show position number? I try merged two function, but doesn't work

1º=FIND("0";B2)
2º=FIND(CHAR(1);SUBSTITUTE(B2;"0";CHAR(1);2))
Number
Position

[Code].....

View 5 Replies View Related

Find Cell Reference Containing Specific Value From Current Position Up

Jan 29, 2009

I'm having some difficulties in defining my formula. I'm pretty sure that this should be possible.
I have a table containing values and dates. For each change in dates, I should summarize every value for that date. I cannot use the autosummarize function, because I do not want the sum to be beneath the values, rather in a column to the right of the values.

I have created a formula which checks for a change in date: =IF(C15=C16;0;1) where the C column contains the dates. Basically I say that the Index column should contain a 0 if the date is the same as previously registered, 1 if it is a new date.

The index column is used in some conditional formatting rules, so I would like to keep it anyway.

View 7 Replies View Related

Excel 2010 :: How To Position A Picture Relative To Last Cell

Jul 14, 2014

I need to insert a picture on a Excel 2010 worksheet.

The worksheet contains a variable number of columns, and the column width is also variable.

The picture needs to be aligned such that the right edge of the picture is in line with the right side of the last used column.

This is so that when the worksheet is converted to a PDF document the picture (company logo) appears on the top right hand side of the page.

I can find the cell reference for the last column, and I can move the picture around the page. But I'm not sure how to find the position of the last column on the page?

I've got this far:

'copy picture and resize and position
Sheets("Output File").Select
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Sheets("External data sheet").Select
Cells(1, 10).Select ' I can make this be relative to the last column on the WS
ActiveSheet.Paste
Selection.ShapeRange.Height = 50
Selection.ShapeRange.IncrementLeft 0 'put the picture in a position relative to the cell selected

View 5 Replies View Related

Position Insertion Point In Cell While In Edit Mode

Aug 24, 2008

When a user clicks a cell in Column A, VBA will enter the date in the cell (in the form MMDDYY) and will position the insertion pointer at the end of the date. The user can then enter a four digit number following the MMDDYY. In this worksheet, Column A holds a transaction number, which is always in the form MMDDYYXXXX. I am trying to save the user the trouble of entering the MMDDYY, since it will always be today's date.

View 3 Replies View Related

Select Cell Range Relative To Starting Position

Mar 5, 2007

I need to change multiple conditional formats which apply to cell ranges 1 row x 17 columns wide. The specific change I am making is able to be acrried out with the code

With Selection.FormatConditions(1).Font
.ColorIndex = xlAutomatic
End With

However, I dont want the user to have to manually select each 17 cell area. I WANT THE USER TO JUST MANUALLY SELECT THE FIRST OF THE 17 CELL AREA. I want to write a macro that will THEN select a row of cells, 17 cells long, from the MANUALLY SELECTED position as at the moment of running the macro. Unfortunately the cells are not always on the same row in each sheet. On one sheet they may be on row 5, on another row 8 and on another, row 15 etc. I was wanting to be able to select a 17 cell range 'relative' to where the cursor is. How do I write the code to select the relative cell range? Is there an answer in an R[1]C[1]:R[1]C[17] sort of code?

View 7 Replies View Related

Change Cell Comment Default Show Position

Feb 3, 2008

It seems that you can change position when you editing comment and its remembered, but when its showing its alway's on default position.

Is there way to change position of displayed (when you mouse over cell and coment pops up) comment position?

View 3 Replies View Related

Find Column A Cell Value If It Exists For Current Cursor Row Position

Dec 1, 2012

trying to find the value of column A (date field) if it exists on the row that the cursor is presently on. I'm trying to check for the value before performing the next function to make sure the user is on a valid row. Cursor could be on any column A to F. Columns are always 6 but the rows can vary.

View 7 Replies View Related

Function That Returns The Position (numberical Order) Of A Cell Within An Array

Apr 25, 2007

Is there a function that returns the position (numberical order) of a cell within an array?

View 9 Replies View Related

Return Row Heading By Column Header And Specific Texted Cell Row Position

Jul 11, 2014

I have a data set, in which Sheet 1 have a matrix of high level showing Engineer, Program and commodity relationship.

I have to create separate sheet for each program on sheet 1, each program sheet should have Engineer name and commodity name from sheet 1.

Multiple commodities and multiple engineers should be separated by definite structure.

find worksheet with input and output (manual) as expected.

View 2 Replies View Related

Add A Textbox At The Current Position (selected Cell) With A Set Size, Fill Color, And Border Color

Nov 2, 2008

I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:

View 2 Replies View Related

Excel 2003 :: Take Specific Text From Cell And Copy It In New One (Text And / Or Words Position Varies)

Jan 17, 2013

I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.

Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)

Useful information are included !!!

I'll give an example of a record...

Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "

Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...

Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:

Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function

Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved