lookup help

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
>

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

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
>

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

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
>

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

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
>