Excel Formula using relative position of cells in two different worksheets

I want to know how a certain Excel formula would be written.

Excel formulas reside in cells computing the value of the cell content.
A given formula can be entered in an entire column, producing different
values in each cell in the column in which the one given formula is
entered.


The desired formula would, based upon the distance in worksheet A of a
given cell G that the desired formula resides in from a starting point
cell S in worksheet A, reference a cell R in worksheet B, that is a
certain distance from a starting point cell BS in worksheet B, and use
the value in the referenced cell BS in worksheet B to produce a value
for the given cell G in worksheet A.

Definition

Distance: a cell can be a given number of rows away from a cell in its
own column; a cell can be a given number of columns away from a cell in
its own row.

Help sections do not seem to answer the two basic questions: 1 Is the
software able to do a given thing? 2 How is the given thing to be done?
Time is wasted simply determining if a software can even do something
in the first place.

0
vincemoon (4)
12/14/2006 3:10:48 AM
excel 39879 articles. 2 followers. Follow

1 Replies
498 Views

Similar Articles

[PageSpeed] 35

David,

You can use columns() and rows() to work out where cells are located.

It is a little difficult to understand what you want may be an example would 
help
 
-- 
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"David Virgil Hobbs" wrote:

> I want to know how a certain Excel formula would be written.
> 
> Excel formulas reside in cells computing the value of the cell content.
> A given formula can be entered in an entire column, producing different
> values in each cell in the column in which the one given formula is
> entered.
> 
> 
> The desired formula would, based upon the distance in worksheet A of a
> given cell G that the desired formula resides in from a starting point
> cell S in worksheet A, reference a cell R in worksheet B, that is a
> certain distance from a starting point cell BS in worksheet B, and use
> the value in the referenced cell BS in worksheet B to produce a value
> for the given cell G in worksheet A.
> 
> Definition
> 
> Distance: a cell can be a given number of rows away from a cell in its
> own column; a cell can be a given number of columns away from a cell in
> its own row.
> 
> Help sections do not seem to answer the two basic questions: 1 Is the
> software able to do a given thing? 2 How is the given thing to be done?
> Time is wasted simply determining if a software can even do something
> in the first place.
> 
> 
0
12/14/2006 3:36:01 AM
Reply:

Similar Artilces:

UI Data Validation using XML Schema
Has anybody done UI data validation usinf XML Schemas. Any pointers will help. Thanks! ...

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

how to use the type member in System.Web.Services.Description
hi all, I want to parse the wsdl file . It is ease to parse the parts of wsdl except that the part of types. Because I don't know how to use the System.Web.Services.Description member types . I have searched the MSDN to find answer , but , it tell me very less. So I come here for help. For example, there is a types is defined as follows: - <types> - <s:schema elementFormDefault="qualified" targetNamespace="http://www.webservicex.net"> - <s:element name="GetGeoIP"> - <s:complexType> - <s:sequence> <s:element minOccurs="0...

How do you set up a submit button to attach worksheet in email?
I am doing a form and I want to have a submit or send link on the bottom of the page. I know how to make hyperlinks but I dont know how to get it where when the user hits the send button it automatically saves that info and sends that form to the intended email address. Basically so you dont have to attach it yourself through email. Is there a way to do this? Have a look here for code to send mail http://www.rondebruin.nl/sendmail.htm#Tips -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on a...

trouble with copying a cell to other cells.
Info adds +1 (so 3oo goes to 301) and in the same function, 2 stays a 2 when copied to another cell. How do I mark what changes and what doesn't? I found the info posted on a different post. by BenjieLop for Copying a formula 9/20/2004 I want to copy a formula from one cell to several others, it is a basic formula =c5*d12 when i copy and paste the formula changes..( =c6*d13 and so on...) I want the second part d12 to change but i want to anchor the first part somehow so the c5 is constant in every formula c5*d13 c5*d14 etc.. The formula =$C$5*D12 will do the trick ...

how do i enable dde in excel?
i am posting a DDE link into an excel cell from an external program and getting the message ' DDE Not enabled' in the cell once the link is pasted in. how do i enable DDE? ...

