If you don't have a lot of formulas, you can just nest it all together. I'm assuming you want to see the index results OR a blank, depending on what's found......Looking for a little assistance please.
=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))
this line works great if there is info to find if not it returns an #NA
For some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?
Just use IFERROR.If you don't have a lot of formulas, you can just nest it all together. I'm assuming you want to see the index results OR a blank, depending on what's found......Looking for a little assistance please.
=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))
this line works great if there is info to find if not it returns an #NA
For some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?
=IF(ISERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))),"",INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)))
if you have a lot of formulas, I'd add a secondary column to do the IF/ISERROR test since the indexing is redundant in the option above (Excel will choke on calculating). This assumes your INDEX/MATCH is in A1.
=IF(ISERROR(A1),"",A1)
I like IFERROR a lot more than if(isna
=IFERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)),"")
Going to use this its much less typing intensive, thanks.Just use IFERROR.If you don't have a lot of formulas, you can just nest it all together. I'm assuming you want to see the index results OR a blank, depending on what's found......Looking for a little assistance please.
=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))
this line works great if there is info to find if not it returns an #NA
For some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?
=IF(ISERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))),"",INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)))
if you have a lot of formulas, I'd add a secondary column to do the IF/ISERROR test since the indexing is redundant in the option above (Excel will choke on calculating). This assumes your INDEX/MATCH is in A1.
=IF(ISERROR(A1),"",A1)
=iferror(index/match,"")
ThanksI like IFERROR a lot more than if(isna
=IFERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)),"")
Here's a solution that will handle just numbers as well as letters.Kanil said:I feel like this should be really easy but I just cant figure it out.
I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.
For example:
===|===A===|===B===|===C===|===D===|
=1=|BLA====|=======|BLA====|=======|
=2=|IAII=====|IAlI=====|=======|=======|
=3=|12A====|=======|=======|12A====|
Just copy, open excel, press Alt + F11 -> Insert -> Module. Paste this into the window.Here's a solution that will handle just numbers as well as letters.Kanil said:I feel like this should be really easy but I just cant figure it out.
I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.
For example:
===|===A===|===B===|===C===|===D===|
=1=|BLA====|=======|BLA====|=======|
=2=|IAII=====|IAlI=====|=======|=======|
=3=|12A====|=======|=======|12A====|
First, you'll have to make your own macro. I've used this one before that this guy wrote:
Function StringConcat(Sep As String, ParamArray Args()) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' www.cpearson.com/Excel/stringconcatenation.aspx
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula. There is a VBA imposed limit that
' a string in a passed in array (e.g., calling this function from
' an array formula in a worksheet cell) must be less than 256 characters.
' See the comments at STRING TOO LONG HANDLING for details.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
If Len(R.Text) > 0 Then
S = S & R.Text & Sep
End If
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
On Error GoTo 0
Err.Clear
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M
Else
''''''''''''''''''''''''''''''''''''''''''''''''
' STRING TOO LONG HANDLING
' Here, the error handler must be set to either
' On Error GoTo ContinueLoop
' or
' On Error GoTo ErrH
' If you use ErrH, then any error, including
' a string too long error, will cause the function
' to return #VALUE and quit. If you use ContinueLoop,
' the problematic value is ignored and not included
' in the result, and the result is the concatenation
' of all non-error values in the input. This code is
' used in the case that an input string is longer than
' 255 characters.
''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ContinueLoop
'On Error GoTo ErrH
Err.Clear
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
Err.Clear
M = LBound(Args(N), 2)
If Err.Number = 0 Then
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M
End If
On Error GoTo ErrH:
End If
Else
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
End If
Else
On Error Resume Next
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
On Error GoTo 0
End If
ContinueLoop:
Next N
'''''''''''''''''''''''''''''
' Remove the trailing Sep
'''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
If Len(S) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
End If
StringConcat = S
'''''''''''''''''''''''''''''
' Success. Get out.
'''''''''''''''''''''''''''''
Exit Function
ErrH:
'''''''''''''''''''''''''''''
' Error. Return #VALUE
'''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
End Function
Here's a solution that will handle just numbers as well as letters.Kanil said:I feel like this should be really easy but I just cant figure it out.
I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.
For example:
===|===A===|===B===|===C===|===D===|
=1=|BLA====|=======|BLA====|=======|
=2=|IAII=====|IAlI=====|=======|=======|
=3=|12A====|=======|=======|12A====|
First, you'll have to make your own macro. I've used this one before that this guy wrote:
Function StringConcat(Sep As String, ParamArray Args()) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' www.cpearson.com/Excel/stringconcatenation.aspx
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula. There is a VBA imposed limit that
' a string in a passed in array (e.g., calling this function from
' an array formula in a worksheet cell) must be less than 256 characters.
' See the comments at STRING TOO LONG HANDLING for details.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
If Len(R.Text) > 0 Then
S = S & R.Text & Sep
End If
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
On Error GoTo 0
Err.Clear
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M
Else
''''''''''''''''''''''''''''''''''''''''''''''''
' STRING TOO LONG HANDLING
' Here, the error handler must be set to either
' On Error GoTo ContinueLoop
' or
' On Error GoTo ErrH
' If you use ErrH, then any error, including
' a string too long error, will cause the function
' to return #VALUE and quit. If you use ContinueLoop,
' the problematic value is ignored and not included
' in the result, and the result is the concatenation
' of all non-error values in the input. This code is
' used in the case that an input string is longer than
' 255 characters.
''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ContinueLoop
'On Error GoTo ErrH
Err.Clear
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
Err.Clear
M = LBound(Args(N), 2)
If Err.Number = 0 Then
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M
End If
On Error GoTo ErrH:
End If
Else
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
End If
Else
On Error Resume Next
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
On Error GoTo 0
End If
ContinueLoop:
Next N
'''''''''''''''''''''''''''''
' Remove the trailing Sep
'''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
If Len(S) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
End If
StringConcat = S
'''''''''''''''''''''''''''''
' Success. Get out.
'''''''''''''''''''''''''''''
Exit Function
ErrH:
'''''''''''''''''''''''''''''
' Error. Return #VALUE
'''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
End Function
Ugh, and here I thought IFERROR only returned a result if there was an error.Going to use this its much less typing intensive, thanks.Just use IFERROR.If you don't have a lot of formulas, you can just nest it all together. I'm assuming you want to see the index results OR a blank, depending on what's found......Looking for a little assistance please.
=INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))
this line works great if there is info to find if not it returns an #NA
For some reason I can not use if(isna I get a too many arguments. I would like a blank if the result is NA what am I doing wrong?
=IF(ISERROR(INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0))),"",INDEX('Daily Report Fri'!$B23:$B33,MATCH($A$13,'Daily Report Fri'!$C$23:$C$33,0)))
if you have a lot of formulas, I'd add a secondary column to do the IF/ISERROR test since the indexing is redundant in the option above (Excel will choke on calculating). This assumes your INDEX/MATCH is in A1.
=IF(ISERROR(A1),"",A1)
=iferror(index/match,"")
Here's a solution that will handle just numbers as well as letters.Kanil said:I feel like this should be really easy but I just cant figure it out.
I have a group of cells in a row that may or may not contain text in them. Each row has one (and only one) cell that has text in it but it could be anywhere in the row (sheet goes to column IU). What I need to do is have a formula in column A that looks at the rest of the columns in the row and places that text into column A.
For example:
===|===A===|===B===|===C===|===D===|
=1=|BLA====|=======|BLA====|=======|
=2=|IAII=====|IAlI=====|=======|=======|
=3=|12A====|=======|=======|12A====|
First, you'll have to make your own macro. I've used this one before that this guy wrote:
Function StringConcat(Sep As String, ParamArray Args()) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' www.cpearson.com/Excel/stringconcatenation.aspx
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula. There is a VBA imposed limit that
' a string in a passed in array (e.g., calling this function from
' an array formula in a worksheet cell) must be less than 256 characters.
' See the comments at STRING TOO LONG HANDLING for details.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
''''''''''''''''''''''''''''''''''''''''''''''''
' Loop through the Args
''''''''''''''''''''''''''''''''''''''''''''''''
If IsObject(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' OBJECT
' If we have an object, ensure it
' it a Range. The Range object
' is the only type of object we'll
' work with. Anything else causes
' a #VALUE error.
''''''''''''''''''''''''''''''''''''
If TypeOf Args(N) Is Excel.Range Then
'''''''''''''''''''''''''''''''''''''''''
' If it is a Range, loop through the
' cells and create append the elements
' to the string S.
'''''''''''''''''''''''''''''''''''''''''
For Each R In Args(N).Cells
If Len(R.Text) > 0 Then
S = S & R.Text & Sep
End If
Next R
Else
'''''''''''''''''''''''''''''''''
' Unsupported object type. Return
' a #VALUE error.
'''''''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
'''''''''''''''''''''''''''''''''''''
' ARRAY
' If Args(N) is an array, ensure it
' is an allocated array.
'''''''''''''''''''''''''''''''''''''
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
If IsArrayAlloc = True Then
''''''''''''''''''''''''''''''''''''
' The array is allocated. Determine
' the number of dimensions of the
' array.
'''''''''''''''''''''''''''''''''''''
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
On Error GoTo 0
Err.Clear
''''''''''''''''''''''''''''''''''
' The array must have either
' one or two dimensions. Greater
' that two caues a #VALUE error.
''''''''''''''''''''''''''''''''''
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M
Else
''''''''''''''''''''''''''''''''''''''''''''''''
' STRING TOO LONG HANDLING
' Here, the error handler must be set to either
' On Error GoTo ContinueLoop
' or
' On Error GoTo ErrH
' If you use ErrH, then any error, including
' a string too long error, will cause the function
' to return #VALUE and quit. If you use ContinueLoop,
' the problematic value is ignored and not included
' in the result, and the result is the concatenation
' of all non-error values in the input. This code is
' used in the case that an input string is longer than
' 255 characters.
''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ContinueLoop
'On Error GoTo ErrH
Err.Clear
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
Err.Clear
M = LBound(Args(N), 2)
If Err.Number = 0 Then
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M
End If
On Error GoTo ErrH:
End If
Else
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
End If
Else
On Error Resume Next
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
On Error GoTo 0
End If
ContinueLoop:
Next N
'''''''''''''''''''''''''''''
' Remove the trailing Sep
'''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
If Len(S) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
End If
StringConcat = S
'''''''''''''''''''''''''''''
' Success. Get out.
'''''''''''''''''''''''''''''
Exit Function
ErrH:
'''''''''''''''''''''''''''''
' Error. Return #VALUE
'''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
End FunctionJust copy, open excel, press Alt + F11 -> Insert -> Module. Paste this into the window.
Then go into your excel and in A1 type =StringConcat("",A2:UI2). Copy down.
This will concatenate everything within a row, since you said there's only one entry, and the rest are blanks, A1 will only have that one entry. Integer / string / etc
This is pretty much the idea here - just making it more easy to navigate and dynamic.Could you have a page where you enter expenses down one column and to the left you tag each with an expense category. On the actual/budget page you have your categories in column A, your actual in column B with sumifs looking at the expense input page, and your budget in column C, and your remaining budget in column D.
Yah, shoot me an email and I'll be happy to look at it for you if the other person isn't able to help you out.General question for a budget spreadsheet I created.
Right now I have the BUDGET Page and an ACTUAL (Spent) Page...I have to monitor both to keep under budget on all my categories
So at the top I put my income for every pay date and then down the below, the bills/expenses that fall between that pay date and the next one.
Actually I'm pretty proud of it as it calculates how much is left over has anything from Haircut to Savings Contributions (I then track my savings account on yet a third page).
So...basically, my haircut budget is 25 dollars a month, let's say it costs 23 dollars. I have to click on the ACTUAL page and type in 23, and then go back to my BUDGET page and reduce the "budget" down to 23 showing that I stayed under after the pay period is up.
Is there a way to combine the actual and budget pages so that I can log expenses all on the same page with an additional line but have it not affect all the numbers (until I need to recalculate the "budget" at the end of the pay period)
For things like gas expenses, I might have to log in an expense 3 times, what I do now is basically continually keep a running overall total on the ACTUAL page where it might be easier to have new lines on the BUDGET page each time I purchase the gas heading.
This makes no sense, I know...
If someone wants to take a look at it, and clean it up/make it a bit more dynamic, I'd be happy to pay a fee for your time. I know it can be done, but right now such an unabridged method that it becomes cumbersome.
I know there are budget templates out there, but I like the one I created: it's clean, and instead of monthly, operates between pay periods (every 2 weeks).
I don't think that's bassackwards. Without seeing the sheet - is this some sort of weighted average?Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.
[SIZE=11pt]=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")[/SIZE]
[SIZE=11pt]I'd like to be able to just =Averageif($E$5:$E$17,PSSVSS,L$5:L$17) But it doesn't work. It only matches the first entry within the named range. [/SIZE]
[SIZE=11pt]I know my other one worksjust fine, but it's more just bugging me at this point.[/SIZE]
No, it's not weighted. But the number of entries within "PSSVSS" may change, so I can't just only average a certain range. Well I could. but this way I can just add/subtract from the named range and not have to worry about updating a ton of formulas to only hit certain rows, if that makes sense.I don't think that's bassackwards. Without seeing the sheet - is this some sort of weighted average?Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.
[SIZE=11pt]=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")[/SIZE]
[SIZE=11pt]I'd like to be able to just =Averageif($E$5:$E$17,PSSVSS,L$5:L$17) But it doesn't work. It only matches the first entry within the named range. [/SIZE]
[SIZE=11pt]I know my other one worksjust fine, but it's more just bugging me at this point.[/SIZE]
I can't see a way of making it work for either AVERAGEIF or AVERAGEIFS.
I think what you did is best. The only other way is to enter a ton of specific criteria in AVERAGEIFS.No, it's not weighted. But the number of entries within "PSSVSS" may change, so I can't just only average a certain range. Well I could. but this way I can just add/subtract from the named range and not have to worry about updating a ton of formulas to only hit certain rows, if that makes sense.I don't think that's bassackwards. Without seeing the sheet - is this some sort of weighted average?Is there a way to AverageIF an item exists within a named range? I'm currently doing it a bassackwards way.
[SIZE=11pt]=(SUMPRODUCT(SUMIF($E$5:$E$17,PSSVSS,L$5:L$17)))/COUNTIF(PSSVSS,"*")[/SIZE]
[SIZE=11pt]I'd like to be able to just =Averageif($E$5:$E$17,PSSVSS,L$5:L$17) But it doesn't work. It only matches the first entry within the named range. [/SIZE]
[SIZE=11pt]I know my other one worksjust fine, but it's more just bugging me at this point.[/SIZE]
I can't see a way of making it work for either AVERAGEIF or AVERAGEIFS.
Hitting F4 while editing the formula would lock all cells in that formula. You can toggle F4 multiple times to lock just rows or columns.Is there a non-vba way to lock multiple cell formulas at one time? Like if I am pulling totals from a different tab from cells C4 & C5 can I lock it as $C$4 and $C$5 at the same time?
Yeah I use F4 to lock the formulas, but it only works on 1 cell at a time, can I lock multiple cells at the same time?Hitting F4 while editing the formula would lock all cells in that formula. You can toggle F4 multiple times to lock just rows or columns.Is there a non-vba way to lock multiple cell formulas at one time? Like if I am pulling totals from a different tab from cells C4 & C5 can I lock it as $C$4 and $C$5 at the same time?
use accessOnce a week.
What I currently do is conditional format to highlight duplicate values and then just cut things into shape.
I can't combine the columns into one and sort, or else I'd do that and run a pivot table to count number of entries.
How can I keep duplicates on the same row (along with the data columns C-XX, tied to column B), while placing singles on their own row with either no data (single from column A) or relevant data (single in column B)Code:A BABA ABAACA ACAADA AEAAEA AFAAGA AGAAHA AIAAIA AJAAKA AKA
Assuming your date column is in col A....Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
I get a #NAME in the column I created the formula. I'll keep dinking with it. If I want MMM-YY as I have 2014 & 2105 data, can I replace MMM with MMM-YY? Thanks for the quick reply!!!Assuming your date column is in col A....Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
Insert a new column B (or wherever) and use this "=TEXT(MONTH(A2),"mmm")" and then pivot on col B. If you want the full month name, change the "mmm" to "mmmm".
Ooops, I completely overlooked that small detail!I get a #NAME in the column I created the formula. I'll keep dinking with it. If I want MMM-YY as I have 2014 & 2105 data, can I replace MMM with MMM-YY? Thanks for the quick reply!!!Assuming your date column is in col A....Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
Insert a new column B (or wherever) and use this "=TEXT(MONTH(A2),"mmm")" and then pivot on col B. If you want the full month name, change the "mmm" to "mmmm".
eta, I jacked the quotes, its working now but only for the month, still need to get it to MMM-YY if possible.
You ####### ROCKOoops, I completely overlooked that small detail!I get a #NAME in the column I created the formula. I'll keep dinking with it. If I want MMM-YY as I have 2014 & 2105 data, can I replace MMM with MMM-YY? Thanks for the quick reply!!!Assuming your date column is in col A....Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
Insert a new column B (or wherever) and use this "=TEXT(MONTH(A2),"mmm")" and then pivot on col B. If you want the full month name, change the "mmm" to "mmmm".
eta, I jacked the quotes, its working now but only for the month, still need to get it to MMM-YY if possible.
=TEXT(A2,"mmm-yy")
Even easier is to just use the group option in the Pivot Table. With dates, it will automatically group by month, year, quarter, week, etc.Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
This is my answer as well.acarey50 said:Even easier is to just use the group option in the Pivot Table. With dates, it will automatically group by month, year, quarter, week, etc.2Young2BBald said:Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
Thanks for the replies, I'm self taught in Excel (and obviously not very well). Where do I find the group option? Do I drag my date field to the row labels and then select it there, or do I do the grouping first and then add it as a row label? I see filters in the choose fields to add to the report section, but none that appear to specifically allow me to select the date parameters for the report.This is my answer as well.acarey50 said:Even easier is to just use the group option in the Pivot Table. With dates, it will automatically group by month, year, quarter, week, etc.2Young2BBald said:Need some help pivot tabeling some data with date info. I have a 25,000 row data set that has a list of occurrences captured by date (mm/dd/yyyy). I want the pivot table to give me the total number of occurrences by month and subset. I formatted the cells in the date column to MMM-YY so that the month and year show in the cell, but when I do the pivot table, it is still listing the days of the month that data occurred (i.e. January has 17 rows of Jan-15 because there were 17 days in January the data occurred). Once I changed the date to MMM-YY I tired to reformat the cells as TEXT and GENERAL, but this just turned the MMM-YY data into a 5-digit number. I tried to figure out if adding a column to the right and doing a LEFT formula would work but nothing is working here either. If I just needed the monthly totals, I could just sort A to Z and highlight to count, but there about 20 subsets I want to have as a secondary row label making counting impossible. Thanks for any guidance that can be shared.
This is better off in Access, but it's doable in Excel if you have to do it there.Here's an accounting Excel question...
So we get a data dump from an inventory system that we would like to put into an Excel document and create a template to upload to our accounting software. From the inventory data dump we would like to be able to convert certain line items into many, but some will stay one to one. For example:
Inventory #3 - $100 - Property 10 will remain the same (because Property 10 does not get allocated)
Inventory #3 - $100 - Property 7 becomes the following line items:
Inventory #3 - $50 - Property 7.1
Inventory #3 - $50 - Property 7.2
Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.
Thanks.
Is property 7 always split? What determines if an item is going to be split or not?Here's an accounting Excel question...
So we get a data dump from an inventory system that we would like to put into an Excel document and create a template to upload to our accounting software. From the inventory data dump we would like to be able to convert certain line items into many, but some will stay one to one. For example:
Inventory #3 - $100 - Property 10 will remain the same (because Property 10 does not get allocated)
Inventory #3 - $100 - Property 7 becomes the following line items:
Inventory #3 - $50 - Property 7.1
Inventory #3 - $50 - Property 7.2
Hopefully that makes some sense. I'm not sure if this should be done through a macro or what.
Thanks.
Set up a binary column and use it as the decision variable ("by changing" cells) in Solver.Using solver, if I want to pull from a specific list of values, how would I do that?
Specifically, I'm trying to choose an optimal Daily Fantasy Baseball Lineup based on the player projections for the day. (I realize that it ultimately may not be the best way to do it, but I want to play around with it.