Test for empty cell in Excell

Oops, made a little mistake.
Sheet 1
      A                    B                           C
1| Name   |         Next                  | Local Time
2| sheetn   Value of sheetn:Cn     Value of sheetn:Dn

I am not sure I fully understand the question

In column A  you want to list the names of all of the other worksheets in the workbook

then in column B you want to list the next blank cell found on each worksheet or the next time on the listed sheet?

Well both really.

I will enter column A in sheet 1 manually and create a sheet with that name. For this example its called sheetn. Then in sheet 1 column B I want to look up the last value in sheetn column B. I then want to put the result of the formula from sheetn columns C and D into sheet 1 colums B and C respectively. Sheetn has many lines in it and I am only interested in the last entry of sheetn when viewing in sheet 1 so I need to look for the last row used.

Example if sheet 1 has the value sheetn in A5 and the last row used in sheetn is 45 then I want the formula to calculate from sheetn:B:45 and display it in sheet1:B:5 and C:5 using the formula for each rows that I have shown above that are currently in sheetn:c and sheetn:d

By the way I also noticed that my original post shows row 1 being used and formula is working on row 2. Obviously this should be the same row.

Hope this make it a bit clearer. If not ask me again. :)

Ok so in sheet 1 we are looking for the following

   Col A |      Col B    | col C
sheetN | sheetn:Bn| Sheetn:Cn

The sheetname cell will likely need a macro or modules in VBA to be achieved in column A: http://www.ozgrid.com/VBA/variable-worksheet-names.htm

but the formula for the value of the last used cell in a column isnt too bad.  In the example below I am referencing sheet2 from sheet1 and assuming column B on sheet 1 will match column B on sheet 2

You will need to use an Array (or CSE) formula to acheive the needed resutl. Note: Array Formulas are entered by keying (or pasting) the formula in and clicking at the end of the formula bar, then pressing Ctrl+Shift+Enter if done correctly Excel will add brackets around the formula.

Formula for Sheet2 Col B is below

=INDEX(Sheet2!B1:Sheet2!B65000,MAX(IF(LEN(Sheet2!B1:Sheet2!B65000)>0,ROW(Sheet2!B1:Sheet2!B65000),0)),1)

Hope this helps

Derek

I'll take a look but to clarify as it may alter your response) your close but not quite.

