lookup help

[green fox]

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

[Earl Kiosterud]

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
>

[green fox]

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

[Earl Kiosterud]

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
>

[green fox]

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

[Earl Kiosterud]

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
>

[green fox]

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

[Earl Kiosterud]

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
>