How do I auto populate cells from external SS

Thanks for the reply Tony.
Unfortunately, I can't seem to get the following formula to work:
*=VLOOKUP(B13,Customers.A2:H6,2,1)*
where "Customers" is another sheet in the book.
All cells are formatted as text.
I've googled and googled but can't find why the formula doesn't work.

Thanks,

Guy

On 06/25/2010 12:52 AM, Tony Pursell wrote:
> Your question #115697 on openoffice.org in ubuntu changed:
> https://answers.launchpad.net/ubuntu/+source/openoffice.org/+question/115697
>
> Status: Open => Answered
>
> Tony Pursell proposed the following answer:
> Here is a possible way.
>
> On another sheet of the same spreadsheet do Insert> Link to External
> Data. Choose the data in the external spreadsheet. You will have to
> give a name to this data table in the external spreadsheet with Insert>
> Names> Define
>
> On the sheet which is your Invoice, use the VLOOKUP function to get the
> data from the linked table on the other sheet.
>
> I assume you have some good reason for maintaining the customer data in
> another spreadsheet, otherwise you could just maintain it on the other
> sheet.
>
> You will need to consider how big your customer table will grow as
> VLOOKUP needs to be told the extent of the whole array.
>
> Postcode will have to be the first column of the customer table for
> VLOOKUP to work.
>
>

 Hi Guy

When you say, the formula doesn't work, is that because it doesn't return anything, or because you get an error? He's the description of VLOOKUP from the Help

VLOOKUP

Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index. If the SortOrder parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. If SortOrder is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order.
The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - OpenOffice.org Calc - Calculate.

Syntax:

=VLOOKUP(SearchCriterion; Array; Index; SortOrder)

SearchCriterion is the value searched for in the first column of the array.
Array is the reference, which is to comprise at least two columns.
Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE (or zero) if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.
Handling of Empty Cells

Example:

You want to enter the number of a dish on the menu in cell A1, and the name of the dish is to appear as text in the neighbouring cell (B1) immediately. The Number to Name assignment is contained in the D1:E100 array. D1 contains 100, E1 contains the name Vegetable Soup, and so forth, for 100 menu items. The numbers in column D are sorted in ascending order; thus, the optional Sort order parameter is not necessary.

Enter the following formula in B1:
=VLOOKUP(A1; D1:E100; 2)

As soon as you enter a number in A1 B1 will show the corresponding text contained in the second column of reference D1:E100. Entering a non-existent number displays the text with the next number down. To prevent this, enter FALSE as the last parameter in the formula so that an error message is generated when a non-existent number is entered.

Hope this helps

Tony

Thanks again for the response Tony. I actually solved the problem by
simply deleting all contents of the cell and re-entering the formula.
Why this worked, I don't know.
But thanks again for your help.

My next problem is converting a date (i.e., 01/07/2010) to a text string
formatted 100710. I'm using this to generate invoices based upon the
date they are issued. My code looks thus:

=CONCATENATE(LEFT(VLOOKUP($B$13,Customers.$A$2:$I$21,7),3),TEXT(DATE(YEAR($H$9),MONTH($H$9),DAY($H$9)),"######"))

And returns *TTT40360*. Any time I try to convert the date to text I get
OO.o's "Number" value.

If you have any ideas I'm all ears - but I understand if I've used up my
allotted time.

Thanks again,

Guy

On 06/25/2010 11:55 PM, Tony Pursell wrote:
> Your question #115697 on openoffice.org in ubuntu changed:
> https://answers.launchpad.net/ubuntu/+source/openoffice.org/+question/115697
>
> Status: Open => Answered
>
> Tony Pursell proposed the following answer:
> Hi Guy
>
> When you say, the formula doesn't work, is that because it doesn't
> return anything, or because you get an error? He's the description of
> VLOOKUP from the Help
>
> VLOOKUP
>
> Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index. If the SortOrder parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. If SortOrder is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order.
> The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - OpenOffice.org Calc - Calculate.
>
> Syntax:
>
> =VLOOKUP(SearchCriterion; Array; Index; SortOrder)
>
> SearchCriterion is the value searched for in the first column of the array.
> Array is the reference, which is to comprise at least two columns.
> Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
> SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE (or zero) if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.
> Handling of Empty Cells
>
> Example:
>
> You want to enter the number of a dish on the menu in cell A1, and the
> name of the dish is to appear as text in the neighbouring cell (B1)
> immediately. The Number to Name assignment is contained in the D1:E100
> array. D1 contains 100, E1 contains the name Vegetable Soup, and so
> forth, for 100 menu items. The numbers in column D are sorted in
> ascending order; thus, the optional Sort order parameter is not
> necessary.
>
> Enter the following formula in B1:
> =VLOOKUP(A1; D1:E100; 2)
>
> As soon as you enter a number in A1 B1 will show the corresponding text
> contained in the second column of reference D1:E100. Entering a non-
> existent number displays the text with the next number down. To prevent
> this, enter FALSE as the last parameter in the formula so that an error
> message is generated when a non-existent number is entered.
>
> Hope this helps
>
> Tony
>
>

