When you are making a formula say
=A5 + A6+A7
when you are doing this in cell D423, it sometimes gets annoying to have to either type in A6 and A7 manually or to navigate to A6 and A7.
I know there is a key you can hold down after doing A5 that keeps you on A5 even after putting in the + sign, so that you don't have to navigate all the way back there.
I can't figure it out!
Does anyone know what this is?
Please help!!
Thanks,
Michael
So I used Excel Magic Trick 185 to dynamically pull content from a spreadsheet: http://www.youtube.com/watch?v=6bGKhbUYOas
However,
It is only working for a few of my columns though and I am getting reference errors, http://www.mediafire.com/?gje8srbp8x2j48w
Can anyone check into my spreadsheet and help me understand why I get these ref errors? Is it because I have spaces in the column names or within the cells? I know a macro is probably a better way to do this, or an auto-filter, but I need it to dynamically update. Any ideas on how to fix this problem or a better way to solve it?
Thanks!
To all the Macro Experts, here is my problem. I created a macro with Funtion and Object to create folders for each cell in a sheet.
About the worksheet:
- Usually the recipient name list is on sheet 2, with a heading (different on most occasions)
WHat I am trying to do:
1. I was the macro to ask User for a specific Folder Location (works fine with the Object and Function)
2. Ask user for what sheet has the names? I have a box pop up with a list of the Sheets in that workbook. (Works fine)
3. Ask user the Cell address of the Heading. For example: Recipient Name in cell A6. Asks for A6 as I use this as reference for the range to create folders.
4. Now here is my problem. Creating folders works great. The problem I am facing is: Duplicate Names.
The industry I am in, I am sure to run in to 2 people with the same First and Last name. Their Identification numbers would be different so would not be an issue, and thus I do not use that in the Macro.
I want the ability in the Macro that it will create a folder for the 2nd name by like John Doe 2
Implying, when the macro runs with two John Doe in the List, it will create folders with the names: John Doe and John Doe 2.
Here is the code for reference:
-----------------------------------------------
-----------------------------------------------
-----------------------------------------------
Option Explicit
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Sub Folder()
'
' Folder Macro
'
' Keyboard Shortcut: Ctrl+n
'
Dim Firstrow As Long
Dim Lastrow As Long
Dim Findstart As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim myFolder As String
Dim Msg As String
'retrieve folder name in myFolder variable
myFolder = GetDirectory(Msg) & "\Scanned\"
MkDir myFolder
'*** subsequent code
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'-------------------------
'Below Code is for Asking User Input to select and go to Recipient List Sheet
'---------------------------------->
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select Recipient List Sheet"
'dialog caption
Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
'
Hi all,
I can automatically create range names by selecting a range of 2 cells wide.
Then selecting the menu
Forumulas | (Name Manager) Create from Selection | Left Column | OK
Works OK thru Dec.
But the 2nd Jan popups a dialog asking to over write the first Jan.
Can anyone assist with VBA code to do this:
i.e. Create a name range from column A
for the single cells from column B.
Trapping duplicate name errors:
And if Jan already exists - continually try Jan2 or Jan3 etc
until named.
.
.
And if Dec already exists - continually try Dec2 or Dec3 etc
until named.
2008
Month | Income
Jan | $1
.
.
Dec | $12
2009
Month | Income
Jan | $1
.
.
Dec | $12
Hi everyone:
I have been trying to come up with a formula (but wit not avail -that's why i'm here ) that can do what i'm going to explain next.
Ok, basically what i'm trying to do is to get the number of times a value repeats in a row in a given column range. I guess many of you (the experts) are thinking on the COUNTIF function but that fucntion alone wont work for my purpose -otherwise i wouldn't be here .
Here it is the scenario:
Coulmn Range $B1:$B5000
X
X
X
Y
X
X
Y
Y
Y
X
X
-----
2
Here is what I am trying to do. I have test cases that I need to monitor for actual date action taken and the calculated date action needs to be taken. I have a summary tab that lists the test case and i have one column that will look at the actual dates work performed tab and return the last cell with data. What I need to do now is for the same test look to my calculated date completition tab and return the next value in after the last action date from the actual date page ( do if you will, the next action due based on the last entered action (actual tab). You will see that the columns have numbers on top that represent the days between actions. I need to return, as the next action date from the calculated date tab based on the last action date that had an entry on my actual date tab.
I hope this makes sense, but if not let me know and I will try to provide a more concrete example.
Calculated due date
C
D
E
F
G
H
I
J
K
L
M
3
Days to next action
30
30
24
18
52
18
48
4
Name
type
inv num
date
date entered
action 1
action 2
action 3
action 4
action 5
action 6
5
test 1
a
406448
13-Dec-2010
12-Jan-2011
11-Feb-2011
7-Mar-2011
25-Mar-2011
16-May-2011
3-Jun-2011
21-Jul-2011
6
test 2
b
413251
25-Feb-2011
27-Mar-2011
26-Apr-2011
20-May-2011
7-Jun-2011
29-Jul-2011
16-Aug-2011
3-Oct-2011
7
test 3
c
408410
11-Jul-2010
10-Aug-2010
9-Sep-2010
3-Oct-2010
21-Oct-2010
12-Dec-2010
30-Dec-2010
16-Feb-2011
8
test 4
d
413600
17-Jan-2011
16-Feb-2011
18-Mar-2011
11-Apr-2011
29-Apr-2011
20-Jun-2011
8-Jul-2011
25-Aug-2011
9
test 5
e
414017
10-Feb-2011
12-Mar-2011
11-Apr-2011
5-May-2011
23-May-2011
14-Jul-2011
1-Aug-2011
18-Sep-2011
Spreadsheet Formulas
Cell
Formula
E5
=IF('Actual date of action'!C5="","",'Actual date of action'!C5)
F5
=IF('Actual date of action'!D5="","",'Actual date of action'!D5)
G5
=IF($F5="","",$F5+$G$3)
H5
=IF($F5="","",$G5+$H$3)
I5
=IF($F5="","",$H5+I$3)
J5
=IF($F5="","",$I5+J$3)
K5
=IF($F5="","",$J5+K$3)
L5
=IF($F5="","",$K5+L$3)
M5
=IF($F5="","",$L5+M$3)
E6
=IF('Actual date of action'!C6="","",'Actual date of action'!C6)
F6
=IF('Actual date of action'!D6="","",'Actual date of action'!D6)
G6
=IF($F6="","",$F6+$G$3)
H6
=IF($F6="","",$G6+$H$3)
I6
=IF($F6="","",$H6+I$3)
J6
=IF($F6="","",$I6+J$3)
K6
=IF($F6="","",$J6+K$3)
L6
=IF($F6="","",$K6+L$3)
M6
=IF($F6="","",$L6+M$3)
E7
=IF('Actual date of action'!C7="","",'Actual date of action'!C7)
F7
=IF('Actual date of action'!D7="","",'Actual date of action'!D7)
G7
=IF($F7="","",$F7+$G$3)
H7
=IF($F7="","",$G7+$H$3)
I7
=IF($F7="","",$H7+I$3)
J7
=IF($F7="","",$I7+J$3)
K7
=IF($F7="","",$J7+K$3)
L7
=IF($F7="","",$K7+L$3)
M7
=IF($F7="","",$L7+M$3)
E8
=IF('Actual date of action'!C8="","",'Actual date of action'!C8)
F8
=IF('Actual date of action'!D8="","",'Actual date of action'!D8)
G8
=IF($F8="","",$F8+$G$3)
H8
=IF($F8="","",$G8+$H$3)
I8
=IF($F8="","",$H8+I$3)
J8
=IF($F8="","",$I8+J$3)
K8
=IF($F8="","",$J8+K$3)
L8
=IF($F8="","",$K8+L$3)
M8
=IF($F8="","",$L8+M$3)
E9
=IF('Actual date of action'!C9="","",'Actual date of action'!C9)
F9
=IF('Actual date of action'!D9="","",'Actual date of action'!D9)
G9
=IF($F9="","",$F9+$G$3)
H9
=IF($F9="","",$G9+$H$3)
I9
=IF($F9="","",$H9+I$3)
J9
=IF($F9="","",$I9+J$3)
K9
=IF($F9="","",$J9+K$3)
L9
=IF($F9="","",$K9+L$3)
M9
=IF($F9="","",$L9+M$3)
Excel tables to the web >> Excel Jeanie HTML 4
I am building a workbook which is a very similar application to Trick #185. The formula works fine on the first cell but I am unable to copy the formula without a #NUM! error being delivered to subsequent sells. Is there something in the formula I'm missing?
=IF(ROWS(N$7:N8)
First I would like to thank everyone here for being so generous with their knowledge, time, and patience!
With that said... I need to use more of it lol.
I want to be able to enter data into a cell and then have a function act on it and have that result show up in the initial cell I entered the data in.
Anything I can think of would result in a circular reference error. Does anyone have any ideas or workarounds to make it look like that's what happened?
Thanks in advance!
hi i have writtern the following in a comman button
sub trythis()
dim x as range
set x = range("b1:b20")
for each x in x
if x.text = "June" then
x.copy
end if
next x
end sub
I have in the range names of the month repeated randomly. June appears 6 times in the list
By executing the above code the last cell containing "June" gets into copy mode (the cell is filled with marchings ants effect). Although the loop goes through many cells containing "June" only the last cell containing "June" shows this effect, how extend this effect to all the cells containing the text "June".
I am following this tutorial from youtube. It is a search function to search for a specefic word in an entire row. Here is the first part of the code:
Code:
=SEARCH(C$3,Table2[[#This Row],[DESCRIPTION]])
except i only get a value in the cell that i typed this in, instead of it searching the rest of the row like in the tutorial. i can't figure out how to fix this. anyone know what im doing wrong?
