offset

need soem help.
user types in a value in a1

in b2 i want to display the results

in a3:a50 i have the data i want to display in b2
corresponding to the number entered in a1

how is this done?

so if 13 is entered in a1 then i want to display
want b2 to equal what is in cell a13


0
anonymous (74721)
6/19/2004 7:01:39 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
666 Views

Similar Articles

[PageSpeed] 39

Hi Pete,

If I understand you correctly, try,

=OFFSET($A$2,$A$1-2,0)

Hope this helps!

In article <Xns950D8EB788ACA123abcdude@207.115.63.158>,
 Pete <anonymous@discussions.microsoft.com> wrote:

> need soem help.
> user types in a value in a1
> 
> in b2 i want to display the results
> 
> in a3:a50 i have the data i want to display in b2
> corresponding to the number entered in a1
> 
> how is this done?
> 
> so if 13 is entered in a1 then i want to display
> want b2 to equal what is in cell a13
0
domenic22 (716)
6/19/2004 7:12:32 PM
Try this in B2:

=INDIRECT("A"&A1)
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Pete" <anonymous@discussions.microsoft.com> wrote in message
news:Xns950D8EB788ACA123abcdude@207.115.63.158...
need soem help.
user types in a value in a1

in b2 i want to display the results

in a3:a50 i have the data i want to display in b2
corresponding to the number entered in a1

how is this done?

so if 13 is entered in a1 then i want to display
want b2 to equal what is in cell a13



0
ragdyer1 (4060)
6/19/2004 7:24:37 PM
Pete

In B2:

=INDIRECT("A"&A1)

will do the job.

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Pete" <anonymous@discussions.microsoft.com> skrev i en meddelelse
news:Xns950D8EB788ACA123abcdude@207.115.63.158...
> need soem help.
> user types in a value in a1
>
> in b2 i want to display the results
>
> in a3:a50 i have the data i want to display in b2
> corresponding to the number entered in a1
>
> how is this done?
>
> so if 13 is entered in a1 then i want to display
> want b2 to equal what is in cell a13
>
>


0
6/19/2004 7:25:21 PM
Try this formula in cell B2
=INDIRECT(CONCATENATE("A",A1))

Pete <anonymous@discussions.microsoft.com> wrote in message news:<Xns950D8EB788ACA123abcdude@207.115.63.158>...
> need soem help.
> user types in a value in a1
> 
> in b2 i want to display the results
> 
> in a3:a50 i have the data i want to display in b2
> corresponding to the number entered in a1
> 
> how is this done?
> 
> so if 13 is entered in a1 then i want to display
> want b2 to equal what is in cell a13
0
lmctighe (4)
6/20/2004 12:55:05 AM
Hi,

   =IF(AND(A1>=3,A2<=50),OFFSET($A$1,$A$1-1,0),"")

   or

   =IF(AND(A1>=3,A2<=50),INDIRECT("A"&A1),"")

--
Regards,
Soo Cheon Jheong
Seoul, Korea
_  _
^��^
 -- 


0
exceler1 (19)
6/20/2004 8:58:29 AM
Your first example worked great. Thanks!

"Soo Cheon Jheong" <exceler@hanafos.com> wrote in news:#Y6fUTqVEHA.2840
@TK2MSFTNGP11.phx.gbl:

> Hi,
> 
>    =IF(AND(A1>=3,A2<=50),OFFSET($A$1,$A$1-1,0),"")
> 
>    or
> 
>    =IF(AND(A1>=3,A2<=50),INDIRECT("A"&A1),"")
> 
> --
> Regards,
> Soo Cheon Jheong
> Seoul, Korea
> _  _
> ^��^
>  -- 
> 
> 
> 

0
anonymous (74721)
6/20/2004 4:49:54 PM
Reply:

Similar Artilces:

Using offset in series values of a chart
I have 1 named range created with the offset function on sheet(1): Month=offset($A$1,0,0,counta($A:$A),1) Simple to use as the x-as categories in a chart. For the y-axis I want to use data which can be offset 1, 2, up to 20 columns. Yes I can create 20 named ranges but to do that for 20 sheets in my workbook gets cumbersome. Therefor my question: Can I use the offset function in the series values, like: =offset(Month,0,7) In VBA it it can be done with: ..SeriesCollection(2).Values = Month.Offset(0, 17) But then I have to create one macro for each graph...again cumbersome. Please help. T...

