Solved

Test for empty cell in Excell

Posted on 2008-06-15
26
1,250 Views
Last Modified: 2010-04-21
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
Comment
Question by:Net_Worker
  • 15
  • 11
26 Comments
 
LVL 5

Author Comment

by:Net_Worker
Comment Utility
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

0
 
LVL 9

Expert Comment

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Net_Worker
Comment Utility
Sorry it doesn't display the formula it errors on the parenthesis.
0
 
LVL 9

Expert Comment

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Expert Comment

by:Derek Schauland
Comment Utility
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
0
 
LVL 5

Author Comment

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:Derek Schauland
Comment Utility
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

by:Net_Worker
Comment Utility
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

by:
Derek Schauland earned 250 total points
Comment Utility
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

by:Net_Worker
Comment Utility
I still haven't quite finished it but you got me on the right track.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now