Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a function
Hi
I have excel sheets with data as shown in the below example sheet1 sheet2 sheet3 sheet4 sheet5 c5 10 c5 15 c5 12 c5 20 c5 empty c6 9 c6 19 c6 11 c6 20 c6 empty c7 10 c7 15 c7 12 c7 20 c7 empty c8 9 c8 19 c8 11 c8 empty c8 empty c9 10 c9 15 c9 12 c9 empty c 9 empty c10 9 c10 7 c10 3 c10 empty c10 empty c11 10 c11 5 c11 4 c11 empty c11 empty I need a function which go through these sheets and return the value of last cell before the empty cells, in this case the value of c7 in sheet4. The empty cells will have data in future. Can I create a chart for the above data and how it will be Axis label range. Thank you for help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a function
One way to model it up for this core issue:
.. need a function which go through these sheets and return the value of last cell before the empty cells, in this case the value of c7 in sheet4 .. In a new sheet, Prepare a quick summary table to show what's in the 5 sheets: List the 5 sheetnames in B1:F1 (sheet1, €¦ sheet5) List the 7 cell refs in A2:A8 (c5, €¦ c11) Place in B2: =INDIRECT("'"&B$1&"'!"&$A2) Copy B2 across/fill down to F8 to populate Blank/Empty source cells will be returned as zeros Then place this in say, H2: =OFFSET($B$2,MOD(ROWS($1:1)-1,7),INT((ROWS($1:1)-1)/7),) Copy H2 down to H36*. This extracts the summary's contents into a single column, tacking sequentially col contents from Sheet1, then Sheet2's and so on to Sheet5's. *by 35 rows as the summary table contains 7 x 5 = 35 cells Now you can place in say, I2: =LOOKUP(2,1/(H2:H36<0),H2:H36) to retrieve the required "value of the last cell before the empty cells" (the value of c7 in sheet4 in your example) -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Walley" wrote: I have excel sheets with data as shown in the below example sheet1 sheet2 sheet3 sheet4 sheet5 c5 10 c5 15 c5 12 c5 20 c5 empty c6 9 c6 19 c6 11 c6 20 c6 empty c7 10 c7 15 c7 12 c7 20 c7 empty c8 9 c8 19 c8 11 c8 empty c8 empty c9 10 c9 15 c9 12 c9 empty c 9 empty c10 9 c10 7 c10 3 c10 empty c10 empty c11 10 c11 5 c11 4 c11 empty c11 empty I need a function which go through these sheets and return the value of last cell before the empty cells, in this case the value of c7 in sheet4. The empty cells will have data in future. Can I create a chart for the above data and how it will be Axis label range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |