Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not listing more than a range of rows
I have two sheets, one has my equipment database and the other is the results
"Quote sheet". in my Quote sheet I am using the: =IF(ROW()-1MAX('Equip Database'!A:A),"",ROW()-7) in the first colum and row 8 (A8), then use the: =IF($A8="","",VLOOKUP($A8,'Equip Database'!A7:L1000,COLUMN(),0)) in the rest of the cells on the same row (B8, C8, D8 etc.) if I enter a qty in the Equip Database Colum A, then I populate the rows in the the Quote sheet. But it only retirves and up to row 27 of the Equip Database rows. It is limited to around that phisical row and not the total amount of where a qty is entered. Ex: in Equip Database I enter qty in row 25 then it is sent to my Quote sheet. If I enter a qty in row 30 it does not get sent to the Quote sheet. But if I enter a qty in row 10, 17 & 20 then it does. Any sugestions? Thanks in advance, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not listing more than a range of rows
Try:
=IF($A8="","",VLOOKUP($A8,'Equip Database'!$A$7:$L$1000,COLUMN(),0)) Regards Trevor "Richard P" wrote in message ... I have two sheets, one has my equipment database and the other is the results "Quote sheet". in my Quote sheet I am using the: =IF(ROW()-1MAX('Equip Database'!A:A),"",ROW()-7) in the first colum and row 8 (A8), then use the: =IF($A8="","",VLOOKUP($A8,'Equip Database'!A7:L1000,COLUMN(),0)) in the rest of the cells on the same row (B8, C8, D8 etc.) if I enter a qty in the Equip Database Colum A, then I populate the rows in the the Quote sheet. But it only retirves and up to row 27 of the Equip Database rows. It is limited to around that phisical row and not the total amount of where a qty is entered. Ex: in Equip Database I enter qty in row 25 then it is sent to my Quote sheet. If I enter a qty in row 30 it does not get sent to the Quote sheet. But if I enter a qty in row 10, 17 & 20 then it does. Any sugestions? Thanks in advance, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not listing more than a range of rows
Tevor,
Thanks for your suggestion, but I think it has something to do with the sheet settings or something like that. I say this becouse I did a resample of two sheets and it works. The only difference is that on some of the rows I merged some cells to use as headings. I really want to keep the previous sheets since they have about 800 rows of info. Any thoughts? Richard P. "Trevor Shuttleworth" wrote: Try: =IF($A8="","",VLOOKUP($A8,'Equip Database'!$A$7:$L$1000,COLUMN(),0)) Regards Trevor "Richard P" wrote in message ... I have two sheets, one has my equipment database and the other is the results "Quote sheet". in my Quote sheet I am using the: =IF(ROW()-1MAX('Equip Database'!A:A),"",ROW()-7) in the first colum and row 8 (A8), then use the: =IF($A8="","",VLOOKUP($A8,'Equip Database'!A7:L1000,COLUMN(),0)) in the rest of the cells on the same row (B8, C8, D8 etc.) if I enter a qty in the Equip Database Colum A, then I populate the rows in the the Quote sheet. But it only retirves and up to row 27 of the Equip Database rows. It is limited to around that phisical row and not the total amount of where a qty is entered. Ex: in Equip Database I enter qty in row 25 then it is sent to my Quote sheet. If I enter a qty in row 30 it does not get sent to the Quote sheet. But if I enter a qty in row 10, 17 & 20 then it does. Any sugestions? Thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting rows based on criteria | Excel Discussion (Misc queries) | |||
Referencing a range of columns and rows with the IF function | Excel Worksheet Functions | |||
Hide all rows within a range except one? | Excel Discussion (Misc queries) | |||
Define a range containing the first 10 rows of a filtered list | Excel Worksheet Functions | |||
Excel - return a picture or range rows as the result of a formula | Excel Worksheet Functions |