Offset help

I need help with and offset formula

I have a summary page with the following
C5=ERC!P5
C6=ERC!P12

What I want to do 
Down column D I want to take that cell reference and offset it by 1ro
and 0colums (on the ERC page).

Thanks in advance
B

--
Message posted from http://www.ExcelForum.com

0
4/23/2004 5:47:42 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
358 Views

Similar Articles

[PageSpeed] 17

If the values in column P are unique, you could use INDEX/MATCH:

   =OFFSET(INDEX(ERC!$P$1:$P$1000,MATCH(C5,ERC!$P$1:$P$1000,0)),1,0)

brianwa < wrote:
> I need help with and offset formula
> 
> I have a summary page with the following
> C5=ERC!P5
> C6=ERC!P12
> 
> What I want to do 
> Down column D I want to take that cell reference and offset it by 1row
> and 0colums (on the ERC page).


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/23/2004 6:38:34 PM
Thanks Debra,

The formula worked, but there is some duplcations so I'll have to fin
another way around it.

Thanks again for your help!

B

--
Message posted from http://www.ExcelForum.com

0
4/23/2004 7:44:42 PM
You could enter the cell reference in another column, and refer to it in 
the formula. For example, in B5, type:   ERC!P5

In D5:  =OFFSET(INDIRECT(B5),1,0)

Then, hide column B

brianwa < wrote:
> Thanks Debra,
> 
> The formula worked, but there is some duplcations so I'll have to find
> another way around it.



-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
4/23/2004 8:54:05 PM
Two ways,

One:

- in a separate (hidden) column B, enter the references without the equal signs,
like
ERC!P5
ERC!P12

- in column C enter
=INDIRECT(B5)
=INDIRECT(B6)

- in column D enter
=OFFSET(INDIRECT(B5),1,0)
=OFFSET(INDIRECT(B6),1,0)


Two:

- enter the following user defined function in a general module in the VBA
editor
'-----
Option Explicit

Function getFormula(tCell As Range) As String
    If tCell.Cells.Count <> 1 Then
        getFormula = "Error"
        Exit Function
    End If
    getFormula = tCell.Formula
End Function
'-----

- in column C enter (as in your example)
=ERC!P5
=ERC!P12

- i column D enter
=OFFSET(INDIRECT(MID(getformula(C5),2,2000)),1,0)
=OFFSET(INDIRECT(MID(getformula(C6),2,2000)),1,0)

HTH
Anders Silven

"brianwa >" <<brianwa.156fti@excelforum-nospam.com> skrev i meddelandet
news:brianwa.156fti@excelforum-nospam.com...
> I need help with and offset formula
>
> I have a summary page with the following
> C5=ERC!P5
> C6=ERC!P12
>
> What I want to do
> Down column D I want to take that cell reference and offset it by 1row
> and 0colums (on the ERC page).
>
> Thanks in advance
> BW
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

0
4/23/2004 9:00:06 PM
This works great!

Thank you both.....

B

--
Message posted from http://www.ExcelForum.com

0
4/23/2004 9:43:34 PM
Reply:

Similar Artilces:

