lookup syntax problem

[Richie Richardson]

I am trying to look up data that is located in another workbook on a network
drive. I already have the reports location referenced but I don't know the
proper syntax to join a lookup statement to the report address so the total
formula will look up a particular value in a particular row in the other
workbook and place that value in the cell in my workbook... if that makes any
sense at all.

 

[Frank Kabel]

Hi
maybe you can give an example (plain text please - no
attachment). And describe the values in your files and
what you exactly want to lookup

 

[Arvi Laanemets]

Hi

To access cell A1 of some sheet in workbook on shared network resource:
='\\Server\SharedResource\Path\[FileName.xls]SheetName'!$A$1
where 'Server' is the network name for server/computer, 'SharedResourc' is
share name for shared folder, and 'Path' lists subfolders (when there are
some). An Example:
On computer 'Comp1' some folder is shared as 'ExcelShares'. In subfolder
'John' is an excel workbook 'JohnsData', with a table on sheet Data. To get
the value from cell A1, the formula will be
='\\Comp1\ExcelShares\John\[JohnsData.xls]Data'!$A$1

I myself prefer to map the shared resource. With previous example I map
ExcelShares on computer Comp1 p.e. as 'J', and set the share to be reopened
when computer is restarted. Now the formula will be:
='J:\John\[JohnsData.xls]Data'!$A$1

You can refer a range too:
=SUM('J:\John\[JohnsData.xls]Data'!$A$1:$A$1000)

You can use such links as parameters for most of formulas. One Exception is
INDIRECT, which returns error, whenever the source file is closed (some
other functions too, when I remember correctly, but I don't have list of
them at moment ready)

 

[Richie Richardson]

Hi Arvi

Thanks for that. Incidentally, I don't suppose you would know how to alter
the reference to look for a particular word in a column and then return the
value of a specified cell in that row.

The purpose for this would be to make the spreadsheet more robust in the
sense that it wouldn't matter if data sets moved about the referenced
spreadsheet as my spreadsheet would be looking for a word in a cell rather
than a specific cell.

The reason for this is due to the fact that I don't have control over the
spreadsheet I am getting my data from and the people that do tend to switch
their data sets around on a fairly regular basis which throws all the links
in my spreadsheet out.

Thanks again

Richie Richardson

Hi

To access cell A1 of some sheet in workbook on shared network resource:
='\\Server\SharedResource\Path\[FileName.xls]SheetName'!$A$1
where 'Server' is the network name for server/computer, 'SharedResourc' is
share name for shared folder, and 'Path' lists subfolders (when there are
some). An Example:
On computer 'Comp1' some folder is shared as 'ExcelShares'. In subfolder
'John' is an excel workbook 'JohnsData', with a table on sheet Data. To get
the value from cell A1, the formula will be
='\\Comp1\ExcelShares\John\[JohnsData.xls]Data'!$A$1

I myself prefer to map the shared resource. With previous example I map
ExcelShares on computer Comp1 p.e. as 'J', and set the share to be reopened
when computer is restarted. Now the formula will be:
='J:\John\[JohnsData.xls]Data'!$A$1

You can refer a range too:
=SUM('J:\John\[JohnsData.xls]Data'!$A$1:$A$1000)

You can use such links as parameters for most of formulas. One Exception is
INDIRECT, which returns error, whenever the source file is closed (some
other functions too, when I remember correctly, but I don't have list of
them at moment ready)

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

I am trying to look up data that is located in another workbook on a network
drive. I already have the reports location referenced but I don't know the
proper syntax to join a lookup statement to the report address so the total
formula will look up a particular value in a particular row in the other
workbook and place that value in the cell in my workbook... if that makes any
sense at all.

 

[Arvi Laanemets]

Hi

When the lookup value is a cell entry, then it's relatively easy:
=MATCH(LookupValue,LookupRange,0)
does return the position of LookupValue in LookupRange.

To get a cell value in same row with LookupValue, use OFFSET function
=OFFSET(C1,MATCH("John",A2:A100,0),0)
returns a value in column C from row where in column A is the first
occurence of "John".


