lookup help #5

Second try with this...I'll try to explain it better. I have a two-cell
worksheet win workbook1. It is replaced every day, automatically with
another two cell sheet in a new workbook with the same name. A1 is a
date. B1 is number, eg. 1542.
I want to automatically place the number into a cell in another
workbook and sheet, by matching the date with dates in column A of the
second sheet, and Column E (fillers)of the second sheet.
I've been playing with match, offset and index, lookup etc. I've been
able to get a number from DATE:E and have it show up in the first
workbook.sheet, but that's the opposite of what I want to do. I can't
have the formuala in the first workbook, because it will be overwritten
each day.

I would be thankful if someone could just point me in the right
direction.

Andy

0
a.fox (15)
8/19/2005 3:18:18 PM
excel 39879 articles. 2 followers. Follow

12 Replies
544 Views

Similar Articles

[PageSpeed] 4

Andy,

In the second sheet, in the column where you want the data, try this 
formula.  It's coded for row 2.

=IF('[Workbook 1.xls]Sheet1'!$A$1 = A2, '[Workbook 1.xls]Sheet1'!$B$1, "")

Copy down with fill handle or copy/paste.  This doesn't deal with the stuff 
in column E because I didn't understand how that fits in.
--
Earl Kiosterud
www.smokeylake.com

"green fox" <a.fox@sympatico.ca> wrote in message 
news:1124464698.694974.125540@g43g2000cwa.googlegroups.com...
> Second try with this...I'll try to explain it better. I have a two-cell
> worksheet win workbook1. It is replaced every day, automatically with
> another two cell sheet in a new workbook with the same name. A1 is a
> date. B1 is number, eg. 1542.
> I want to automatically place the number into a cell in another
> workbook and sheet, by matching the date with dates in column A of the
> second sheet, and Column E (fillers)of the second sheet.
> I've been playing with match, offset and index, lookup etc. I've been
> able to get a number from DATE:E and have it show up in the first
> workbook.sheet, but that's the opposite of what I want to do. I can't
> have the formuala in the first workbook, because it will be overwritten
> each day.
>
> I would be thankful if someone could just point me in the right
> direction.
>
> Andy
> 


0
someone798 (944)
8/19/2005 5:09:14 PM
Column E is where I want the number from workbook1 ($b$1) to go. For
example:

workbook1
A1=Thursday, August 18 2005         B1=1345

Workbook2
A23 = Thursday, August 18 2005        E23=0

I want the 1345 from workwook1 entered into workbook2, E23.

Thanks, sorry for the lack of clarity.  :-)

Andy

0
a.fox (15)
8/19/2005 10:01:40 PM
Andy,

Then put the formula in E2, and copy down.
--
Earl Kiosterud
www.smokeylake.com

"green fox" <a.fox@sympatico.ca> wrote in message 
news:1124486395.576216.290320@z14g2000cwz.googlegroups.com...
> Column E is where I want the number from workbook1 ($b$1) to go. For
> example:
>
> workbook1
> A1=Thursday, August 18 2005         B1=1345
>
> Workbook2
> A23 = Thursday, August 18 2005        E23=0
>
> I want the 1345 from workwook1 entered into workbook2, E23.
>
> Thanks, sorry for the lack of clarity.  :-)
>
> Andy
> 


0
someone798 (944)
8/20/2005 5:19:08 AM
I'm having trouble getting it...

