Is it possible to get a row to populate from a table on another page upon searching data entered in a cell?

Hi Forbzy,

One name for what you're describing is a 'breakout table', and a search of the Numbers community will bring up several examples. Here's a link to one. If my post there fits what you want, you'll find a more complete description in the post linked in that one.

https://discussions.apple.com/thread/3804118?answerId=17857818022#17857818022

Regards,

Barry

HI John,

Well past midnight,and too late to deal with this in detail, so this just touches on the main points.

My expertise is with the Mac version of Numbers, as is true with most of the regulars in this community. I'm vaguely aware with some of the limitations of Numbers for iOS, but have never used it.

Retrieving a row of data from a data table and placing it onto a second table in the same document is fairly simple in Numbers using VLOOKUP. The second table may be placed on a separate Sheet, which can be printed independent of the sheet containing the main table, but the table cannot be in a separate Document (ie. a different file).

A table with 700 lines of data is doable on the Mac version of Numbers, and probably within the limits of the iOS version.

700 items on a pop-up menu seems terribly unwieldy to me. If you can accurately enter the individual codes manually, I suspect that would be speedier than using a pop-up.

I can run you through the process on the Mac version of Numbers, which may provide a workable solution for the iOS version. Any tweaking, though, would probably require avisit to the iWork for iOS community.

Regards,

Barry

Hi John,

Sorry for the delay.

A VLOOKUP table is a rectangular array of cells, which may be a separate Table (preferred) or a block of cells on a larger Table. The column containing the search-for values (the codes, in your case) must be the leftmost column of the lookup table.

columns-range is the range of columns making up the lookup table. The range starts with the column containing the search-for values, and ends with the last column to the right containing data to be searched.

return-column is the column of the lookup table from which the data is to be returned. It is specified by a number. 1 is the search-for column, 2 the next column to the right, etc.

close-match determines whether the formula will accept a close match to the search term, or will accept only an exact match. A 'close match' is defined as 'the largest value less than or equal to the search-for value.'
For text values, 'less than' is read as 'alphabetically before' the search value.

Here's a small sample table set showing an example:

In practice, the data table (top) would be on a separate Sheet from the smaller Search table, and that sheet's name would be included in the cell range reference shown in the formula (unless the Data table's name, "Data" was used only for this table within the document).

There's a single formula, entered in the selected cell (B2) of the Search table, then filled right and down to all cells below and to the right of B2:

B2: =IFERROR(VLOOKUP($A2,Data :: $A:$F,COLUMN(),FALSE)," - ")

The FALSE value for close-match means only an exact match will be accepted. If no exact match is found, as in row 4 of the search table, VLOOKUP will return an error. The error is trapped by IFERROR, and the text string shown is inserted in the cell. Note that VLOOKUP is not case sensitive.

The COLUMN() function returns the number of the column containing the formula, and provides an automatic means of incrementing the return column as the formula is filled right.

Can't supply any details on how to enter a formula in Numbers for iOS, as I've not done that on an iOS device.

Regards,

Barry

Great discussion on VLookup. I'm able to implement this solution, and it works great.

My question... is it possible to do VLookup into a 2nd document.

ie. I would love to have an order document (for input) and a product database document (for lookup).

Thanks,

Eddie

John,

Thanks for the quick response. That's what I have found as well (was hoping otherwise).

Here's a seemingly unrelated question (though it would still solve the same problem).

When saving a spreadsheet as a PDF (iPad version), is there any way to only save the current sheet?

ie. I could have my order page in sheet 1, product database in sheet 2 for vlookup, and then save sheet 1 only for sending within my organization.

Thanks for your help,

Eddie

Eddie,

The thing to do would be to Print > PDF > Save as PDF, specifying just the Sheet for within your organization for printing.

Jerry