Arvi laanemets

Hi Arvi

Thanks for that. Incidentally, I don't suppose you would know how to alter
the reference to look for a particular word in a column and then return the
value of a specified cell in that row.

The purpose for this would be to make the spreadsheet more robust in the
sense that it wouldn't matter if data sets moved about the referenced
spreadsheet as my spreadsheet would be looking for a word in a cell rather
than a specific cell.

The reason for this is due to the fact that I don't have control over the
spreadsheet I am getting my data from and the people that do tend to switch
their data sets around on a fairly regular basis which throws all the links
in my spreadsheet out.

Thanks again

Richie Richardson

Hi

To access cell A1 of some sheet in workbook on shared network resource:
='\\Server\SharedResource\Path\[FileName.xls]SheetName'!$A$1
where 'Server' is the network name for server/computer, 'SharedResourc' is
share name for shared folder, and 'Path' lists subfolders (when there are
some). An Example:
On computer 'Comp1' some folder is shared as 'ExcelShares'. In subfolder
'John' is an excel workbook 'JohnsData', with a table on sheet Data. To get
the value from cell A1, the formula will be
='\\Comp1\ExcelShares\John\[JohnsData.xls]Data'!$A$1

I myself prefer to map the shared resource. With previous example I map
ExcelShares on computer Comp1 p.e. as 'J', and set the share to be reopened
when computer is restarted. Now the formula will be:
='J:\John\[JohnsData.xls]Data'!$A$1

You can refer a range too:
=SUM('J:\John\[JohnsData.xls]Data'!$A$1:$A$1000)

You can use such links as parameters for most of formulas. One Exception is
INDIRECT, which returns error, whenever the source file is closed (some
other functions too, when I remember correctly, but I don't have list of
them at moment ready)

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)



message news:[email protected]...

I am trying to look up data that is located in another workbook on a network
drive. I already have the reports location referenced but I don't

know
the

proper syntax to join a lookup statement to the report address so the total
formula will look up a particular value in a particular row in the other
workbook and place that value in the cell in my workbook... if that

makes
any

sense at all.

 

[Richie Richardson]

Hi Arvi

One last question. What is the proper syntax for combining both of these
things? ie;
grabbing data from another workbook on a network drive and using the offset
& match function to match a specific word(s) in a column from that workbook
and return a value from a cell in that row to my workbook.

I keep trying to put one and one together and it always seems to equal a
circular reference or an error!

Thanks

Richie Richardson

Hi

When the lookup value is a cell entry, then it's relatively easy:
=MATCH(LookupValue,LookupRange,0)
does return the position of LookupValue in LookupRange.

To get a cell value in same row with LookupValue, use OFFSET function
=OFFSET(C1,MATCH("John",A2:A100,0),0)
returns a value in column C from row where in column A is the first
occurence of "John".


Arvi laanemets

Hi Arvi

Thanks for that. Incidentally, I don't suppose you would know how to alter
the reference to look for a particular word in a column and then return the
value of a specified cell in that row.

The purpose for this would be to make the spreadsheet more robust in the
sense that it wouldn't matter if data sets moved about the referenced
spreadsheet as my spreadsheet would be looking for a word in a cell rather
than a specific cell.

The reason for this is due to the fact that I don't have control over the
spreadsheet I am getting my data from and the people that do tend to switch
their data sets around on a fairly regular basis which throws all the links
in my spreadsheet out.

Thanks again

Richie Richardson

Hi

To access cell A1 of some sheet in workbook on shared network resource:
='\\Server\SharedResource\Path\[FileName.xls]SheetName'!$A$1
where 'Server' is the network name for server/computer, 'SharedResourc' is
share name for shared folder, and 'Path' lists subfolders (when there are
some). An Example:
On computer 'Comp1' some folder is shared as 'ExcelShares'. In subfolder
'John' is an excel workbook 'JohnsData', with a table on sheet Data. To get
the value from cell A1, the formula will be
='\\Comp1\ExcelShares\John\[JohnsData.xls]Data'!$A$1

