formula Help needed

Hi
I have two worksheets one is purchases and another is named Bank
on purchases I have 4 columns

    :::::A::::::::::::::::::::::::::B:::::::::::::::::::::::::::C:
1:::::                 WHSmith
2::::DATE----------Dr------------Cr
3::::2-10----------�480.18------(Formula needed)

Then I have Bank
   DATE----------Details---------Dr------------ Cr
   9-10-----------WHSmith------ 0-----------�480.18

What can I put in C3 to lookup for �480.18 in Bank worksheet and return the
value in C3?????
TIA
hope I explained it clearly..




0
MeAgain
10/21/2003 7:28:57 PM
excel 39879 articles. 2 followers. Follow

6 Replies
472 Views

Similar Articles

[PageSpeed] 23

Do you just want whatever is in the one cell to appear in another cell?

If you do, just go to the cell where you want the information to appear
and type the equals sign = and then click on the cell where the
information currently resides.  You're basically saying:  "Whatever's
in C1, also put here in C2.



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

0
10/21/2003 8:13:50 PM
I know what you are saying but it was just an example.
I have more 1000 rows in Bank Sheet. I update it every days from the Bank
website and I have different supplier accounts. WHSmith is just one of them.
I want to lookup for �480.18 in range Bank!$C$1:$D$1000
I did try VLOOKUP but it won't work.
I am looking for any formula that would return the Extact Matched value from
the range.

"mdijulio" <mdijulio.vo1az@excelforum-nospam.com> wrote in message
news:mdijulio.vo1az@excelforum-nospam.com...
> Do you just want whatever is in the one cell to appear in another cell?
>
> If you do, just go to the cell where you want the information to appear
> and type the equals sign = and then click on the cell where the
> information currently resides.  You're basically saying:  "Whatever's
> in C1, also put here in C2.
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
MeAgain
10/21/2003 8:30:34 PM
Hi there,

It sounds as though you want an "IF" function, but I'm not sure what your
criteria for a match would be but I'm guessing that it is looking for the
contents of B1 (in the purchases worksheet) in the B column of the Bank
worksheet, does this sound right?

"MeAgain" <gulbale@nowhere.3333> wrote in message
news:zIglb.504$vr5.301@newsfep4-winn.server.ntli.net...
> I know what you are saying but it was just an example.
> I have more 1000 rows in Bank Sheet. I update it every days from the Bank
> website and I have different supplier accounts. WHSmith is just one of
them.
> I want to lookup for �480.18 in range Bank!$C$1:$D$1000
> I did try VLOOKUP but it won't work.
> I am looking for any formula that would return the Extact Matched value
from
> the range.
>
> "mdijulio" <mdijulio.vo1az@excelforum-nospam.com> wrote in message
> news:mdijulio.vo1az@excelforum-nospam.com...
> > Do you just want whatever is in the one cell to appear in another cell?
> >
> > If you do, just go to the cell where you want the information to appear
> > and type the equals sign = and then click on the cell where the
> > information currently resides.  You're basically saying:  "Whatever's
> > in C1, also put here in C2.
> >
> >
> >
> > ------------------------------------------------
> > ~~ Message posted from http://www.ExcelTip.com/
> > ~~ View and post usenet messages directly from
http://www.ExcelForum.com/
> >
>
>


0
10/21/2003 11:51:07 PM
Here's an approach using OFFSET and MATCH (for left lookup)
which you might want to play around with...

In the "purchases" sheet, let's say you have redesigned
the table structure below (this is suggested, btw) in cols A - E,
with data from row 2 downwards, for e.g.:

Org .....Date.... .....Dr.Cr.....Date
WHSmith ..02-Oct-03.. �480.18.(amt?).....(date of Cr?)
MPH ....18-Oct-03.. ..�50.00.(amt?)......(date of Cr?)
etc

And you would like to extract the info for
cols D & E from the "Bank" sheet

In the "Bank" sheet you have 4 cols A - D,
with data from row 2 downwards, for e.g.:

Date ...........Details.... ...Dr........ Cr
09-Oct-03...WHSmith....0 ...�480.18
15-Oct-03 ..MPH............0 .....�50.00
etc

Set-up steps:

In the "Bank" sheet

Put in E2: =TRIM(B2&"_"&D2)
copy down

In the "purchases" sheet

Put in D2:
=OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-1,1,1)

Format D2 as currency (�)

Put in E2:
=OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-4,1,1)

Format E2 as date (dd-mmm-yy)

Select D2:E2 and copy down cols D:E

Using the sample data above, the values returned in
the "purchases sheet" cols D:E would be as shown:

Org .....Date.... .....Dr...Cr..Date
WHSmith ..02-Oct-03.. �480.18.�480.18.....09-Oct-03
MPH ....18-Oct-03.. ..�50.00...�50.00.....15-Oct-03

Hope the above helps

Max

MeAgain <gulbale@nowhere.3333> wrote in message
news:OOflb.456$vr5.155@newsfep4-winn.server.ntli.net...
> Hi
> I have two worksheets one is purchases and another is named Bank
> on purchases I have 4 columns
>
>     :::::A::::::::::::::::::::::::::B:::::::::::::::::::::::::::C:
> 1:::::                 WHSmith
> 2::::DATE----------Dr------------Cr
> 3::::2-10----------�480.18------(Formula needed)
>
> Then I have Bank
>    DATE----------Details---------Dr------------ Cr
>    9-10-----------WHSmith------ 0-----------�480.18
>
> What can I put in C3 to lookup for �480.18 in Bank worksheet and return
the
> value in C3?????
> TIA
> hope I explained it clearly.


0
demechanik (4694)
10/22/2003 7:37:11 AM
thanks it will work for me.

"Max" <demechanik@yahoo.com> wrote in message
news:OwkK79GmDHA.2772@TK2MSFTNGP10.phx.gbl...
> Here's an approach using OFFSET and MATCH (for left lookup)
> which you might want to play around with...
>
> In the "purchases" sheet, let's say you have redesigned
> the table structure below (this is suggested, btw) in cols A - E,
> with data from row 2 downwards, for e.g.:
>
> Org .....Date.... .....Dr.Cr.....Date
> WHSmith ..02-Oct-03.. �480.18.(amt?).....(date of Cr?)
> MPH ....18-Oct-03.. ..�50.00.(amt?)......(date of Cr?)
> etc
>
> And you would like to extract the info for
> cols D & E from the "Bank" sheet
>
> In the "Bank" sheet you have 4 cols A - D,
> with data from row 2 downwards, for e.g.:
>
> Date ...........Details.... ...Dr........ Cr
> 09-Oct-03...WHSmith....0 ...�480.18
> 15-Oct-03 ..MPH............0 .....�50.00
> etc
>
> Set-up steps:
>
> In the "Bank" sheet
>
> Put in E2: =TRIM(B2&"_"&D2)
> copy down
>
> In the "purchases" sheet
>
> Put in D2:
> =OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-1,1,1)
>
> Format D2 as currency (�)
>
> Put in E2:
> =OFFSET(Bank!$E$2,MATCH(TRIM($A2&"_"&$C2),Bank!$E:$E,0)-2,-4,1,1)
>
> Format E2 as date (dd-mmm-yy)
>
> Select D2:E2 and copy down cols D:E
>
> Using the sample data above, the values returned in
> the "purchases sheet" cols D:E would be as shown:
>
> Org .....Date.... .....Dr...Cr..Date
> WHSmith ..02-Oct-03.. �480.18.�480.18.....09-Oct-03
> MPH ....18-Oct-03.. ..�50.00...�50.00.....15-Oct-03
>
> Hope the above helps
>
> Max
>
> MeAgain <gulbale@nowhere.3333> wrote in message
> news:OOflb.456$vr5.155@newsfep4-winn.server.ntli.net...
> > Hi
> > I have two worksheets one is purchases and another is named Bank
> > on purchases I have 4 columns
> >
> >     :::::A::::::::::::::::::::::::::B:::::::::::::::::::::::::::C:
> > 1:::::                 WHSmith
> > 2::::DATE----------Dr------------Cr
> > 3::::2-10----------�480.18------(Formula needed)
> >
> > Then I have Bank
> >    DATE----------Details---------Dr------------ Cr
> >    9-10-----------WHSmith------ 0-----------�480.18
> >
> > What can I put in C3 to lookup for �480.18 in Bank worksheet and return
> the
> > value in C3?????
> > TIA
> > hope I explained it clearly.
>
>