Paste Several Values into one Cell
Hi all. I have data in several cells (I2:GJ2) on one row. I want to copy all of this data into one cell as it appears now (in a particular sequence). Is this possible? Thx in advance. Not sure if this is what you want but to concatenate data from several cells into 1 cell, use formula =Concatenate(I2," ",H2," ",G2) just add more cell references with commas and " " to give a space between each cell value for all the cells you want. "sgarrett" <anonymous@discussions.microsoft.com> wrote in message news:110F5183-D1CE-4C5A-A7F8-90D01E500FBE@micr...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

Headers in Excel #3
Can you tell me if it's possible and if so, how I can change the header cells, i.e. A B C D E ... etc? Thanks. Andrea (508) 842-3880 Hi Andrea no this is not possible. The best workaround you can achieve is: - use row 1 as header - goto cell A2 and to the menu 'Windows - Freeze Panes' to always show row 1 - in 'Tools - Options - view' you can hide the row and column headers -- Regards Frank Kabel Frankfurt, Germany "Andrea" <shrews.receptionist@nitco-lift.com> schrieb im Newsbeitrag news:12ff301c44354$b88982b0$a501280a@phx.gbl... > Can you tell ...

Excel -> Access Import Bizarreness
A guy gave me an Excel file that he needs me to covert to XML. No problem. Slurp it into a database and use a DataSet.WriteXML() call to turn it into an XML file. But I can't import it into Access because it has "Merged Cells". For example, data that should look like this: ColA ColB ----- ----- Rec1 this Rec2 this Rec3 this Rec4 that Rec5 Other The three "this" cells have been merged into one tall cell: ColA ColB ----- ----- Rec1 this Rec2 Rec3 Rec4 that Rec5 Other I need to "unmerge" these, but I know almost nothi...

sum values from range of cells if cells correcponding have the sam
hiya, i have a small project and i've spent too much time on this already - can't figure it out myself. What i need to do is to create a formula which will find the same values in column A and will sum the corresponding to them values from columns B:E. this is a sort of result i want to get: A B C D E F RESULT: 1 A1 1 5 2 3 A1 31 2 B5 2 6 3 3 A2 19 3 C1 3 7 4 3 B1 22 4 A1 4 8 5 3 B5 37 5 B5 5 9 6 3 C1 33 6 C2 6 1 6 3 C2 16 7 A2 7 2 7 3 8 B1 8 3 8 3 9 C1 9 4 0 3 I would appreciate any ideas how to do this. Thanks and regards, Assume in G1 down are the unique item...

Is this possible within Excel 2000?
Hi, I've used Excel on a get by basis for many years at work, and I'm just beginning to realise its vast capabilities, having viewed several groups which I didn't know existed previously. I subsequently would like to ask if it is possible for Excel to look at the numerical or text content of a cell on one worksheet and then look at a predefined range on another worksheet and find the equivalent cell content and then move the cursor to that cell? This has me stumped presently. Thanks in advance for any assistance. J.J. To actually move the selection, you'd need to use a litt...

Excel 2000 fail to open workbook
Hi, I have one Excel template. If we open it on Excel 2000, the Excel will hang, and the process occupy cpu 95% ~98%. But we can open this tempalte in Excel 2003 normally, and if we save this tempalte in Excel 2003, and then open it in Excel 2000, it work normally too. Excel 2000 (9.0.3821 SR-1) Excel 2003 (11.8231.8221) sp3 Would you help us to find the reason for this issue? Thanks, Nancy Hi Nancy, Perhaps there was some (probably minor) corruption/inconsistency in the workbook that Excel 2003 could fix but Excel 2000 couldn't. -- Cheers macropod [Microsoft MVP - Word] ...

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

Spreadsheet name in a Cell
I am looking for a cell formula that would recover the spreadsheet name and display it in a cell - more or less as "=CELL("row";A1) function is able to return the line number of cell A1 or as "=CELL("filename";C18)" will return the full path of the Excel workbook housing cell C18... Thanks a lot - Alain79 See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH Bob Phillips "Alain79" <desaivresanospam@free.fr> wrote in message news:ddsce3$94f$1@s1.news.oleane.net... > I am looking for a cell formula that would recover the spre...