I myself prefer to map the shared resource. With previous example I map
ExcelShares on computer Comp1 p.e. as 'J', and set the share to be reopened
when computer is restarted. Now the formula will be:
='J:\John\[JohnsData.xls]Data'!$A$1

You can refer a range too:
=SUM('J:\John\[JohnsData.xls]Data'!$A$1:$A$1000)

You can use such links as parameters for most of formulas. One Exception is
INDIRECT, which returns error, whenever the source file is closed (some
other functions too, when I remember correctly, but I don't have list of
them at moment ready)

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)



message I am trying to look up data that is located in another workbook on a
network
drive. I already have the reports location referenced but I don't know
the
proper syntax to join a lookup statement to the report address so the
total
formula will look up a particular value in a particular row in the other
workbook and place that value in the cell in my workbook... if that makes
any
sense at all.

 

[Arvi Laanemets]

Hi

=OFFSET('C:\My
Documents\[SourceWorkbook.xls]SourceSheet'!B1,MATCH("John",'C:\My
Documents\[SourceWorkbook.xls]SourceSheet'!A2:A100,0),0)

(replace path, workbook and sheet names, etc according your needs)


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)

Hi Arvi

One last question. What is the proper syntax for combining both of these
things? ie;
grabbing data from another workbook on a network drive and using the offset
& match function to match a specific word(s) in a column from that workbook
and return a value from a cell in that row to my workbook.

I keep trying to put one and one together and it always seems to equal a
circular reference or an error!

Thanks

Richie Richardson

Hi

When the lookup value is a cell entry, then it's relatively easy:
=MATCH(LookupValue,LookupRange,0)
does return the position of LookupValue in LookupRange.

To get a cell value in same row with LookupValue, use OFFSET function
=OFFSET(C1,MATCH("John",A2:A100,0),0)
returns a value in column C from row where in column A is the first
occurence of "John".


Arvi laanemets


message news:[email protected]...

Hi Arvi

Thanks for that. Incidentally, I don't suppose you would know how to alter
the reference to look for a particular word in a column and then

return
the

value of a specified cell in that row.

The purpose for this would be to make the spreadsheet more robust in the
sense that it wouldn't matter if data sets moved about the referenced
spreadsheet as my spreadsheet would be looking for a word in a cell rather
than a specific cell.

The reason for this is due to the fact that I don't have control over the
spreadsheet I am getting my data from and the people that do tend to switch
their data sets around on a fairly regular basis which throws all the links
in my spreadsheet out.

Thanks again

Richie Richardson

:

Hi

To access cell A1 of some sheet in workbook on shared network resource:
='\\Server\SharedResource\Path\[FileName.xls]SheetName'!$A$1
where 'Server' is the network name for server/computer,

'SharedResourc'
is

share name for shared folder, and 'Path' lists subfolders (when

there
are

some). An Example:
On computer 'Comp1' some folder is shared as 'ExcelShares'. In subfolder
'John' is an excel workbook 'JohnsData', with a table on sheet Data.

To
get

the value from cell A1, the formula will be
='\\Comp1\ExcelShares\John\[JohnsData.xls]Data'!$A$1

I myself prefer to map the shared resource. With previous example I map
ExcelShares on computer Comp1 p.e. as 'J', and set the share to be reopened
when computer is restarted. Now the formula will be:
='J:\John\[JohnsData.xls]Data'!$A$1

You can refer a range too:
=SUM('J:\John\[JohnsData.xls]Data'!$A$1:$A$1000)

You can use such links as parameters for most of formulas. One

Exception
is

INDIRECT, which returns error, whenever the source file is closed (some
other functions too, when I remember correctly, but I don't have list of
them at moment ready)

--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)



"Richie Richardson" <Richie [email protected]>

wrote
in

message I am trying to look up data that is located in another workbook on a
network
drive. I already have the reports location referenced but I don't know
the
proper syntax to join a lookup statement to the report address so the
total
formula will look up a particular value in a particular row in the other
workbook and place that value in the cell in my workbook... if

that
makes

any
sense at all.