On sheet 1 I am look for
ColA     |                                   ColB        |                          ColC
sheetN | =sheetN!(Bn+TIME(12,0,0)) | =I(sheetN!Bn+(TIME(20,0,0))

Nothing actually needs to be done to sheetN. I need to somehow make the sheet name a variable read from the contents of sheet1!colA:rowN and use that variable to get a value to insert into the formula for each of colB and colC above, using the last line in sheetN!colB. I figured checking for a null value and moving up 1 place to get the value. How to code the variable and the null check sequence, I am not sure.

DOH! I should proof read before posting.

ColA     |                                   ColB        |                          ColC
sheetN | =sheetN!(Bn+TIME(12,0,0)) | =sheetN!Bn+(TIME(20,0,0))

Thats better. I am sure you would have picked it up anyway.

Aren't you getting the values for columns B and C from another sheet?

that is the only part of the previous formula that references sheet (n)

It is checking the sheet for the last value in a column and returning that value to sheet 1 column B or C

I have used the formula on a test as I have not yet worked out how to use the dynamic reference however, I have tried to work it out but this formula gives a result of 0 but should be 09:27. Both the referenced cell and the used cell are formatted as time. Any clues?

When you select the cell containing the formula on sheet 1 does the formula have brackets around it like this

{
=INDEX(Sheet2!B1:Sheet2!B65000,MAX(IF(LEN(Sheet2!B1:Sheet2!B65000)>0,ROW(Sheet2!B1:Sheet2!B65000),0)),1)}

or does it just display the formula?  Without the brackets the calculation will not work as it needs to be an array formula.

Also you will need to format the cells in sheet 1 containing the formula as time so they will correctly display the value when returned.

No parenthesis on the formula. I added them and now it shows the formula in the cell. The cell is formatted as time already in both sheets.

Sorry it doesn't display the formula it errors on the parenthesis.

The brackets should not need to be typed in... when you paste the formula instead of hitting enter to insert it, press Control+Shift+Enter

that should add the brackets and create an array formula

Yes that worked. Now how to make that an automated process? Sheets are added and removed daily and so sheet1 needs to be easy to edit and update.

To get a list of worksheet names that is dynamic you will probably need a macro fired on load.

This link explains how that works and also has a downloadable example

http://www.ozgrid.com/News/oct-2006.htm#ExcelTips

I think with this guide I should be able to work the required code. I just have concern about the ctrl+shift+enter sequence to create the array. When I entered the parenthesis it did not create the array so how do I do that in code?

I would copy and paste the formula all the way down the columns as needed for ease of use

however to add this to a macro you could do something like

cells(x).formulaarray= "<Formula goes here>"

within the macro generating the sheet names to place the value fetch into the row as the sheet name is populated

OK we're making progress. I have followed the guide you have given me and the dynamic list is all working great but it only works if I create a new blank worksheet. I need the function to work by copying a template. The template has a preconfigured chart based on the data that is entered into it. It also uses panes and the chart is static at the top of the sheet but aside from that it is a standard worksheet.

The guide states that this only works on standard worksheets. What does that definition mean?

From here I will need to create a macro button that will;
1.Open a dialog prompt to enter the new sheet name
2.Copy and rename the template from the dialog variable
3.Add the sheet name to the dynamic list
4.Hyperlink that name in the list to the sheet.

Can you offer any code snippets that can do what I need based on the guide you have given me.

Also from your last post, could you give me an example? I get what you mean but not sure how the code would look.

This is the best example I could find for getting an array formula from a macro to the screen

Range("b3").Select
Selection.FormulaArray = "=MIN(if(a2:a100>0,a2:a100))"

Cell B3 on sheet containing macro will have the value created by the array formula listed above.  Does not require brackets in Macro because it sets the formulaArray property of the selection object.  The selection object becomes cell b3.

As for the macro:

1. Range("A1") = InputBox("Enter value here") will put the text entered into the input box in cell A1

2. something like the following:

dim varname as string

   Range("A1") = varname

Worksheets(2).Name = varname

creates a variable, then you could populate the variable with varname = inputbox("enter data") and use the variable to populate A1 and the sheetname.  the 2 specifies worksheet 2 of the worksheets array, something like

dim i as int

i = 1

x = worksheets.count - i

worksheets(x).name = varname should rename the worksheet that is next to last each time it is executed perhaps in a for loop.

3. could integrate this macro into dynamic list macro so they all happen together... perhaps using the hyperlink formula to get the link

I was thinking.. you might create the dynamic list in a different column, like ZZ and hide it, then use the hyperlink() formula to link to the sheet with the name in column ZZ..

I'll give that a try. Any ideas on what I need to change to make it work copying my template?

might be able to do a move/copy

leave the template sheet as last in workbook, the use macro coding to create the new sheet and name it, and then copy the template contents only into new named sheet

I tried a move/copy which failed. If I create a new worksheet it shows up in the list fine. Would I be best to use the macro recorder to select all > copy > past or can this be done in code. Sorry for all the lame questions but I haven't done any VBA for about 4 years and I am very rusty. I remember that mouse clicks did not get recorded with the macro recorder. Is that still the case?

You could use VBA to build a macro...

when it creates the sheet, then copy the template and paste to the newly created sheet.  If you leave the template as the last sheet, maybe called template it should be able to whip right through it each time the macro is fired.

I would not use the macro recorder... since you already have a macro built for the dynamic sheet names you could add on to it or add another macro action for a button click to fire the copy/paste

something like

dim iCount as integer

icount = worksheets.count

worksheets(template sheet number).copy After:=worksheets(icount)

should plop a copy of the sheet after the last sheet... might be able to paste into existing sheet or rename the newly copied sheet to something useful and use that in the dynamic list

Yes but thats what I mean. With the code from the website you gave me, it doesn't populate the list if I do copy/move. I was going to do exactly what you said just above but thats where I come unstuck. Is there a function that can select the entire template contents and copy into a new blank sheet? Unless I am doing it wrong I think this will be the only way while still using the dynamic list.

I still haven't quite finished it but you got me on the right track.