=IF('[rpt_Layout 8000 Fillers and Graybar.xls]qry: filler and
graybar'!$A$1 = a19, '[rpt_Layout 8000 Fillers and Graybar.xls]qry:
filler and graybar'!$b$2, "")

i've tried a more explicit reference to workbook1 c:\layoutdocs\rpt_
etcm
the colon in the worksheet reference was mentioned in error messages,
and I've had a message about the reference to workbook not being
permited.

I can't be more specific because I tried everything I could think of
Saturday night (or was it Friday?) and finally put it down.

I'm in a complete fog, but I figure I missed something that should be
painfully obvious.

dense but determined,

Andy

0
a.fox (15)
8/22/2005 1:56:46 PM
Andy,

I don't know what all that stuff is in the link in your IF function.  You 
have rpt's and qry's and stuff in it.  Sounds as if you're trying to refer 
to Access reports and queries or something.  You need only the workbook 
name, sheet name, and cell reference.  It should take the form:

'[Work book 1.xls]Sheet 1'!A1

--
Earl Kiosterud
www.smokeylake.com

"green fox" <a.fox@sympatico.ca> wrote in message 
news:1124719006.870563.81400@g14g2000cwa.googlegroups.com...
> I'm having trouble getting it...
>
> =IF('[rpt_Layout 8000 Fillers and Graybar.xls]qry: filler and
> graybar'!$A$1 = a19, '[rpt_Layout 8000 Fillers and Graybar.xls]qry:
> filler and graybar'!$b$2, "")
>
> i've tried a more explicit reference to workbook1 c:\layoutdocs\rpt_
> etcm
> the colon in the worksheet reference was mentioned in error messages,
> and I've had a message about the reference to workbook not being
> permited.
>
> I can't be more specific because I tried everything I could think of
> Saturday night (or was it Friday?) and finally put it down.
>
> I'm in a complete fog, but I figure I missed something that should be
> painfully obvious.
>
> dense but determined,
>
> Andy
> 


0
someone798 (944)
8/23/2005 2:48:23 AM
The workbook was exported from access with a vba routine. The sheet
name 'qry: fillers etc., is a carry over from the access report.
Do you think that the colon in the name is the problem? If its the
naming, then I'll just change my access routing to fix it. I'm as adept
a access vba as I am at excel. argghh! Now I have to start messing with
outlook too. I love this stuff but my brain resists.

Thanks a mint.

Andy

0
a.fox (15)
8/23/2005 12:13:16 PM
Andy,

Your IF looked out of whack to me, but I see now that it's not.  I should 
have recognized those names.  My mistake.

I think you're right about the colons.  It wouldn't allow me to name a sheet 
so.  Your formula works if the sheet name doesn't have the colons.

The Lesynski (?) naming convention for access database objects calls for 
object names like rptLayout_8000_Fillers_and_Graybar, or 
qryFiller_and_graybar, but I don't recall the use of colons there.

I think you're just around the corner of the solution.  Keep hacking! :)
--
Earl Kiosterud
www.smokeylake.com

"green fox" <a.fox@sympatico.ca> wrote in message 
news:1124799196.472394.40930@f14g2000cwb.googlegroups.com...
> The workbook was exported from access with a vba routine. The sheet
> name 'qry: fillers etc., is a carry over from the access report.
> Do you think that the colon in the name is the problem? If its the
> naming, then I'll just change my access routing to fix it. I'm as adept
> a access vba as I am at excel. argghh! Now I have to start messing with
> outlook too. I love this stuff but my brain resists.
>
> Thanks a mint.
>
> Andy
> 


0
someone798 (944)
8/23/2005 3:48:36 PM
Thanks again Earl,

that worked, although I think I'll have to nest it in another statement
so it doesn't wipe out the previous date value.

determined

Andy

0
a.fox (15)
8/24/2005 2:01:01 AM
Andy,

Good.  I'm not sure why a date is getting wiped out.  The IF returns the 
data in B1 associated with the date in A1.  What's getting wiped out?
--
Earl Kiosterud
www.smokeylake.com

"green fox" <a.fox@sympatico.ca> wrote in message 
news:1124847296.293725.255280@g43g2000cwa.googlegroups.com...
> Thanks again Earl,
>
> that worked, although I think I'll have to nest it in another statement
> so it doesn't wipe out the previous date value.
>
> determined
>
> Andy
> 


0
someone798 (944)
8/25/2005 12:35:09 AM
Sorry, I  'mis-spoke',  Column A has 31 dates -- august 1 to august 31.
Column E has the corresponding number from the other workbook. The
'else' part ot the if statement returns a blank if the dates don't
match. I need to keep the dates once they are in there. There daily
records. I haven't had a chance to look at it today -- today I'm
wrestling with using a userform to update a spreadsheet AND a userform
to grab dates and return emails. I really appreciate your help.

Thanks,

Andy

0
a.fox (15)
8/25/2005 1:54:52 AM
Andy,

Lemme see if I got this right.  You put a new workbook 1 (rpt_Layout 8000 
Fillers and Graybar.xls), and you want any of the column E values previously 
found from their associated value in B1 (because the date in column A 
matched) to remain in the second workbook.  It's the dynamic nature of any 
formula to work with the current data being referenced.  That's why they 
change when the referred cells (first workbook) change.  You need the data 
to "latch" into the column E cells.  That can be done with self-referential 
cells (circular references), but isn't a great solution.  A macro that puts 
the hard values in place in column E is the better solution.
--
Earl Kiosterud
www.smokeylake.com

"green fox" <a.fox@sympatico.ca> wrote in message 
news:1124934892.331230.122550@f14g2000cwb.googlegroups.com...
> Sorry, I  'mis-spoke',  Column A has 31 dates -- august 1 to august 31.
> Column E has the corresponding number from the other workbook. The
> 'else' part ot the if statement returns a blank if the dates don't
> match. I need to keep the dates once they are in there. There daily
> records. I haven't had a chance to look at it today -- today I'm
> wrestling with using a userform to update a spreadsheet AND a userform
> to grab dates and return emails. I really appreciate your help.
>
> Thanks,
>
> Andy
> 


0
someone798 (944)
8/25/2005 9:41:10 PM
Thanks Earl...that sounds right, I'm using three other related sheets
to create a document, this is the fourth. I'm using a single VBA
routine with the other sheets so it would make sense to do this in
there. I'll see how I can incorparate what I've learned here with that
routine.

Thanks again

Andy

0
a.fox (15)
8/26/2005 12:29:39 AM
Reply:

Similar Artilces:

5.5 -> 2003 Move
Greetings, I am currently under the control of a small NT 4 Domain that I am "moving" to 2003. I say moving because I want to start over on a clean slate and will be re-installing all Vital Apps and services on the the new DC's. The Network consists of less than 100 users and is using exchange 5.5 as it's mail server. I would like to know if there are any thoughts or ideas on my current plan of moving mail to the New 2k3 environment. My plan is as follows: 1. I will perform a move of the mailbox stores to .pst/.ost files. (not sure if there is a way to do this from ...

My category heirarchy got messed up
I just noticed today that my category database seems to be messed up. I'm using Money2003 Deluxe. I started to describe this using generic names, but it was too confusing. So, I'll try it with actual names: The problem: I used to have an Expense category called Personal Care with two subcategories, Fitness and Haircuts. Today, I noticed that these two categories seem to have disappeared from my Money file. They do not show up under Personal Care on the "Set up your Categories" screen. Transactions which formerly had these categories assigned are now mostly showin...

some one help me please
Can someone help me i need to get the total from the amount column to generate over to the amont paid column, the to columns always are the same amount. this is how my spreadsheet looks. Loan # Invoice # Invoice Date Amount Date Paid Amount Paid please help Hi In cell F2 enter =D2 Copy down as appropriate. Regards Roger Govier inneedjen wrote: > Can someone help me i need to get the total from the amount column to > generate over to the amont paid column, the to columns always are the same > amount. this is how my spreadsheet looks. > Loan # Invoice # Invoice Date Amou...

Need a little help setting fonts...
Hello, I need to change the fonts on my applications so that they can use the Registered and Trademark symbols. The Trademark symbol doesn't appear to be in the MS Sans Serif font. MS Word with that font does show it though. Anyway, I've noticed that W2K and XP now use a differnt font. I've forgotten the name of it at the moment. Anyway, I've discovered a minor problem with the way Windows and fonts work and could use some help determining the correct resolution. I've tried changing the fonts in all my windows to the new font. This works pretty much okay. Ho...

Need Help with log on
I have frontpage and have always edited my site using it, my computer crashed, we got a new one and installed frontpage again. In the past when I would go to edit my site a box would come up asking for my user name and password so I could connect to the host server (I think thas what it is called) Anyway now no box comes up at all and I do not seem to beable to edit anything. I have asked my web host and they can not help me. The site is up and working but I can not make changes. Sorry I do not understand much of the tech stuff. I had a lady who did this for me but she is no lon...

Help with reading attributes
Hi, I have this xml structure <listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <rs:data ItemCount="1"> <z:row ows_AZNamnUppdragsansvarig="Peter" ows_AZTypAvUtbetalning="Arvode till privatperson" ows_AZPersonnummer="196202081276" ows_AZPlusgiro="5456436534" ows_MetaInfo="1;#"...

outlook 2007 and activesync 4.5
After reinstalling wxp and office 2007 i can't synchronise my Dell-PDA in outlook. Activesync says: Problem 85010014 on desktop.... I have reinstalled Outlook / office / activesync. Nothing worked. It has something totdo with outlook, but untilll now nobody coud help me out. You can...?! i hope. My pda says that there has been an sync... "Marius" <Marius@discussions.microsoft.com> wrote in message news:CCA1BF9E-FB29-4F77-BB0A-C02172FAC0AF@microsoft.com... > After reinstalling wxp and office 2007 i can't synchronise my Dell-PDA in > outlook. > A...

Hyperlink #5
Is it possible to make the destination of a hyperlink a graph o graphic -- Message posted from http://www.ExcelForum.com Hi not without using VBA and triggering the selection_change event of your worksheet. Have a look at http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon016 for some code Frank > Is it possible to make the destination of a hyperlink a graph or > graphic? ...

Can not seem to get Csharp to talk to the sql compact 3.5 database
Hi I am new to csharp. I am trying to get my Csharp windows app to connect to the sql compact database. I am using Visual Csharp Express 2008 and I created the database using the Csharp express IDE. It is in the project folder. I created the tables and added elements to the table manually with the IDE. So the IDE seems to connect to the database. I can also contact the database using the SQL Server management studio. When I created the database I had the connection string placed in the config.app file. A string does appear to exist there. When I run the app. I can not seem to g...

Need IF Formula Help
Hi Guys Am attempting to write a formula with 2 ranges of outcome. Here's what I have so far and you can tell me what's wrong because I'm getting a #value! error message instead of an answer =IF(AND(G4)<=0.5,sum=0),IF((G4)>=1.5,(sum=0.5*(F5)) If a specific cell has a response that is less than .5, I need it to return a zero answer. If that same cell's response is greater than 1.5, I need it to multiply .5 by another cell (f5) and return the answer Any thoughts? Thanks in advance for your help. Please email any responses Jessica.w@zimshipping.com Hi not sure what &#...

teredo tunneling psuedo-interface code 10??? affecting my usb now i cant download music onto my ipod help!!
so i just got an ipod and when i pluged it in it said that and ipod was detected but could not be......( i forgot the rest) please unplug and try agian and it dosent work . so then i downloaded tune up utilities and it said i had 3 errors ,2 of wich really dont matter, but then i looked at the 3rd one and it said i had a code 10 on my teredo tunneling psuedo interface and it could not start. please help me!!!!! -- etfboy450 ------------------------------------------------------------------------ etfboy450's Profile: http://forums.techarena.in/members/169455.htm View this t...

NETWORKDAYS
How do I use the "networkdays" formula to determine when a produc should ship by? I have a PO issue date and a shipment due date, I need to calculate "Should ship by date". I know that it takes 30 days on the water, an I can't include weekends. Help -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showthread.php?threadid=27564 On Fri, 5 Nov 2004 09:42:32 -0600, ajpowers <ajpowers.1f991b@exce...

HELP: Search a formula inside of a Cell
I am trying to compare some old spreadsheets and upgrade to the new and have a case in try to determine if a value was added to a formula inside a cell. So I would like to do a search/find on the contents of the FORMULA in the cell. Example: Cell A1 Contains a formula +F12+F34+F67 Is there a function that can search the formula? Example: FIND "F34" in cell A1. Everything I can see so far you can only search the results, not the formula. Thank you in advance for your help!!! Mike formula are strings. Usually I use the FIND in the worksheet menu to locate the form...

Inputting Source Data
I am creating a custom combination chart to display sales data. Bars along the x axis will display actaul sales figures and a line graph will dispay projected sales. I am new to this and am not sure how to enter the data. I have the data on a separate worksheet as you see below (but lined up better). If anyone has suggested regarding how to enter the data formula, I would appreciate it. Apr May Jun Jul Aug Sep Oct Nov Dec Total Actual 6 Projected 2,836 560 800 1,936 8,112 5,412 6,656 3,568 29,880 Thanks, Ali :) :confused: -- foredis -------------------------------------...

Altering data that was called with LOOKUP
Hi, I have a list of data approx 9000 lines long (32 columns), and have a seperate sheet that allows a user to input a code. This then retrieves their data item using vlookup(). I would like to let the user then amend the contents of one of the fields, thus updating the original data list. Is this possible? thanks -- Lynxbci3 ------------------------------------------------------------------------ Lynxbci3's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10377 View this thread: http://www.excelforum.com/showthread.php?threadid=487545 It could be possible, ...

Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot find any reference to it in the help. try this url http://www.fontstuff.com/vba/vbatut07.htm ================= "Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message news:72D4DB63-1D7F-477A-93C7-2F767A207B26@microsoft.com... > Is it possible to lookup a calendar for date selection on a cell. I cannot > find any reference to it in the help. ...

Exchange 5.5 in a Windows 2000 Environment
Is it possible to run an Exchange 5.5 server as the only Exchange server in a Windows 2000 environment? On Tue, 01 Feb 2005 14:21:48 -0600, Randy Dalton <RPDalton@bibb.com> wrote: >Is it possible to run an Exchange 5.5 server as the only Exchange server >in a Windows 2000 environment? Absolutley, even though you have AD, you don't need to run an AD aware messaging platform. You can stick with 5.5 and choose whether or not to deploy the ADC to enable single point administration or alternatively just carry on with a separate directory as is the case in 5.5/NT4 environments. ...

530 5.7.0 Must issue a STARTTLS
Hi, I recently migrated from Exchange 2000 to Exchange 2003 on a new server. Since the migration, some people outside our company get the following bounce back message when they try to e-mail someone who's mailbox is on the Exchange 2003 server: Subject: Returned mail: unreachable recipients: <<e-mail address>> Likely reason for failure: 530 5.7.0 Must issue a STRATTLS command first. Please help. Thanks! Frank Check your SMTP virtual to be sure that you have not enable encryption for inbound e-mail. "fment" <fment@discussions.microsoft.com> wrote in ...

Customer Sync error....HELP!!!
I was updating a customer in HQ, their billing info. I closed out the client list, then went back into it 5 minutes later and every one of our 10,000 customers now all have the same billing information and extra phone numbers in each customer account. The purchase history is intact as are all the tracking numbers per customer. On top of that, HQ had already sync'd to my other stores and now every store has 10,000 of the same customer. I need help and need it badly and as fast as possible. Anyone have any ideas on how to fix this? ...

Help please with multiple accounts in Exchange
I am setting up Exchange 2003, with Outlook as client software. What I need to do is to be able to accept email for two accounts, shall we say called 1@domain and 2@domain, where BOTH these emails go into the 1@domain Inbox on Outlook, where 1@domain is forwarded to a third party but not 2@domain. Becuase 2@domain is a 'private' box. All email to both accounts needs to be accesible via webmail and blackberry from the one inbox. And I cannot rely on anyone sending email to use codes like 'private' etc. Anyone that can help would be a genius! Thanks in advance. On Fri, 20 Aug...

Urgent Help with CheckSignature method of SignedXml
Hey Friends, I have a Signed XML that looks like the below I also have the public certificate for this but i am not able to pass the public key of the X509Certificate2 to the CheckSignature method any idea or help? <?xml version="1.0" encoding="UTF-8"?> <B:Envelope xmlns:B="http://schemas.xmlsoap.org/soap/envelope/" xmlns:A="http://www.routeone.com/namespace.messaging.diag#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><SOAP:Header xmlns:SOAP-SEC="http://schemas.xmlsoap.org/soap/security/2000-12" xmlns:S...

help : exchange 5.5 DS problem
Hi All, One of my exchange 5.5 site has around 10 servers. On last Friday, I found the DS database of one of exchange servers was damaged so the DS service of the server could not be started. Therefore, I repaired the DS database then I could start the DS service on the exchange. However, from the exchange server's event log, we found the following error message >>Source : MSExchangeDS >>Catalog : internal processing >>Event ID : 1171 >>Message content : Exception e0010004 has occurred with parameters -1601 >>and 323384 (internal ID 208017d). Cont...

deleted items #5
Is there a way to retrieve the deleted items after you empty "deleted items" in Office 2000 Outlook? I do not have the "Corporate/workgroup" set-up--just "Internet Mail Only". When you emptied the 'deleted items,' folder there should have been a message asking "Are you sure that you want to Permanently Delete..." Permanently deleting implies there is no way to recover them. >-----Original Message----- >Is there a way to retrieve the deleted items after you >empty "deleted items" in Office 2000 Outlook? I do not &...

Help creating a cost range
I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You Try a VLOOKUP. See: http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "tysonstone" <tysonstone@discussions.microsoft.com> wrote in message news:4F45699F-5C22-4E4F-98E5-BE246543CCD4@microsoft.com... >I need help creating a cost r...

Drop down list help #2
Excel 2003I have created a drop down list in a cell(s) and I want the list to show all values with out a scroll bar. The list is not long (maybe 20 values in the end) Anyway to do this? Thanks Dave Only with a combobox....................Data Validation dropdowns are limited to 8 items. Debra Dalgleish has a sample workbook with a 12 item combobox tied to a DV dropdown. Can be easily changed to your 20 items. http://www.contextures.on.ca/excelfiles.html#DV0020 Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 11:36:13 -0700, "Dave" <dcooper@iacnc.edu> wrote: >Exce...