Need a function

[Walley]

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

 

[Max]

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
---