look one cell below

Does anyone know how reference one cell below another cell.  Example  In
cell B10 I want to return the value in the cell below cell A5, but I
can't type A6 directly.  I want to type in something like =A(5+1) but
excel doesn't take that.  Any suggestions?  Thanks!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
jpx.uxo8b (1)
10/7/2003 2:34:36 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
528 Views

Similar Articles

[PageSpeed] 51

Hi, try 

=OFFSET(A5,1,0)

Gromit



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/7/2003 3:25:29 PM
"jpx" <jpx.uxo8b@excelforum-nospam.com> wrote in message
news:jpx.uxo8b@excelforum-nospam.com...
> Does anyone know how reference one cell below another cell.  Example  In
> cell B10 I want to return the value in the cell below cell A5, but I
> can't type A6 directly.  I want to type in something like =A(5+1) but
> excel doesn't take that.  Any suggestions?  Thanks!

=OFFSET(A5,1,0)


0
Paul
10/7/2003 3:25:58 PM
Possibilities:

=OFFSET(A5,1,0)

=INDIRECT("A"&5+1)

-- 
Jim Rech
Excel MVP


0
jrrech (1932)
10/7/2003 3:28:15 PM
jpx

Try using
=INDIRECT("A"&5+1)

Andy
"jpx" <jpx.uxo8b@excelforum-nospam.com> wrote in message
news:jpx.uxo8b@excelforum-nospam.com...
> Does anyone know how reference one cell below another cell.  Example  In
> cell B10 I want to return the value in the cell below cell A5, but I
> can't type A6 directly.  I want to type in something like =A(5+1) but
> excel doesn't take that.  Any suggestions?  Thanks!
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
andy2763 (218)
10/7/2003 3:29:52 PM
DIM ROW,COLUMN AS INTEGER
THE_CELL_I_WANT = cells(ROW+1,COLUMN)         'this should be 1 down for the
reference cell

regards Julian Brotherton.



"jpx" <jpx.uxo8b@excelforum-nospam.com> wrote in message
news:jpx.uxo8b@excelforum-nospam.com...
> Does anyone know how reference one cell below another cell.  Example  In
> cell B10 I want to return the value in the cell below cell A5, but I
> can't type A6 directly.  I want to type in something like =A(5+1) but
> excel doesn't take that.  Any suggestions?  Thanks!
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
10/7/2003 3:32:44 PM
Create a range name in a cell (any cell) called Down1 (or 
whatever you like). Then make the range name relative, 
you can then use that range name anywhere & any number of 
times simultaneously anywhere on THAT sheet (ONLY). 
Here's how ;
select cell A1, create a range name in the Name 
box "Down1". Excel will create the range using an 
absolute address. Now go to Insert>Name>Define and remove 
the dollar sign from the address of the range called 
Down1, this makes it a relative range name & allows 
multiple use.
>-----Original Message-----
>Does anyone know how reference one cell below another 
cell.  Example  In
>cell B10 I want to return the value in the cell below 
cell A5, but I
>can't type A6 directly.  I want to type in something 
like =A(5+1) but
>excel doesn't take that.  Any suggestions?  Thanks!
>
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~ View and post usenet messages directly from 
http://www.ExcelForum.com/
>
>.
>
0
10/8/2003 12:25:16 PM
Reply:

Similar Artilces:

Highlighted cell
I have a highlighted cell that gets bigger and smaller as I move the mouse and I can't get rid of it. It has the flashing edges like I copied it. What's going on? Thank you! Sounds like you're accidentally in extended mode (but there's no flashing edges) Hit F8 to toggle it off -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Reen" wrote: > I have a highlighted cell that gets bigger and smaller as I move the mouse > and I can't get rid of it. It has the flashing edges like I copied it...