Hi Tony,
Again, problem solved. I changed the formatting on the date cell to
"Text" and then used "LEFT", "MID" & "RIGHT", as so:

=CONCATENATE(LEFT(VLOOKUP($B$13,Customers.$A$2:$I$21,7),3),RIGHT($H$9,2),MID($H$9,4,2),LEFT($H$9,2))

This gives me: TTT100701 which works very well for an invoice number.

Thanks again,

Guy

-------- Original Message --------
Subject: Re: [Question #115697]: How do I auto populate cells from
external SS
Date: Sat, 26 Jun 2010 00:18:59 +0200
From: G Davis <email address hidden>
To: <email address hidden>

Thanks again for the response Tony. I actually solved the problem by
simply deleting all contents of the cell and re-entering the formula.
Why this worked, I don't know.
But thanks again for your help.

My next problem is converting a date (i.e., 01/07/2010) to a text string
formatted 100710. I'm using this to generate invoices based upon the
date they are issued. My code looks thus:

=CONCATENATE(LEFT(VLOOKUP($B$13,Customers.$A$2:$I$21,7),3),TEXT(DATE(YEAR($H$9),MONTH($H$9),DAY($H$9)),"######"))

And returns *TTT40360*. Any time I try to convert the date to text I get
OO.o's "Number" value.

If you have any ideas I'm all ears - but I understand if I've used up my
allotted time.

Thanks again,

Guy

On 06/25/2010 11:55 PM, Tony Pursell wrote:
> Your question #115697 on openoffice.org in ubuntu changed:
> https://answers.launchpad.net/ubuntu/+source/openoffice.org/+question/115697
>
> Status: Open => Answered
>
> Tony Pursell proposed the following answer:
> Hi Guy
>
> When you say, the formula doesn't work, is that because it doesn't
> return anything, or because you get an error? He's the description of
> VLOOKUP from the Help
>
> VLOOKUP
>
> Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index. If the SortOrder parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. If SortOrder is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order.
> The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - OpenOffice.org Calc - Calculate.
>
> Syntax:
>
> =VLOOKUP(SearchCriterion; Array; Index; SortOrder)
>
> SearchCriterion is the value searched for in the first column of the array.
> Array is the reference, which is to comprise at least two columns.
> Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
> SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE (or zero) if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.
> Handling of Empty Cells
>
> Example:
>
> You want to enter the number of a dish on the menu in cell A1, and the
> name of the dish is to appear as text in the neighbouring cell (B1)
> immediately. The Number to Name assignment is contained in the D1:E100
> array. D1 contains 100, E1 contains the name Vegetable Soup, and so
> forth, for 100 menu items. The numbers in column D are sorted in
> ascending order; thus, the optional Sort order parameter is not
> necessary.
>
> Enter the following formula in B1:
> =VLOOKUP(A1; D1:E100; 2)
>
> As soon as you enter a number in A1 B1 will show the corresponding text
> contained in the second column of reference D1:E100. Entering a non-
> existent number displays the text with the next number down. To prevent
> this, enter FALSE as the last parameter in the formula so that an error
> message is generated when a non-existent number is entered.
>
> Hope this helps
>
> Tony
>
>

Hi Guy

Now that everything is working OK, can you go to

https://answers.launchpad.net/ubuntu/+source/openoffice.org/+question/115697

and mark the question as Solved.

Thanks

Tony

Thanks Tony Pursell, that solved my question.