How can I make cross from data,Please help me.
Hi How can I change ( or set ) data that are in a column in rows to columns in one row.( such as cross ) Best Regards, Saeid. If you have to join a few rows, then you may do: Insert - Function - CONCATENATE. ATTENTION: If you want to join the texts but to have something between you must insert in the every text the relevant symbol (i.e.=CONCATENATE("B6/";"C6/";"D6/") "Saeid" wrote: > Hi > How can I change ( or set ) data that are in a column in rows to columns in > one row.( such as cross ) > > Best Regards, > Saeid. > ...

Help with Reports Please
I would like to know how I can send to print multiple reports at once? I have a form that lists an office within that off there are pts that go to that office for different reasons. I want to be able to only send via fax once to this office but to include the different reports associated with this office ID. I can do this with each individual report but then offices would be getting multiple faxes. If so would this need advance coding? Thank you in advance for any help you can provide. ...

vlookup and offset
Hi, Can anyone PLEASE help me with the following? - it's driving me cRaZy!! I want to look up a value in column B and return the contents of the cell across 4 and down 4 from the value looked up in column B. Is this possible? Rachel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ "RachelS" <RachelS.vyc4y@excelforum-nospam.com> wrote in message news:RachelS.vyc4y@excelforum-nospam.com... > Hi, > > Can anyone PLEASE help me with the foll...

how to get the lenth and offset of paragraph?
To All: I have a pragram ,here only is a example similar to My pragram �� these are some data : AAAA this is a test! here is the content of AAAA. BBBB this is a test,I want to know that between AAAA and BBBB lenth . CCCC this is a test! here how can I know CCCC excursion is what relative to AAAA�� best regards! Terrcy Application : SDI View : Derived from CEditView. Menu item added : Calculate Length Paste paragraph mentioned in your mail in view.. be sure to remove any leading blank spaces before 4 BBBB line... Imp...

email address history in Outllook 2003, help to correct
When I compose an email message and start typing the address, a list of addresses pop up. this is helpful but some of them are wrong. When I try and change them by clicking on them, they still go back to the spelling errors I mistakingly put in when I first composed the email. For example, james@mymail.com comes up iames@mymail.com. How can I correct the first mistake of typing it wrong? Thanks, Don in GA Nevermind. I found an answer on a previous post. Thanks anyway. (That was to delete the autocomplete address) On Tue, 16 Jan 2007 11:23:47 -0500, Don Smith <dhsmith@bellsouth.net&g...

Userform Help
Hello, How could I prevent users from entering text manually into the comb boxes on my userforms and to just select the available text from th pull down menu. Thanks -- Message posted from http://www.ExcelForum.com You could set the MatchRequired property of the combobox to true. This prevents users leaving the control until they match an entry in the list -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Axcell >" <<Axcell.11sejy@excelforum-nospam.com> wrote in message news:Axcell.11sejy@excelforum-nospam.com....

Help with CHTMLView and Session
I have an MFC application that incorporates a browser in one of its views using CHTMLView. We have a company website that this view navigates to. On this website, people log in and download stuff (at least in IE they do). When they login, a session gets associated with their process. If they click on a link to download a file, the HTML is such that it causes another window to open up (IE in this case). Because IE is a different process, the session isn't valid for him, so he gets an error that the page isn't available. I found that if I override OnNewWindow2 and perform the...

Creating my first Workflow Rule
Hi there, I'm new to Microsoft CRM. We've just installed v3.0 and I'm trying to create my first workflow rule using the Workflow Manager. When a new account is created I want an email notification to be sent to the account owner. I've tried to create this rule, but honestly have no idea what I'm doing. I followed the giude in the help documentation, but I guess it's not detailed enough for me to get this first one created correctly. I think I need a 'guide for dummies' if such a thing exists. If anyone out there can help me create my first workflow rul...

Help #3
I upgraded my Microsoft Outlook 98 to Microsoft Outlook 2002 and in the "Contacts" section, I lost all notes under every name, some phone numbers, and some addresses. All names transferred correctly. I then went back to the 98 version, hoping that the missing information would be there as it originally was, and it is not. I know it must be out there somewhere, but I do not know where to find it. We searched for .pst files, but they appear to be corrupt. Thank you for any help you can give me. Michelle ...

Folders resorted order themselves
Outlook 2003 on Windows XP SP3 My folders have be resorted in a blanket fashion into Groups and by attachments - I didn't not do this. It just happened after I had accessed one of my archive folders and searched for the word web in my archive folder. (Thought I's say this in case relevant) Any ideas why it happened / and how to reset them all back Thanks Juliet You probably modified your default Messages view. You can reset it via; View-> Arrange By-> Current View-> Define Views... Another way to go would be to reset all your views in Outlook via; St...

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. ...

HELP!!! I need HELP with a array formula PLEASE !!!!
Hello, Here is the ARRAY Formula I have and this is what I am using it for. The situation is that it worked 1 time and than not again. =INDEX(D48:K48,,MAX(IF(D48:K48<>"",COLUMN(D48:K48)))-COLUMN(D48)+1 Duty: I have a row of number that appear hourly (DOLLAR AMOUNTS), the number are anything from nothing to 10000. I want the hourly number to appear in specified cell. Here is an example. (I am using EXCEL 2000) Row D48:K48 answer in cell G2 1st hour D48 = $100.00 G2 Should be $100.00 2nd Hour D48 = $100.00 E48 = (nothing) G2 Should be (nothing) 3rd Hour D48 = $100.00 ...

Need help with Macros for Great Planis
Hi, I am trying to automate the process which invoices gets printed one by one, from an invoice batch in Great Plains' "Billing Entry" screen. And when users pick "Print to Screen", it fires up a Crystal Report viewer where they have an option to save the Invoice as PDF's. I would like to automate this process and auto-save all invoices in the batch into PDF's. I found somewhere on the Internet that the macro command "FileSave file ':C:temp/temp123.pdf' " will save a file onto the hard drive. I have the script to call the "Print ...

Menu Help wanted
Does anyone know any ready menu created from scratch without using Cmenu or any other class inherited from it and made in Core C++ Code which can be supplied with my application. Alternately I would like to know how to do the following easily. 1) keeping a Menu item open even on the event of a click 2) Highlighting on the Menu Item 3) Not activating some parts of the menu depending on parameters passed ...