0
MeAgain
10/22/2003 7:43:03 PM
you're welcome !

"MeAgain" <gulbale@nowhere.3333> wrote in message
news:g6Blb.1120$C85.7521@newsfep4-glfd.server.ntli.net...
> thanks it will work for me.



0
demechanik (4694)
10/22/2003 10:23:42 PM
Reply:

Similar Artilces:

Need help getting files from Exchange Server.
We have a computer that had an account on Outlook attached to a Microsoft exchange server. Apparently for the moment, this computer and the server are inextricably tied together. Outlook cannot even be started without logging into the server because information store or whatever is missing. How do you get the information out of the exchange server to wean the computer from it? tom <Spamblocker@ameritech.net> wrote: > We have a computer that had an account on Outlook > attached to a Microsoft exchange server. > > Apparently for the moment, this computer and the > se...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

I need the fix for Knowledgebase ID: 810999
Where can I get this from? Thanks. In the last exciting episode, "Stephen Aldous" <steve@stephenaldous.com> wrote: >Where can I get this from? Other from Microsoft Product Support Services as stated in the article? No idea. -- Chris Scharff MessageOne http://www.messageone.com -- ROT13 for valid SMTP address -- Call Product Support Services as listed in the article. If all you're looking for is the hotfix, then the call should be free. -- David Sapery Exchange MVP dsapery@mvps.org "Stephen Aldous" <steve@stephenaldous.com> wrote in message...

Please help, I know nothing
I am a very new at this and it is driving me crazy. For example, I want to add all of column A + column B and I want the answer to go in C. I want this action to always happen automatically. How do I do this...in easy, non-math language?? I understand the basic concept of the formula, but it will only do it in C1, e.g I also get the error #name? Where do I enter this "name" and what is it referring to I would appreciate any and all help Thanks Amme your question is a little vague do you want c1 to add a1 & b1 then c2 to add a2 & b2 If yes then in c1 type in =a1+b1 o...

Help with ActiveX
Hi all. I am just getting my feet wet using MFC and am having trouble with an ActiveX control I created. Essentially, I need an ActiveX control that is a container for local controls. What I have is a set of instruments with slightly different communications needs. My plan is to have separate controls for each instrument so that I can use a single exe for all of them. The exe provides the user interface while the ActiveX provides instrument feedback. There are no input fields on the ActiveX but there are several text fields and a progress bar. I am managing the text fields and progress bar in...

Cannot enter an array formula
I'm trying to help a user on a toshiba laptop with array formulas. For some reason he cannot create an array formula. The formula evaluates on everyone else's laptop but his. He has Windows 98 and Office XP Professional. Has anyone encountered this? Ryan If he can't create it then you wouldn't know that it doesn't evaluate, so which is it? What's the formula? and can he actually create it but it doesn't appear to have the right answer. If you hit F9 does the right answer appear? -- Regards Ken....................... Microsoft MVP - Excel ...

time formula question
This formula works great if the ending time is before 0:00. =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)* {2;3;4})),"") $C$9:$C$11 is my starting time i.e. 20:00 $E$9:$E$11 is my ending time i.e. 04:30 How can I get this to work if A21 = 20:15? TIA, David Try this: =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((($C$9:$C$11<$E$9:$E$11)*(A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)+(($C$9:$C$11>=$E$9:$E$11)*((A21>=$C$9:$C$11)+(A21<=$E$9:$E$11)))*{2;3;4})),"") HTH -- AP "David" <dfizer@r...

Removal of Outlook 2000 from XP home Help
I am attempting to remove an unauthorized version of Office 2K from a Win XP home computer. The Removal went fairly normal except that now when accessing any E-Mail client, the Windows installer is attemtping to install something from the Win 2K premium disk. Is there a Registery entry causing this problem? Is there a way to stop this from happening? do you know what it's installing and does it fix it if you give it the disk? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: ht...

