User Profile
XXplore
Brass Contributor
Joined Dec 25, 2022
User Widgets
Recent Discussions
Re: Calculating bonus with multiple conditions
wmfw2360 The formulas in (...) returns True=1 or False=0 (Condition_1)*(Condition_2)*...*(Condition_n)*(Result) It returns 1*1*...*Result=Result when all conditions are Ture, otherwise 0; Similar to If ( AND(Condition_1, Condition_2, ..., Condition_n), Result_If_All_True )463Views0likes0CommentsRe: Populating information from one excel file into another
Jeremy2112 Say Sheet1 is your "credit card due date list", input formulars in calendars. (1) If you accept Date and Text in 2 rows: =IFERROR( OFFSET( Sheet1!$A$1, MATCH(A1&"th", Sheet1!$B:$B,0)-1, 0), "") (2) Then change a little bit to have both Date and Text in 2nd row: =IFERROR(A1 & CHAR(10) & OFFSET(Sheet1!A1,MATCH(A1&"th",Sheet1!B:B,0)-1,0), A1) (3) Then more changes to skip 1st row: Replace A1 with COLUMN(cell) & (ROW(cell)*number_according_to_your_calendar) & "th" For Date with "st", "rd" just copy, paste and adjust the formulars.1KViews0likes0CommentsRe: Formula adjust down column as it autofills right
XLN00B Try In cell C65, input =IF( OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,0)=0, OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,-1), IF( OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,-1)=0, OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,11), OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,0) ) ) Instead of using J65, I65, U65... , Use Formular OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,0) - Manual change 0 when you want to refer to different column, e.g. 1 for K65, 2 for L65 - Drag right will auto-increase COLUMN() parameter eg. from COLUMN(C64)-3 to COLUMN(D64)-3, which means from offset 0 row to 1 row, i.e. J65 to K651KViews0likes1Comment- 2.7KViews0likes4Comments
Re: Including number format paste special in the Macro
Use Data -> Text to Columns can solve this issue. You can find more infomation in Excel Help: Move pointer to a cell with green mark -> An exclamation menu pop out -> click "Help on this error" Action could be recorded: Selection.TextToColumns Destination:=Range("I1"), _ DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, _ Tab:=True, _ Semicolon:=False, _ Comma:=False, _ Space:=False, _ Other:=False, _ FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True1KViews0likes0CommentsRe: If there is a certain Text -> CheckBox disappear
I suppose you're using Develop->Controls->Insert->Combo/CheckBox, And I think you need use VBA. (1) Create a macro, where "H7" is the cell linked to ComboBox, "2" is the value you want to blank the CheckBox. Modify the names according to yours. Sub Macro1() If Range("H7") = 2 Then Sheets("Sheet2").CheckBoxes("Check Box 4").Visible = False Else Sheets("Sheet2").CheckBoxes("Check Box 4").Visible = True End If End Sub (2) Right click the ComboBox and assign this macro to it.1.2KViews0likes0CommentsRe: Formulas and Functions
You can get the formular string but it will not calculate automatically. In B1: =IF(A1<=4, FORMULATEXT(D1),"") In C1: =IF(A1<=4, FORMULATEXT(E1),FORMULATEXT(F1)) I don't know how to activate calculation of these formular string. If you accept auxiliary cell, "EVALUATE" works. You can select B2, Press Ctrl+F3, create a name like RAW=EVALUATE(Sheet2!B1). Then you can use =RAW to calculate formular of cell above current cell.761Views0likes0CommentsRe: Bringing the Curve to the Forefront
Do you mean the curve and points in Scatter Chart? I suppose bring forward/backward doesn't support layer order within a chart. There's some workaround to make the curve clearly. (1) Click the points, in "Format Data Point" -> Fill & Line -> Marker -> Marker Option, decrease size of Built-in point marker. (2) Copy the chart, show only points in ChartA; show only curve in ChartB, fill with transparent and bring forward. (3) Create Scatter chart without line -> Select Source, add same series again -> Select this new series, Format Data Series -> Plot Series on Second Axis, Change Chart Type of 2nd series as "Scatter with Smooth Line"1.2KViews0likes1CommentRe: Autofill from a changing list
If in your 2nd sheet columns remains same order, "vlookup" "xlookup" can meet your needs If column order changes but remains same name, "match" and "offset" helps. Adding new rows should not impact anything in your formular when you refer to columns.809Views0likes0CommentsRe: Multiple lookup and references
MS_excel2022 I'm not fully understand your question, But If you lookup a value from A1:A5000 which is a column, MATCH('sheet1'!A632, A1:A1500,0) will return the row number of first matched cell. So you would change the formular to =OFFSET($C$1,MATCH('sheet1'!A632, A1:A1500,0),0) if MATCH returned 3, you will got an offset of 3 rows 0 column from Range("C1"), which is value of Range("C4") Description: offset(reference, rows, columns, optional height=1, optional weight=1)1.3KViews0likes0Comments
Recent Blog Articles
No content to show