Offset of icons in exe files
Hello, is there a way to retrieve the offset of the icon section in an exe file? Thanks for any clue! Kai Sandner For what purpose? To change the icon? Are you trying to load/extract an Icon from the exe? See if LoadResource and FindResource will help you out. Otherwise a little more info about what you are trying to do will help. AliR. "Kai Sandner" <ks@polycolor.de> wrote in message news:7ad2e9c5-9f6d-460b-a8c7-92e2a3d0af94@a70g2000hsh.googlegroups.com... > Hello, > > is there a way to retrieve the offset of the icon section in an exe > file? > > ...

Using Offset
if i have A1 through A5 containing 5,8,8,3,2 and add a new value each day going straight down like A6 = 7 for tomorrow, if i want cell B1 to return the value of the last cell in column A, how can i get OFFSET to find the last value entered in column A? Scott You can do it without using OFFSET if you wish. If values are numeric per your example......... =MATCH(9.99999999999999E+307,A:A) returns row number of last numeric cell in column A =ADDRESS(MATCH(9.99999999999999E+307,A:A),1) returns address of last numeric cell in column A =LOOKUP(9.99999999999999E+307,A:A) returns the last valu...

Compaction Failure
I recently had a compaction process failure because when I was presented with the dialogue box that asked if I wanted to let OE perform a compaction, I mistakenly clicked on "OK" instead of "Cancel". For whatever reason (maybe because OE was online and my PC was busy) the compaction failed and reported that file "folders.dbx" was in use. Fortunately I backup (copy) my "OE Identity" every time I start my PC so I had a recent copy of my files and folders. However, rather than lose the most recent emails - the ones that had arrived since the backup...

Text Boxes Randomly Offset After Saving
Hi, I'm using Excel 2007 and I'm using text boxes in a visual hierarchy system on a spreadsheet. When I save, everything looks nice and orderly but when I open the spreadsheet again, some text boxes have jumped around and the lines that were snapped to them don't look like they're snapped anymore. Then when I move the text boxes a little bit, the lines immediately snap to them again. Some text boxes are even all the way in the bottom corner, squished together. I've tried grouping and ungrouping text boxes but it doesn't seem to do much. I am using the "...

creating columns with data from offset rows
I have 5 "channels" of data that I'm bringing into a spreadsheet. Each of these channels is represented by numbers in two consecutive rows of column C that I then need to combine to one number. Example, C5 & C6 / C7 & C8 / C9 & C10 / C11 & C12 / C13 & C14 then starting over with C15 & C16. I want to combine these sets of cells into new columns D thru H. Column D for instance would consist of: D5 C5 * 100 + C6 D6 C15 * 100 + C16 D7 C25 * 100 + C26 Column E will consist of E5 C7 * 100 +C8 E6 C17 *100 +C18 E7 C27 *100 +C28 and so on What...

SUMIF with OFFSET
Hello I am trying to find a way to use SUMIF with OFFSET on a range of variable length. My data is organized in this manner: Each row represents one fee charged to a client on a particular date. Column E contains the names of the fees that are charged. Each client has at least one entry for Fee1 through Fee5. The number of rows for any particular client may increase during the year. A blank row will separate clients from each other (Row 18 in this example). There may be more than one blank row between clients. Rows 9 - 17 represent fees charged to one client (Client 1)....

Offset Question #3
So close.... Was trying to do this myself, thought the logic was there but obviously I'm missing something still. Basically want to have a chart automatically update to show additional rows (therefore months) depending on the current month. Table goes from B6:H18 (Row 6 Contains titles: Month,Raised,Closed,Progress,Planned, Projected) Rows 7:18 contain the appropriate data for each month, column B containing all months. Was hoping to have the chart basically recognise when a new months of data should be shown (e.g. currently May so only show data up to row 11). My Version: =OFFSET(Ma...

Offset secondary axis
Hi all.. 1. How do I move secondary axis so that it'll be displayed together with the primary axis? 2. Is it possible to offset secondary axis so that it seems to be continuing from primary axis? the reason is because i'm plotting a value for 'H/D' which goes from 0 to infinity,.. but I'll break it at 'H/D'=5, and start with its reciprocal 'D/H'.. so the H/D = 5 equals to D/H = 0.2 and H/D = infinity equals to D/H = 0 pls kindly help... or any other suggestion... Grayfox ------------------------------------------------------------------------ Posted ...