Help me create sales chart based on state and quantity
We have a production report on excel. It shows the details for our clients. Part of that data includes the state in which the client lives. We are trying to create a chart showing the percentages of each state( so we know where the most deals are closed) Any suggestions? Hello mr_merchant_man, this sounds like a job for a pivot table, using Average as the data calculation operator. Or, depending on your version of Excel, you can use AVERAGEIFS (in Excel 2007) or calculate an averate with a combination of SUMIF divided by COUNTIF. To be more specific, it would help to s...

Help with formula #28
I have an excel spread sheet with formulas that work correctly, when I email them to another person and they open the attachment all looks ok. When they save the excel file to their PC and open it the formula cells now all have #Name in them. Anyone know what is causing this??? The error message gives us a clue: Excel can't find something - things to check: 1. Make sure the receivers all are using the same add-ins as the sender 2. Make sure that personal.xls is the same for the receivers as the sender 3. Make sure all defined names are the same -- Gary's Student "Play...

need help Combo Box with duplicate entry.
I have a combo box with unique and non-unique entries. (search field) 00010 | john | smith | 12345 | 00002 00196 | jane | doe | 0120 | 00001 00196 | Jone| wood| 0220 | 00005 I would like the following to happen. 1) user types the number needed ( 10 ) 2) the combo box zero fills the field (00010) 3) then selects an entry from the combo box. (12345) if the select is incorrect ( one of the non-unique numbers was selected - 00196) the user will open the combo box and select the correct entry. (jone wood) add the info will be put on the form. the following code works if the user ente...

Sort Need
A B C Row 1 206 S. Harbor Dr. 206 S. Harbor Dr. =LEFT(A1,FIND(" ",A1)-1) =TRIM(MID(A1,FIND(" ",A1)+1,255)) First equ immediately above is in B1 =LEFT(A1,FIND(" ",A1)-1) Second is in C1. =TRIM(MID(A1,FIND(" ",A1)+1,255)) A1 is address in column of addresses to be sorted. Equ starting: =LEFT pulls out the street address number Equ starting: =TRIM pulls balance. Problem: In this example, Sort places C1 in rows starting with l...

Need disk # 2 for Home Publishing Premium 2000
The #2 disk of a (6) disk set in my Home Publishing Premium 2000 was demolished in my CD drive. This is the setup disk and since the CD is no longer available from Microsoft I need a copy badly. Is there anyone that can successfully copy a #2 disk? I will be more than happy to pay anyone for there trouble. Patsy wrote: > The #2 disk of a (6) disk set in my Home Publishing > Premium 2000 was demolished in my CD drive. This is the > setup disk and since the CD is no longer available from > Microsoft I need a copy badly. Is there anyone that can > successfully copy a #2 dis...

How to get the handle of a Help file belonging to other process.
Hi all, In my application,I want to change the Index of a HTMLHELP file which belongs to a dialog running in other exe.In order to do this I need to find whether the Help file is open or not & get the handle of that to use HH_DISPLAY_INDEX .Is there any way to do this?? Note: I dont want to use FindWindow() as it is giving rise to some problems in my application.. Thanks in advance ...

Help with cells auto formatting
Hi, I have posted a similar question before however I never really got this sorted so sorry for repeating myself. Basically I use two spreadsheets daily at work all with various information on and various formats in each Column. My problem is when I close the spreadsheet and reopen it the cells that are formatted as 'general' or 'number' turn into Euro currencies. Does anyone know why this happens or how I can stop it? The spreadsheets aren't stored locally they are stored on a serve that only myself and my boss can get onto and we both have the same p...

Help on adding values to a bar chart
Hello, Here is my problem. I currently have a bar chart that has 8 X-axis bars. I need to add 2 more to the X-axis. The labels for the x-axis are based on cells within another spreadsheet, but are not consecutive cells, they are spread apart. So when I go to add the two more cells, I can only add one. When I click on the second cell to add it to the list, all of the previous cells get unselected. I have even tried to manually add these two values in. Is there a limit to how many values can be on the X-axis? Please Help!!! Thanks. To select non-consecutive cells: select first cell, h...

