Linking and Query, Please help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sharky23 - 21 May 2006 06:12 GMT

I am trying to setup a spreadsheet that will display information stored on a
network server for a simplified report. I have found how to access and link
to another workbook stored on a network drive. The file that I will be
accessing is replaced daily with the new report.

I need the information from column "D" that is in a row where let's say
column "A" =22, and column "B" =302. The number of rows in the spreadsheet
change daily so I am not able to choose a specific cell to link to.

Any ideas?

Reply to this Message

Biff - 21 May 2006 07:25 GMT

Hi!

What type of data is in column D, text or numeric?

Will there be only 1 instance of 22 in column A and only 1 instance of 302
in column B?

Try one of these:

If column D is numeric:

Normally entered:

=SUMPRODUCT(--(A1:A65535=22),--(B1"B65535=302),C1:C65535)

If column D is text:

Array entered using the key combination of CTRL,SHIFT,ENTER:

=INDEX(D:D,MATCH(1,(A1:A65535=22)*(B1:B65535=302),0))

Adjust the range size if you don't use anywhere near the entire column. No
sense in referencing down to row 65535 if you only use 100 rows at most.

Biff

>I am trying to setup a spreadsheet that will display information stored on
>a
[quoted text clipped - 9 lines]
>
> Any ideas?

Reply to this Message

Sharky23 - 21 May 2006 20:09 GMT

Thanks, all I have to do now is tweak this to access the excel sheet on the
network drive. Your assistance in this was very appreciated. I have already
tried it out a little and it works perfectly using either of the two formulas.

> Hi!
>
[quoted text clipped - 35 lines]
> >
> > Any ideas?

Reply to this Message

Biff - 21 May 2006 21:19 GMT

You're welcome. Thanks for the feedback!

Biff

> Thanks, all I have to do now is tweak this to access the excel sheet on
> the
[quoted text clipped - 45 lines]
>> >
>> > Any ideas?

Reply to this Message