Offset Problem
=OFFSET(`[worksheet05.xls]PROCESS LOG`!$K$9,B9-4,0,1,1) This formula linked to another spreadsheet previously worked, but a ne workbook has been created and the formula now returns a "0" value to th cells? No errors etc are shown with the formula. The source date value is (eg 123 yet the link returns 0. Any help or advice will be appreciated. Thanks :confused -- morrida ----------------------------------------------------------------------- morrida3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2726 View this thread: http://www.excelforum.com/sh...

formula to look up ref in one sheet and offset in another
Hi there I am trying to create a formula which will use a product code typed into a cell in the same sheet, look up this code in a separate file, and give me the sales figure which is offset from this code. I.e. (if this helps) This is the formula I have at the moment just using the other file: =OFFSET('[Budget Tracker Master Copy 2010.xlsm]Total by Product'!$EJ$5,739,3) but I want the code which is in EJ5 to come from my current sheet which is in cell B8. The range to look up in the Budget Tracker sheet would be $B$5:$MS$5 Hope this makes sense! Thanks =offset() i...

Vlookup and offset
Hi, I would like to lookup a users ID in a table and check if they have a paticular qualification, a normal VLOOKUP will do that but then I would like to refer to a date in the header to see when they gained the qualification so the cell can determine whether they are qualified based on the current date. I could do this without checking the date but that would disguise the lack of skills for the rest of the past records as soon as the skill was recorded. I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as to how to put it all together. Hope someone can help. ...

Broken/offset X-axis on a time scale
Hi, I am trying draw line charts with a broken/offset x-axis with the x-axis being on a time-scale. I am mainly trying to show time kinetics of immune data at first few time points (2, 5, 9 and 12 weeks) and then split the graph and show data for same individual at 1, 2, and 3 years down the line. One way I am trying to do this is by changing the dates for the week time points to months so that that portion of the graph is stretched out and then manually labelling the time points as weeks and years. But the graph dosent look too good. Could you let me know if there is a better way ...

Target cell by "offset" of named intersection?
How do I refer to a cell by using an offset reference to another? My current targets are referred to by intersection off named columns and rows - eg: =monthtotal_JUL stock_bought gives me intersection of row "monthtotal" and column "stock_bought", but now I want another reference to cells above the "monthtotal" row (still in the stock_bought column) without having to name the rows separately. I played with the R1C1 convention ( R[-5] etc) but I'm not sure it's compatible with the intersecting name reference, or if it is, just where to put the offset w...

Is there a way to offset the y-axis of a chart without having the.
I'm making a xy scatter graph for my boss. She wants me to offset the y-axis to the right a little, but does not want anything to the left of it. Is there a way to do this in Excel? Do you want to make the Plot Area narrower? Select the plot area and drag the little black handles. Or do you want the minimum of the X axis to be larger, a positive number instead of zero? Double click on the X axis, click on the Scale tab, and choose appropriate parameters. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.co...

offset not working
I have the following that does work: =OFFSET('Revenue Assumptions'!D76,D24,C24) I have the following which does not work: =OFFSET(B24,D24,C24) D and C 24 obviously stay the same. My value for B24 is 'Revenue Assumptions'!D76 Any idea why the offset is not working when using for its reference point the written name of a cell? -- Boris Hi "BorisS" <BorisS@discussions.microsoft.com> wrote in message news:AE946969-EBEE-4460-B82B-3C585A848427@microsoft.com... >I have the following that does work: > > =OFFSET('Revenue Assumptions'!D76,D2...

Find a number then offset
In cell Z34, how do I find a number from 1 to 12 (say 9 which could be anywhere in column B5:B30) then OFFSET from that to find the result to put in Z34? e.g."=FIND(9) in B5:B30 then OFFSET(9,20,2)" Thanks, Barney Try this =INDEX(B5:D65535,MATCH(9,B5:B30,0),3) -- HTH Bob "Barneypo@gmail.com" <barneypo@gmail.com> wrote in message news:9c7077b9-e190-429d-8ac3-ec9c742cff52@t23g2000yqt.googlegroups.com... > In cell Z34, how do I find a number from 1 to 12 (say 9 which could > be anywhere in column B5:B30) then OFFSET from that to...

Use vlookup within offset?
Can I use a vlookup within an offset formula to define the reference? I want to find a value in a column on a tab, go down one, and record the anwser. On sheet2 in cell c7 I type the formula: =offset(vlookup(b7,'sheet1!$f$1:$f$182),1,0),1,0) b7 is defined by another lookup formula so it's variable depending on an input cell. I've used index/match successfully for this purpose, but vlookup gives me "the formula you typed contains an error." with no explanation of the error. What I'm doing is converting a column of entries on sheet1 to a horizontal...

how to know the offset of title in text file
Dear All: here ,I have a text file. In my text file,have some paragraph and each paragraph has a Title. Question: How Can I know what each paragraph's offset and is in the text file and length is . It is appreciated for someone answer! You have to know what defines a paragraph. If it is a special character, you can parse the file looking for said character. "terrcy" <terrcy@cn-finet.com> wrote in message news:OAq5oz2KEHA.624@TK2MSFTNGP11.phx.gbl... > Dear All: > > here ,I have a text file. In my text file,have some paragraph and each > paragraph has a...

How do I offset TWO columns behind another TWO columns?
I have created a bar chart with 4 colums of information. I would lik two of the columns to be behind the other two colums. Can anyone hel me with how to do this. I have tried creating the chart then offsetting but obviously all th coumns offset behind each other. Thanks in anticipation -- Moxy198 ----------------------------------------------------------------------- Moxy1980's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2807 View this thread: http://www.excelforum.com/showthread.php?threadid=47908 Hi, Set the two columns you want in front to be on the s...

Offset and If Statements
Hi All. I am using an Offset forumula to bring back data in a rank ordered format. I am using the following formula, which is working fine: =OFFSET($V$1,MATCH(ROW()-1,LocationRankList,0),-2) However, the file that this is in is going to be used as a template, for an automated workbook (to be used with many different sets of data). On other occasions though the list (locationranklist) will be shorter than on the original one. Where data is then not included (because the list is shorter) it brings back a zero. i do not want to be able to see zero's as charts are being produced fro...

Offsetting x axis chart labels
Please help - I'm really stuck with an approaching deadline... I'm doing a column chart with negative and positive bars, with the axis crossing at 0. My x axis labels need to be far enough down fro the columns so that they don't overlap the negative ones, but th offset feature only goes up to 1000 and this isn't enough. I've trie hardcoding spaces into the source cells for the labels to offset the further but although it works fine on screen and aligns the tex perfectly, in print preview and when actually printing the labels don' stay put. Any help gratefully appre...

Data in table, may need to convert to columns with OFFSET?
I have data in the following format: Column A is a numerical range from a2-a70 Row 1 is a numerical range of data from b1 - cc1 The data in between "row 1" and "column a" is the recorded data I need to access. (This data is the recording of energy directed at a cellphone antenna and Column A is the 'angle' at which the energy strikes the widget, with row 1 being the amount of energy fired at the widget. The result is shown in the corresponding cell.) (Column A) (B) (C) (D) (E) (F) angle/energy 1 2 3 4 5 -3 (resu...

Excel radial diagram text offset
I have a radial diagram. In this diagram the value arrays are displayed in relation to there total sum. The values of the arrays are displayed as absolute number not in percent of the total sum. How can I get the total sum of all the arrays to be displaed in the diagram without to be forced to calculate it separately? A chart does no calculations for you. If you want a value in the chart, you must calculate it, and point the chart at the calculation. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _...

OFFSET Formula Trouble
Maybe someone can help me with this formula: =OFFSET('NM On time'!$B$25,0,0,COUNTA('NM On time'!$B$25:$J$25),9) I have defined a name for this formula, to utilize as a criteria rang in a graph. Don't ask me why I can't just grab the data I need, I' doing this for someone else, has something to do with altering th data. Anyways, my problem is that this formula is meant to grab "header" for a list of store numbers like so... 95 | 60 | 46 | 50 | 8 | 98 | 90 | 65 | 54 There are nine store numbers, 9 columns across. Obviously, there is just 1 row contai...