Worksheet Cell Reference
I have a reference to a cell in another worksheet, same workbook. When the value in the referenced cell is blank it returns a "0" value. However, I want the returned value to simply leave as a blank cell. Any advice? David Hi David Something like this : =IF(Sheet1!A1="","",Sheet1!A1) HTH John "DavidS" <DavidS@discussions.microsoft.com> wrote in message news:71737C7E-5095-4E27-A70C-82CE3845C368@microsoft.com... >I have a reference to a cell in another worksheet, same workbook. When the > value in the referenced cell is blank it...

How do I perfom a character count in a single cell in Excel?
I need to peform a character count for certain cells in Excel. I found a formula for counting words within a cell but not the number of specific characters. If you count spaces as a character, you can use directly the function LEN. If you don't want the spaces to count, use something like: =LEN(SUBSTITUTE(A1," ","")) Hope this helps, Miguel. "RonNette72791" wrote: > I need to peform a character count for certain cells in Excel. I found a > formula for counting words within a cell but not the number of specific > characters. ...

Format cell will not appear
Hello, I have a budget spreadsheet in E2k that I need to update. I can change al the numbers and formlas but if I select Format Cells the dialog box will not appear. I cannot find a single cell anyware in that workbook that works. The budget sheet has some grey area to the righ. I tweaked it last year and I can't remember what I did. Please help Tim Tim, Maybe the sheet is protected. Tools, Protection. If you see Unprotect sheet" it was. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Tim Marciniak" <t...

Calculating specifc byte positions in cells
Hi, Here is what I am trying to do: I have a spreadsheet that keeps track of employees vacation and sick time. The spreadsheet is formatted to column A is the employee name, columns B - AB are days of the month. I would like to have the manager be able to enter in the time off as either S8 or V8 (8 representing the number of hours off the employee had S = sick V = vacation). I would like Column AC to then calcuate the total sick hours and AD to calcuate the total of vacation hours. I played around with a few formulas but could not figure out a way to do it. Is there a way to have this d...

Clear content cells with external link
I have a workbook with a lot of external links. What I'd like to do is to create a simple VBA code to find cells with external link, and then clear content them. Does anyone know how to do this ? Thanks Hey Diego, You have 5 posts to 2 different NG's asking this same question. Have some patience and please do not double, or quintuple, post. tim "diego" <porno_abis@hotmail.com> wrote in message news:06c201c3561c$7e0adce0$a101280a@phx.gbl... > > I have a workbook with a lot of external links. What I'd > like to do is to create a simple VBA code to fi...

Highlight the cell/row if particular name comes..
Hi, This is what I would like to achieve, There will be sheet full of address details and in that I want to highlight the cells with some colour if the particular list(i.e.list of 10 particular area names like Area1,Area2....Area10) of area names present along with the door # and Street name. The challenge is that the area names will be mixed with street name ect., in the same cell. I can guess that this is possible with macro but my knowledge in that is ZERO.. Pls help on this... Can you give us some examples of what your data looks like, and details of what columns you...

Plot empty cells as not plotted (leave gaps)
Under Tools/Options/Chart Menu I have set the "Plot empty cells as: Not plotted (leave gaps)as well as Plot visible cells only, however the chart still plots null values causing the automatic scaling to be impractical If you're using a formula that returns an empty string for missing values, change it to an NA()function. For example: instead of =IF(B9="","",B9) use =IF(B9="",NA(),B9) To hide the resulting #N/A errors on the worksheet, you can use conditional formatting. There are instructions on my web site: http://www.contextures.c...

EMPTY CELLS!!! Validation Rule!!!
How do I set up a cell to be 0.0 (zero) if there is no data available??? I know is on the Validation Rule, but I don't know the formula. I can't add the values of other cells becasue there are a few empty ones. PLEASEEE HELP!! Thanks It sounds as if setting the Default Value to 0 will accomplish what you need. For existing records you will need to do something such as run an update query to replace null with 0. You may be able to use the control's Exit event to add the value, depending on just what you need to do. I don't think you can use table-level validation to add...