Excel 2003 help doesn't work. I got both Excel 2003 and 2007 insta
I have both Excel 2003 and Excel 2007 installed. The Excel 2003 help doesn't work, but the Excel 2007 help works fine. When trying to open help in Excel 2003, the help window open, but it's all grey. ...

OWA Question
Can anyone help me with this problem? We use Public Folders on our exchange server in order to have a central place to post info like programs, pricelists, telephone lists, etc. We have some external users who wish to use Outlook Web Access (OWA) in order to be able to view their mail. Because they're not in the USA, dialup is not an option for them. Because they're satellite connected, they cannot use VPN. The problem with it that we've seen is Public Folders (and forms), which are really 2 problems. First: The folders display fine. The contents of them, the dates, etc. sh...

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 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 > &...

OE access error HELP!!!!
Recently i've been having problems opening my attachments. It wouldn't allow me to open any attachments, except .doc, .xls and office documents. It wouldn't allow me to open any other file extension, like .jpg, .html, .gif, .pdf etc. And It's all happend when i download an update for Internet Explorer 6. It comes up with the message. "OE removed access to the following unsafe attachments in your mail". How the hell do i fix this up? Before i even downloaded this update i could already open the attachments in there. And it wouldn't even let me save th...

help with weboutllook
Can no longer use weboutlook outside of my lan...may someone please help me...thx Please see your first posting. Kilan wrote: > Can no longer use weboutlook outside of my lan...may > someone please help me...thx ...

Formula Help #3
I need help with a formula to extract the last name from this example: Mary.Smith@mmmm.edu What I am looking for is "Smith", which will always be between "." and "@". TIA Greg With your example, email address in cell A1 =MID(A1,SEARCH(".",A1) + 1,SEARCH("@", A1) - SEARCH(".",A1) - 1) Dan E "Greg Rivet" <gregrivet@hotmail.com> wrote in message news:#1ucu0pYDHA.1644@TK2MSFTNGP10.phx.gbl... > I need help with a formula to extract the last name from this example: > > Mary.Smith@mmmm.edu > > What I am...

Help with charts.
I'm creating a mail merge in Word, using a large Excel data source. The file contains multiple names, with various associated data. I want to create an individualized chart for each name, using data associated with their name in the Excel data source file. One customized chart per name, merged into the Word document. Can this be done. I cannot figure out how to merge data into a chart.--- or is there a better way? Please help if you can.. In article <5A0A45FF-B5F8-44DC-9924-A7ACCA0ED26E@microsoft.com>, HRassist@discussions.microsoft.com says... > I'm creating a mai...

HELP!! PUBLIC FOLDERS E2K3
I am running Exchange Server 2003 on a standalone Win2k3 server, both are Enterprise version. Earlier today, I rebuilt and defragged the public folders for both the default public folder store and a secondary public folder store. After rebuilding and defragging, the secondary public folder store was fine, but the default public folder store was no longer listed in the First Storage Group. The .edb & .stm files are intact and are consistent (eseutil /mh), but for some reason the First Storage Group, which uses pub1.edb is not listed in the First Storage Group. If I attempt to...

Offset from refernce in other cell
I need to point a cell to another as an offset from a reference. For example, cell A1 formula is =G10 I want cell A2 formula to be =G20 I need this to be dynamic as I need to drag it along the whole spreadsheet. I can't seem to get this to work using the offset or indirect functions. Any ideas? Jonathan Blitz AnyKey Limited Israel A1: =OFFSET(G1,ROW()*10-1) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jonat...

Help designing this database
What would be the best way to design this database with multipletables and all tables have a common similarities:TABLES (divided by type of allegations):investigation referralsanonymous tipsstaff referralspattern claimseligibility reviewdocument requestfalse injury allegationsALL above tables will have these controls:Client#First nameLast nameeach client# can also have multiple claims (claim#). The tables abovecould have the same client# w/same claim#.I was thinking of creating a separate table calling it CLIENT TABLEthat will hold all the client#, first name, and last name. Client# isthe uniq...

Help! I screwed up.......
First a little background...... Small company....7 PCs (4 still Win98, 2 XP Pro and 1 Windows 2000 server) connected to the internet via a Bellsouth DSL connection using a Cayman DSL modem/router. Guy before me installed the server as a domain controller. Company wants to change name of server. Server starts to hang twice a day saying something about packet corruption in the event logs. Company wants all systems open to everyone else. Company wants everything shared (hard drives, printers, wives, etc.). So, I'm thinking that the domain controller thing adds a level ...