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
0
Utf
5/25/2010 3:30:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
870 Views

Similar Articles

[PageSpeed] 22

=offset() is one of those excel functions that won't work if the sending
workbook is closed.

If the sending workbook is open, then you could use:

=offset(indirect
   ("'[Budget Tracker Master Copy 2010.xlsm]Total by Product'!"&B8),739,3)

This expression will break twice if you close the sending workbook.  =indirect()
is another function that won't work if the sending workbook is closed.



vickya wrote:
> 
> 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

-- 

Dave Peterson
0
Dave
5/25/2010 4:59:27 PM
Reply:

Similar Artilces:

How to update one form when closing another ?
I have two forms open. frmPeople has the Focus frmAccount does not. frmAccount has subFormPeopleList I add a new person to frmPeople (which is linked to frmAccount) Question 1: When I close frmPeople I want to update frmAccount so the new person shows in subFormPeopleList Quetion 2: But I'm also wondering if I add code to the "close event" for frmPeople to update frmAccount What will this do if frmAccount is not open? Will it add the person twice? I'm guessing not as updating a form doesn't really add data to the table... right? Thanks for any help. Mel Thanks...

Looking for a graphics library
I found a graphics library a few months ago but I didn't download it at the time and now I can't remember where I found it! I'm pretty sure I followed a link from a thread in this group, but I could be wrong. The main thing I remember about the library is that it wasn't really about graphics, but rather the graphics (by which I really mean "imaging") library was just a small part of a larger whole. I think that larger whole was about robotics (and/or artificial intelligence). I'm almost positive that the name of the whole thing started with "A&...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

Pivot table of data in more than one worksheet
Hi I have data whose numbers of rows exceed one worksheet.. ie. there are around 100,000 rows.. so I have this data in 2 worksheets.. the data is in continuation.. How can I pivot the data of both worksheets in one pivot table??? Thanks Hi Rehan this can be achieved using multiple consolidation ranges for your pivot table - check out http://www.contextures.com/xlPivot08.html for details Cheers JulieD "Rehan" <anonymous@discussions.microsoft.com> wrote in message news:0abe01c53053$ca11cc30$a401280a@phx.gbl... > Hi > > I have data whose numbers of rows exce...

Need to extrcat data from one Excel file to an other
Hi there I'm looking form a solution to press on button in a Excel sheet which ill exctract some datas from an other Excel sheet which can be close or opened. It should beworing on Windows XP, 2003, Vista and 7 as well with 32 bits and 64 bits. Thanks in avance for your advise and best regards Tra Browse through the example codes found at Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Look at the 'Copy/paste/merge" section. Should be some good material to start with. Gord Dibben MS Excel MVP On Sat, 28 May 2011 19:53:03 +0200, Tra Sumaka <trasumak...

how do i e-mail one page only in excel(trial)
i am attempting to e-mail one page of a workbook and can not figure out how. i do not wish to attatch the complete workbook. If you mean one worksheet and not one "page", you could copy that worksheet to a new workbook and send that as an attachment. If you mean one "page" from a worksheet, select and copy then paste into the body of your email message. Or File>Send to>mail recipient>send current sheet as message body. For all Excel email questions get answers at Ron de Bruin's site. Note the SendMail add-in available as well as the code. http://www.rond...

Generic row reference in formula
I am using a formula in Excel to sum the values in a row: =SUM(B2:F2) Is there a way to use a generic row reference in a formula? For example, I don't know for sure at run time if the data and formula is going to end up in the second row. What I want is something like this =SUM(B#:F#) where the # sign would indicate the current row containing the formula. Thank you very much. Rick Quatro rickquatro@gmail.com One way: =SUM(INDIRECT("B"&ROW()):INDIRECT("F"&ROW())) -- HTH, RD --------------------------------------------------------------------------- P...

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

Another application trying to access Address book!
Whe I go to send a new mesage in Outlook (XP w/all the latest patches and service packs) a notification screen comes up and says that anther application is trying to access my address book. It gives me the option to stop or allow for various amounts of time. I have a current version of Anti virus on my PC and it come back clean when I do a scan. This seem to start after I installed Adobe Acrobat. Soes anybody have any info on this. Or is there a way to find out what application is trying to access the address book. Do you have Word as the default email editor in Outlook? If so you ca...

writing more than one javascript functions for OnSave of an entity
Hi, I want to check whether value of an attribute (field) for an entity is null or not. I want to 'trim' the value before checking. As there is no built in trim function with javascript, I have a seperate function. I want this function to be available to whole javascript code so that I can check the values for all the required attributes. Where should I put this seperate trim function? regards... Hi, My favourite way is to dynamically define function in onLoad event and than use it in the Form, may be in onChange event. But this function will only be available in that partic...

SFO Client to point to another server
Hello, I am running a pilot project with core users using SFO client in their laptops. However, when we eventually go live on the production server, how do i get these SFO clients to read from the new server? What happens to the data that is already present in the SFO Client? Do I have to uninstall and reinstall the SFO clients, if so, any specific procedure to do this? Kindly advise. Hi M.Babu, there will be a way. Uninstall and reinstall SFO - be sure, that the Client, where you uninstall SFO is online and the old SRM server exists. - install, Update or change to your production...

Formula name
Hello, For the life of me, I can't figure out how to copy/paste a formula into the Name>Define>Refers To box. I've used named formulas many times but just typed the formula in. This formula is very long and complex and I just don't feel like typing it into the Refers To box. Can someone 'guide' me through this please? It's got to be simple but I'm overlooking something. TIA Sandy Sandy, copy the formula and use Ctrl v to put it in the refers to box -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post a...

Calculate to another cell as default value
Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "...

moving Exchange 2003 mailboxes from multiple domains to one new do
My compnay has Exchange servers in three different domains. We have built a new domain and want to move all mailboxes into one exchange server in the new domain. We have let mailbox size get out of hand and some users have 2GB mailbox size. exporting old mailbox data to PST and importing into new exchange server is possible but would take forever (hundreds of users). any ideas how to streamline this process? or an alternate method of attack? On Wed, 28 Mar 2007 08:24:06 -0700, Kemper <Kemper@discussions.microsoft.com> wrote: >My compnay has Exchange servers in three different d...

How do I get charts to ignore results of a formula if it equals 0
Hi, Use NA() instead of zero. Depending on your chart type the point will not be plotted. Cheers Andy ChrisRox wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

Create two rows based on one row
A worksheet has information in a single row. The new worksheet has columns. How can I create two rows per column in this new worksheet, based on information from the single row (key=document number, same amount for debit- and credit - but in a different row, accounts based on table) . Hi not really sure about your spreadsheet layout. Could you post an example (plain text - no attachment please) and describe your expected result based on this example -- Regards Frank Kabel Frankfurt, Germany Toni wrote: > A worksheet has information in a single row. The new worksheet has > columns. ...

One column returns value based on other column
I have a column for the employee's name and it is a lookup field. I want a second column to return the department that is in that record of the table that the lookup field is based on. I.E. If we put John Smith in the employee field then automatically the department Accounting is returned in the department table. Is this possible? Keithbetsey, You would use a Query, which includes both your main table and the thable that the lookup field is based on. Your statement "I have a column for the employee's name and it is a lookup field" is probably not true. It is probably...

Chart to show response to a question. One of answer 15 choices.
I know this will be simple but I can't figure it out. On a number of given dates I ask how happy people are and get a number of responses between 1 and 15. I then need to plot the number of occurences of those individual respones i.e. two people were 2, three people were 7 etc. I guess it might be a simple X/Y but I just cannot fathom it out. Any help would be greatly appreciated. Regards, Nick. Hi, You need to create a summary table of possible responses and tally. Assuming responses are in A1:A15 in B1:B5 are the value 1 to 5, which are the available responses. ...

formula will not add up right
I put in =sum(E1+G23) & the total in the cell where I put the formula comes up(2 or 3) cents off? It comes up short when I put =sum(E1*G23). Not al the time but off & on. I have to add stuff on paper to check that the total is right. I should not have to take all this time to use Excel. Your question has already been answered an hour and a half ago -- Kind regards, Niek Otten "Kickstart" <Kickstart@discussions.microsoft.com> wrote in message news:DDD6AB31-657B-4F64-8709-D9E36CF96C41@microsoft.com... >I put in =sum(E1+G23) & the total in the cell where I p...

Create a search folder to look at all emails in one folder and selected criteria in other folders
Hi! Is is possible to create a search folder that shows ALL the email in the Inbox while only showing the unread email is other folders and subfolders? I would like to use this as a modified Inbox. Thanks, PJW I don't think you can do that since a search folder only allows one set of criteria. <phil.whitacre@cox.net> wrote in message news:1144603155.163778.133800@z34g2000cwc.googlegroups.com... > Hi! > > Is is possible to create a search folder that shows ALL the email in > the Inbox while only showing the unread email is other folders and > subfolders? I wou...

X-Y map of 2 issues with one PIN
I want to map on an x-y chart two issues about 2 to 4 products I want to map the products by 1) there price & 2) there complexity I built the data and the chart but my problem is that I am getting 2 sets of plotted pins - one set is how you would expect in various points on the chart the problem is that there is a second set of plot points that run acroos the bottm of the chart and if you remove that plot range from the chart then the other polt points are no longer dynamic. This use to be easy You need to share some more about what you are doing. What are you plotting? Price...

Lookup last in column formulas
Hello Excel users and experts, Is there any significant difference in these formulas that return the last value in the column. =LOOKUP(9.99999999999999+307,A:A) =LOOKUP(9.99999999999999E+307,A:A) =LOOKUP(9.999+307,A:A) =LOOKUP(9.999E+307,A:A) Why use fourteen 9's past the decimal point, either with or without the E when three 9's with or without the E works just as well? Thanks Howard Hi! Good question. Here's a bone of contention I have when I see these types of formulas. Say for example that you know for certain that the absolute largest number that could possibly ...

Is there a way to protect just one cell, not the whole sheet?
The books I read seem to indicate so, but say you first must turn on the 'Protect Sheet' option. When I do that, everything is protected. Any help much appreciated. Hi try the following: - first select all cells you don't want to protect - goto 'Format - Cells - Protection' and uncheck 'Locked' - now protect your sheet -- Regards Frank Kabel Frankfurt, Germany "Fred Exley" <fexly221@msn.com> schrieb im Newsbeitrag news:10hsd5t42ghdt65@corp.supernews.com... > The books I read seem to indicate so, but say you first must turn on the > &...