Need Help With A Formula Please
Hello and thank you in advance for your help! I have a Name on a column. The same name is listed multiple times on another sheet. However, one of the entries has the word DSL on the colmun next to it and on the next colum a value. I want to count those values. For example: On sheet 1 I have: Mike on A2 On sheet 2 i have: A2= Mike B2= Sales c2= 5 A3= Mike B3= DSL C3= 8 A4= Mike B4= Parts C4= 10 I want a formula of sheet1 that will return a value of 8 for Mike by matching the word Mike and DSL on sheet 2. Please helppppp!!!! Thanks!! -- EVRPAGING -------------------------------...

Excel prints some sheets very small
When I try to print multiple sheets of the same workbook at the same time, one of them (a different one each time) prints very small (about an inch tall) in the upper left hand corner. I am printing from a network file but I don't know if this has anything to do with it or not. Please HELP!!! ...

how do I insert rows without changing formula
In a cashflow worksheet how can I add rows without having to re-do all the proceeding formulas. use absolute references e.g. =$A$5 -- HTH RP (remove nothere from the email address if mailing direct) "tinka" <tinka@discussions.microsoft.com> wrote in message news:4ED4569E-0FD6-469B-82B1-F30139B1A280@microsoft.com... > In a cashflow worksheet how can I add rows without having to re-do all the > proceeding formulas. ...

EXCEL 2007 acting up
j$ = "THIS MACRO WILL ERASE THE SECOND & THIRD SET OF DATA" + Chr$(13) + Chr$(13) Using EXCEL 2007, I get an error with the line above as a macro. It does not recognize CHR$. What am I doing wrong? Thanks Not for me it didn't. Check for MISSING references in Tools>References, uncheck them if there are any. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pcor" <pcor@discussions.microsoft.com> wrote in message news:F63F6E95-AF0A-4927-B337-99B5C48B92B1@microsoft.com... > j$ = "THIS MACRO WILL...

problems loading excel documents
When I try to open up an excel document via my documents it says that the same document is open already how can this be stopped Tina First try the standard fix(es)......Tools>Options>General uncheck "ignore other applications" OR Start>Run "excel.exe /regserver"(no quotes and note the space before the / mark). You may have to enter your full path to excel.exe....in that case surround with quotes as in..... "C:\mypath\to Excel\somewhere\excel.exe" /regserver Gord Dibben XL2002 On Sat, 15 Nov 2003 09:16:14 -0800, "Tina" <TinaSCovell...

Two Screens???
We are running MS Excel 97 with MS XP OS on a computer with a Matox drive where we have two screens. Excel used to open on the left hand screen but now opens onto the right hand screen. I'm unable to resize the excel application by selecting the border. How can i get it to re-open on the left hand screen as a default? regards Wayne Wayne wrote: > We are running MS Excel 97 with MS XP OS on a computer > with a Matox drive where we have two screens. > > Excel used to open on the left hand screen but now opens > onto the right hand screen. > > I'm unable...

Proper use of categories in invoicing?
I am a freelance web developer. I use Money to print and track my invoicing. Being a freelancer, I often have a wide variety of clients: there is a continual influx of new clients, as well as a stable of regular clients. The regular clients often bring their own clients with them, and in this case, my role is only to develop the site. As a result, I will often send out 10 or 20 invoices to one of these regular clients over the course of a year, covering work that I may have done on 20 or 30 sites. As it is now, I can sort for invoices to the REGULAR CLIENTS, but I cannot search/sort for i...

how to convert word file into excel file
If any body know about how to convert MS Word file into MS Excel file please let me know. I am unable to do so. Help me Rizwan Wrote: > If any body know about how to convert MS Word file into MS Excel file > please > let me know. > I am unable to do so. > > Help me Hi Rizwan Try Copy and Paste -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=467752 Dear Pau...