Pencil pushing Excel gurus, please enter
- Thread starter blakbearddelite
- Start date
You could do 50 columns if =isnumber, and then one long ass =if. Then just do a data > group columns.
Might be silly...but it will work if you can't get an index working.
I'm not sure I get the array formula thing and how it must be entered with Control + Shift + Enter.
Where might I find a decent tutorial on vb? Is that Visual Basic?
I am a bit rusty with excel but I helped a friend with something similar to this.
VLOOKUP is your friend for this.
=VLOOKUP(C2,C5
17,2,TRUE)This formula says to look for the value in C2 in the range C5:C17. If the value is found, then return the corresponding value from the same row in column D.
https://support.office.com/en-us/ar...pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
All companies do that for sure, along with hiding downsides! The automakers overbuild inventory to play absorption accounting games, and then will shut down production for a month or two come January. Kills working capital, but can boost your income.
Ah, got it to work. I never would have figured that out. I don't know why that extra step is required. Thanks so much!!!
I am a bit rusty with excel but I helped a friend with something similar to this.
VLOOKUP is your friend for this.
=VLOOKUP(C2,C5
This formula says to look for the value in C2 in the range C5:C17. If the value is found, then return the corresponding value from the same row in column D.
https://support.office.com/en-us/ar...pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8
The problem is that it needs to use the 'contains' functionality. The data has other information in it that changes each month, banking information. One month it could be COMPANY ABC 234434534 and the next month is might be COMPANY ABC 4234854834. It needs to be smart enough to know that they're the same thing. A vlookup won't do that.
go into your original cell, put the cursor at the end (take out those { }) and hold ctr and shift then press enter. now it will tell it to act on every cell in a range and work as expected.
kinda clever and didn't know that was a feature you could do.
if your spreadsheet isn't working after you do that, if you replicated this exactly, the most likely next failure point would be not labeling the cells as denoted (the things and results denoted in the bottom right of the image)
and yes vb is visual basic. here's an example of one i hacked out from a significant figures function i didn't exactly like because it wasn't dealing with some data i was receiving.
open a new excel file, hit alt + f11, congrats yer in vb macro mode. now you can hack the planet.
paste this in
Code:
Function SigFigsMike(rng As Range, Optional iType As Integer = 1)
'iType = 1 is Precision
'iType = 2 is Scale
Dim rCell As Range
Dim sText As String
Dim sText2 As String
Dim iMax As Integer
Dim iLeft As Integer
Dim iStart As Integer
Dim iPrecision As Integer
Dim iScale As Integer
Dim i As Integer
Dim iDebug As String
Application.Volatile
Set rCell = rng.Cells(1)
'if not a number then error
'commented out this was breaking on 0.0
'If Not IsNumeric(rCell) Or IsDate(rCell) Then
' SigFigsMike = CVErr(xlErrNum)
' Exit Function
'End If
sText2 = Trim(rCell.Text)
sText = ""
'find position of decimal point (it matters)
iDec = InStr(sText2, ".")
If Val(sText2) = 0 Then
iLeft = 1
iScale = 0
'do this if it's an integer
ElseIf iDec = 0 Then
iLeft = Len(sText2)
iScale = 0
'do this if it's a numeric
Else
'GET NUMBERS TO LEFT OF DEC
If iDec = 2 Then
iLeft = 1
Else
iStart = 0
If Mid(sText2, 1, 1) = "-" Then
iStart = 1
End If
iLeft = iDec - iStart - 1
End If
'GET NUMBERS TO THE RIGHT
sText = Mid(sText2, iDec + 1)
'strip trailing zeroes
While Right(sText, 1) = "0"
sText = Left(sText, Len(sText) - 1)
Wend
iScale = Len(sText)
End If
iPrecision = iLeft + iScale
'return Min or Max
Select Case iType
Case 1
SigFigsMike = iPrecision
Case 2
SigFigsMike = iScale
Case Else
SigFigsMike = CVErr(xlErrNum)
End Select
End Functionthen just close the window. now you have the SigFigsMike function written in vb. it takes a cell as input, then a 1 to have it return precision, or a 2 for scale.
so go into A1 and put in the value 1300.03
in B1 put =SigFigsMike(A1, 1) and in C1 put =SigFigsMike(A1, 2)
you should see the values 6 and 2, which means to store this data in a database, the field would need to be declared as numeric(6, 2) to actually store it.
it's a little weird, like it seems to care how the format of the number is displayed, ie. 1300.0300005 won't be exactly right until you make the input cell an actual number out to x places and refresh the formula, but whatever, good enough for how little i need it.
you could basically make a vb that has a case statement and a bunch of string pattern matching in it, or even much more complicated logic. ie. if string like this, but not like that, and amount is greater than whatever, then use this value, etc.
For quick data summarization, yes.
For reference to other dependents (formulas in other tabs/worksheets/etc), they can be more a PITA than they're worth.
I use Pivot tables a lot for sales information (Unshipped sales), that I track, summarizing them by division. But outside of that, I wouldn't link to that table because god forbid the next time I refresh with updated data, and a division either gets added or deleted in the data, and it fucks up the linked to formula.
i guess if you made a huge window you could put 2 within that same instances in it and control how much space they took up, maybe more likely is that you have 1 instance of excel open and you are looking at multiple files in it, while you actually want a 2nd instance of excel open? if that's the case, just navigate to start menu -> excel and it should load up a new instance with no files. do a file -> open from it, and now you have a totally independent instance from the other one, throw it on one screen and maximize it and do the same with the other. is that what you're looking for?
also get dis mouse http://www.thechicagogarage.com/forum/entertainment-tech/161073-hey-nerds-buy-mouse.html
Thread Info
-
Created -
57Replies
-
1KViews
-
Participants list