Finding Cell with Minor loss of fidelity
I have a Excel Spreadsheet that was created in Excel 2003, but updated with Excel 2007. Whenever I save it, it runs the Compatibility Checker, and comes back with a 'Minor loss of fidelity' message. I would like to find the cell(s) that thie issue talking about. Is there a way to find which cell(s) have the issue, so I can fix them? -- Doug Krohn Hi, when saving the spreadsheet Save it as xlsm "Doug Krohn" wrote: > I have a Excel Spreadsheet that was created in Excel 2003, but updated with > Excel 2007. > Whenever I save it, it runs the Compati...

Cell Drag & Drop without Incrementing
Hi, Simple question really. Is it possibe to drag a cell without it incrementing automatically. Eg if cell a1 contains TR321 I want to be able to drag it down to fill cells a2:a10 without any change, ie A10 is also TR321. Cheers, thanks for your help. Paul Hi Paul use an absolute reference. change the cell reference TR321 to $T$321 also have a look at http://www.cpearson.com/excel/relative.htm -- Regards Frank Kabel Frankfurt, Germany Paul wrote: > Hi, > > Simple question really. Is it possibe to drag a cell > without it incrementing automatically. Eg if cell a1 > co...

Does any one ever use function capFileSaveDIB?
I use VS .NET 2003. I had connected to a USB camera. I tried to capture the image. The function capFileSaveDIB is used. MSDN said this function would return true if successful. I call the function like this: int cameraCapture(HWND hWndofCap, CString pathCStr) { char* pathStr = ""; strcpy(pathStr, pathCStr.GetBuffer()); pathCStr.ReleaseBuffer(); if (capFileSaveDIB(hWndofCap, pathStr)) return 1; else return 0; } I get the return value: 1. However, I don't get the picture where pathCStr pointed. I don't understand. Have you met this problem? I can't find out wh...

Shade half a Cell
Hi Is it possible to shade half of a Cell? I have merged to cells togethe and now i am wanting to shade half of the Cell, BUT, i am wanting, i possible, to shade the Cell on an angel (for example - shade from top left to bottom right and have half gre and half white) Is this possibl -- Message posted from http://www.ExcelForum.com Hi sparky........... You can't shade half a cell any way with the normal background coloration technique. You can however, for special cases, create a drawing object of the shape you want and color it and place it in a cell..........it would look similar....

Calling on cells with red fonts
Can someone please guide me how to show only those cells in red fonts i column. I just want to see the red fonts not those in black fo calculation. Thanks a lot. 74 89 25 93 21 32 2 -- celesta ----------------------------------------------------------------------- celestar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3626 View this thread: http://www.excelforum.com/showthread.php?threadid=56777 Chip Pearson shows how to work with font colors here: http://www.cpearson.com/excel/colors.htm celestar wrote: > > Can someone please guide me how to show...