Lookup Help
I have a spreadsheet with three tabs. Tab 1 needs to perform summary calculations Tab 2 & 3 are ODBC connections with source data from other files. Tab 2 is a sql query that is a cross tab with columns 1-4 representing row data and then the remaining columns are employee ids with a productivity value in their column. Not all columns have the same value as they correspond to a job/service performed on a specific date. My problem Tab 1, column 2 contains "completion dates from 1/1-current and refreshes based on tab 2 each time it opens. I need a lookup formula that wi...

Excel spradsheet automation help
there are 3 sheets in an excel spreadsheet say the names are 1,2 AND3 layouts ar ethere for all 3 sheets. the first sheet is used as input and based on certain fields/columns in the first sheet i need to populate entries in the second and 3 rd sheet. teh main purpose is taht based on the first sheet the 2 and 3 sheet should be automatically filled. wat kind of formula / macro should i be using .. im new to this and jsut know to create macro. CAn anybody help ON either Sheet 2 or Sheet 3, you can put this formula in any cell and it will return whatever value is in cell A1 on sheet 1. =Sh...

help with Excel Chart, Series name
Hello, I am using ORACLE OLE2 package to print a series bar graph in excel. I need to rename "Series Name" that comes out in as a char in excel. By defual the series are names as Series1, Series2, Series3,.....SeriesN. How can I access the handle to "Series Name" which help me change its name. Any help will be highly appreciated. Thanks you, Bilal Bilal, Manally, you do this with Chart - Source data - Series tab. There's a series name box. Select the series to be edited in the drop-down at the left first. It may have a cell reference currently in it, or you can...

Outlook Express address bk. from Mac to Outlook PC ?? Help
I am trying to export the address bk from my Outlook Express (Mac OS 9.2) to a form that my Outlook PC (Windows XP) can read and use. Any thoughts on what procedure to follow. When I export from the Mac it just saves it as a "Simple text" file - no much good for what I want to do. Any thoughts??? ...

I need to charge only the headers
Hello everybody Often I have the e-mails whit very heava files. How can I do to charge only the headers? Thaks a lot Henpat henpat <henpat63@hotmail.com> wrote: > Often I have the e-mails whit very heava files. How can I do to > charge only the headers? I don't understand the question. Could you give an example of what you wish to do? -- Brian Tillman ...

need to edit blank email
I have started working with Outlook 2003, and am exceedingly annoyed that the messages I make are all spaced out at inordinately large intervals. A little bit of knowledge being dangerous, I have fixed Word and Excel to start exactly as I want them to, and would like to do the same with Outlook. When I am done composing a message, I Select All and |Format|Paragraph| and set Before and After to 0 and it collapses the message somewhat, but its annoying to have to keep doing it. Any advice would be gratefully appreciated. -- Regards, P D Sterling Florida, New York USA are you using wo...

Help Needed With Write Conflict Error Message.
I have an order form that users open and close on a regular basis. The form is based on a query. On the form are two fields, "TicketPrinted" and "RevisedDate". Most of the time the field "Ticket Printed" has a value in it of "Yes". What I want to be able to do is that if a date is entered into this "RevisedDate" field, OR if the information is changed in this "Revised Date" field, that when the user closed the form, an update query runs that changes the "TicketPrinted" field from the original value of "Yes"...

I Need Help #2
For whatever reason when I import certain text files, the numbers in certain cells come up for example: 24,00 which excel doesn't recognize. How do I tell the spreadsheet that any cell that has two digits after a comma to add another zero at the end? I am not sure if this helps you, but when you select a cell (or range of cells) hit ctrl+1 or go to format/cells the dialog box lets you customize the cell, the first tab is number and this lets you select the type of cell, like number/text etc. under number it let's you select the decimal numbers desired after a comma. Ben >...

Macro help #6
I can't figure out how to set up VLOOKUP formula with 2 variabl elements. After the intial formula runs in the specified cell, I want 2 element changed: the location of the result and the lookup value. In the case below that would be B1 and A2. Range("B1") = Application.WorksheetFunction.VLookup(Range("A2" Range("data"), 2, False) I've tried setting it up like this: Dim i, k As Integer For i = 1 To 2 For k = 1 To 2 Range("B" & k) = Application.WorksheetFunction.VLookup(Range("a" i), Range("data"), 2, False) Next Next ...