Solved

Test for empty cell in Excell

Posted on 2008-06-15
Medium Priority
1,301 Views
I have a workbook with multiple worksheets. Each sheet is a detailed page with dates and times. Sheet 1 has been added as a quick view of each page. The rows of Sheet 1, column A has the name of each of the multiple sheets. I need a formula probably in vba code for Sheet 1 Column B and C. Currently the formula is input into each sheet.

For each populated cell in Sheet 1 column A, look at the value in the row of column A (name). Then go to the named sheet and test for the next available empty cell in Column B, read the value from the cell above insert that into the formula below which will be in column B and C on Sheet 1.

The intention is to move columns C and D from the multiple sheets and to just show the equivalent value on Sheet 1 in Columns B and C. See below.

Current - Sheet 'n'
A            B              C                                                          D
1 12/06/08  | 12:15  |     =IF(B2="","",(B2+TIME(12,0,0)))   |   =IF(B2="","",B2+(TIME(20,0,0)))

Sheet 1
A                    B                           C
1| Name   |         Next                  | Local Time
2| sheetn   Value of sheetn:Bn     Value of sheetn:Dn

I hope this makes sense.
0
Question by:Net_Worker
• 15
• 11

LVL 5

Author Comment

ID: 21788090
Sheet 1
A                    B                           C
1| Name   |         Next                  | Local Time
2| sheetn   Value of sheetn:Cn     Value of sheetn:Dn

0

LVL 9

Expert Comment

ID: 21788494
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?

0

LVL 5

Author Comment

ID: 21788534
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. :)
0

LVL 9

Expert Comment

ID: 21788668
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
0

LVL 5

Author Comment

ID: 21788813
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.
0

LVL 5

Author Comment

ID: 21788816
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.
0

LVL 9

Expert Comment

ID: 21789932
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
0

LVL 5

Author Comment

ID: 21790968
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?
0

LVL 9

Expert Comment

ID: 21793124
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.
0

LVL 5

Author Comment

ID: 21793761
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.
0

LVL 5

Author Comment

ID: 21793795
Sorry it doesn't display the formula it errors on the parenthesis.
0

LVL 9

Expert Comment

ID: 21793879
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
0

LVL 5

Author Comment

ID: 21793926
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.
0

LVL 9

Expert Comment

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

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

LVL 5

Author Comment

ID: 21795633
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?
0

LVL 9

Expert Comment

ID: 21795681
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
0

LVL 5

Author Comment

ID: 21800105
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.
0

LVL 5

Author Comment

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

LVL 9

Expert Comment

ID: 21802869
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..
0

LVL 5

Author Comment

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

LVL 9

Expert Comment

ID: 21803054
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
0

LVL 5

Author Comment

ID: 21803110
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?
0

LVL 9

Expert Comment

ID: 21803640
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
0

LVL 5

Author Comment

ID: 21809764
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.
0

LVL 9

Accepted Solution

Derek Schauland (Microsoft MVP) earned 750 total points
ID: 21813822
Sure you can copy and paste in a macro

an example is below:

Select the cells used in the worksheet that contains the template

Range("a1:y39").Select

'Copy that selection
Selection.Copy

'Select the worksheet to paste the selection into (worksheets(i) where i is the number of the newest dynamic sheet)
Worksheets(i).Select

'select the first cell of the dynamic sheet so you have somewhere to paste your templae
Range("a1").Select

'paste the clipboard content into the now active sheet
ActiveSheet.Paste

Note the range I chose was for an example. you would use a range that covers the entire template
0

LVL 5

Author Closing Comment

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not tâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
Suggested Courses
Course of the Month12 days, 19 hours left to enroll