How do I make Outlook look in Stationery subfolders?
When I click Actions->New Mail Message Using->More Stationery, Outlook displays the .htm files in Program Files\Common Files\Microsoft Shared \Stationery, but NOT its subfolders. I've looked through the Registry, which shows a number of possibilities, but I don't know how to tell it to include subfolders. (Don't suppose good old /s would work?) How about a way to make the Get More Stationery button display subfolders instead of connecting to Microsoft's website? I'm using Outlook 2000 (HTML but not Word Editor) on XP Pro. I gather from other posts that Outlook 200...

one mailbox sending NDR, but message is being delivered
Clever People! Any chance you can help me on this, I have an XCHG2k3 Server with 5 mailboxes, all connect to POP3. One of the exchange mailboxes is sending a mailbox unknown (5.1.1) error to the sender, but is still delivering the message to the client inbox. This occurs for internal and external sources. The NDR is as follows: ********************* Reporting-MTA: dns;company.ltd.uk Received-From-MTA: dns;company.ltd.uk Arrival-Date: Tue, 1 Feb 2005 13:03:42 +0000 Final-Recipient: rfc822;firstname.surname@COMPANY.LTD.UK Action: failed Status: 5.1.1 ********************** Any ideas? Kat ...

How do you do a strike out in an Excel cell?
How do you do a strike out in an Excel cell? Format | Cells... | Font | Effects = strikethrough Regards Trevor "dgalati" <dgalati@discussions.microsoft.com> wrote in message news:4364AE43-F016-45B3-A6ED-160A405B3291@microsoft.com... > How do you do a strike out in an Excel cell? ...

One To Many Records In A Sub Form
I have a form set up, with a sub form in it. When I key the data in the form it updates the sub form as it should. The problem is that some of the records I want to key in the main form, might have multiple records in the sub form. How do I get this to work as I can only have one record each time. Both the form and the sub form are related to differnet tables. Many Thanks in advance for any assistance. This is a really head scratcher for me. On Tue, 19 Feb 2008 07:10:02 -0800, Tony Montana <TonyMontana@discussions.microsoft.com> wrote: >I have a form set up, with a sub form i...

How many worksheets can be inserted in one workbook?
I have just started using MS-Excel. I was wondering what is the maximum number of worksheets that can be inserted in 1 MS-Excel Workbook? If I remember correctly, I've seen folks report that they're been able to insert upwards of 1000 sheets (as a test). For practical purposes you're limited by the amount of memory your system has. Search Excel help for "specifications and limits". It says: Sheets in a workbook - Limited by available memory (default is 3 sheets) -- Biff Microsoft Excel MVP "Bobby" <Bobby@discussions.microsoft.com> wrote in messa...

Word 2007 show combined revisions in one document
I combined several sets of revisions in one document and noticed a peculiarity: when a word was replaced without deleting the space after the word and inserting a new space, the deleted word would show up twice in the combined-revisions document -- once before the inserted word and once after. So it looked like this: (replacing "dogs" with "cats" -- I'm USING caps to show the deleted word) I like DOGScatsDOGS. Where the space had been left in, it looked correct: I like DOGS cats. There was no problem in this respect with the individual redlined do...

Filter rows containing cells with values within a range (for example <11 or >10 but <21)
Column B consists of Brand names (each brand has numerous products) Column C consists of Product names (over 500 products) Coumns D to CG (row 1) consist of months going back a few years. The data from D2:CG539 consists of the rank of each brand for each month. I would like to filter my rows so that I can view only the products that have ever ranked in the top 10. I already have a couple of formulas that allow me to filter my data. Example 1: If I want to know the highest rank ever achieved by a product I use this formula: =MIN(D2:CG2) and then drag it down. Now I can use autofilter and see w...

Looking for POS for Grocery/Deli using GP. Anysuggestions?
6-10 stations ...

All Replies from One Account Regardless of Which Received
I have two accounts set up in Outlook 2007. I want to receive mail via one but when I hit reply have OL automatically use the other. I have already set the other as the default account in TOOLS-- ACCOUNT SETTINGS. Is this possible and if so how? No it is not possible automatically. Outlook used the receiving account = to send by default. You would need to change it manually for each item = you send (use the Accounts button). --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After furious head scratching, BigK asked: | I have two acco...

Add comma after last " in a cell
Ultimately, I want to do a text to columns method but 1st I need to make the data work properly. I have close to 9000 items of vary degrees of numbers and text. I want a formula that will insert a comma after the last " or ' in a cell, reading from left. 3/4" x 1/2" pvc pipe 1/2" x 1/2" x 6' black pipe 1' insulated filter This way when i do the text to columns, i can separate the by commas. The purpose is to separate the sizes and the descriptions. Put these two macros in a standard code module, select the cells you want to process the...

Substracting numbers in non contigous cells
Hi! I need a formula to substract numbers in non contigous cells THAT ARE IN ONE COLUMN and have the result in other cell, for example: ..=2E.....................A...................B R1..................................BLANKCELL R2..................................BLANKCELL R3..................................15 R4..................................BLANKCELL R5..................................BLANKCELL R6..................60.............75 R8..................................BLANKCELL R9..................................BLANKCELL R10................................BLANKCELL R11...............