Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to find the data of a next cell in a list
The Workbook has 2 sheets. On Sheet 1, Column A is a list of Warehouse
locations, (1A1A is in Cell A3, 1A1B is in Cell A4, 1A1C is in Cell A5 and so on). On Sheet 2 I am setting up Lable Location Templates which span Cells A1:K35, with A1 containing the text of Sheet1!A3 (i.e. 1A1A) I want Sheet2!A36 to display the data from Sheet1!A4 (i.e. 1A1B) and for each copy down the page to do likewise. I was hoping there was some kind of NEXT type formula for finding a value in a list and then returning the next value from said list. The only other alternative I can see is hlookup but I might as well type in all the values as I would have to change the row value in the formula each time for this to function correctly. Any help is appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to find the data of a next cell in a list
In Sheet2,
Put instead in A1: =INDIRECT("Sheet1!A"&INT((ROW(A1)-1)/35)+3) Copy down as far as required The above will place the contents of: Sheet1!A3 within A1:A35 then: Sheet1!A4 within A36:A70 then: Sheet1!A4 within A71:A105 and so on .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "merrimop" wrote: The Workbook has 2 sheets. On Sheet 1, Column A is a list of Warehouse locations, (1A1A is in Cell A3, 1A1B is in Cell A4, 1A1C is in Cell A5 and so on). On Sheet 2 I am setting up Lable Location Templates which span Cells A1:K35, with A1 containing the text of Sheet1!A3 (i.e. 1A1A) I want Sheet2!A36 to display the data from Sheet1!A4 (i.e. 1A1B) and for each copy down the page to do likewise. I was hoping there was some kind of NEXT type formula for finding a value in a list and then returning the next value from said list. The only other alternative I can see is hlookup but I might as well type in all the values as I would have to change the row value in the formula each time for this to function correctly. Any help is appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to find the data of a next cell in a list
Typo in line:
then: Sheet1!A4 within A71:A105 should have read as: then: Sheet1!A5 within A71:A105 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel |