入黎免費幫你砌excel/教你formula (10)

樓主繼續潛水(10)

有問題講低，一定會有人答(除非冇)

如果有FILE要send過黎，或者有其他問題，可以用以下方法搵到我:
Telegram : @excel4freehk
E-Mail : excel4freehk@gmail.com

BTW, 個網懶得交domain錢摺埋咗了

1. 檢查A1是否完整中文地址，可郵寄
2. 檢查B1是否完整英文地址，可郵寄
3. 將C1的中英地址拆開，格式如下

香港中環康樂廣場 8 號
交易廣場二期 8 樓
8/F, Two Exchange Square,
8 Connaught Place, Central,
Hong Kong

想問一下我係sheet 2 有3個table
如果我想係sheet1 到整個dropdown list
有冇得一揀左dropdown list就update到個table去sheet1

利申 剩係識用iferror 跟dropdown list個架個value
有冇d先進d既方法 thanks

Data Validation整個Table Name既list
假設個drop down list喺A1
之後用Vstack, Indirect砌返個Table出黎:
=VSTACK(INDIRECT(A1&"[#Headers]"), INDIRECT(A1))

典解唔直接用

=INDIRECT(A1&"[#All]")

問返上一個問題

唔該哂你我試左一格ok,但下面開多間鋪又亂哂了
可唔可以下面開多幾間鋪謝謝

建議用多幾張sheet做，而唔係同一張sheet，如果要同一張sheet做，唔好斷開D data，之後開多個Column填分店

唔識呢個

唔該我再試下

唔熟Excel Table d reference
我得閒研究下
https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

我有一份一年十二個月嘅Spreadsheet
每個月橫行Title係種類
直欄Title係日期
然後個Cell係銀碼
而芋幾個Cell 入面Attach 咗 Notes
如果我要 Export 一條List 係邊一粒Cell 落咗Notes 請問應該要點做


我想要下面個Find and Replace list 但係要Excel format
請指教

好似要用VBA

Sub GetAllComment()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim Tempwb As Workbook: Set Tempwb = Workbooks.Add
Dim ws As Worksheet
Dim cmt As Comment
Dim MyArray As Variant
Dim i As Long
Dim j As Long
    
    'Header
    MyArray = Array("Book", "Sheet", "Cell", "Value", "Comment")
    i = 1
    For i = 1 To 5
        Tempwb.Sheets(1).Cells(1, i).Value = MyArray(i - 1)
    Next i

    'Loop through all comments in workbook
    i = 2
    For Each ws In wb.Sheets
        For Each cmt In ws.Comments
            If cmt.Parent.Address <> "" Then
                MyArray = Array(ThisWorkbook.Name, cmt.Parent.Worksheet.Name, cmt.Parent.Address, cmt.Parent.Value2, cmt.Text)
                For j = 1 To 5
                    Tempwb.Sheets(1).Cells(i, j).Value = MyArray(j - 1)
                Next j
                i = i + 1
            End If
        Next cmt
    Next ws
    
    Erase MyArray
    Set Tempwb = Nothing
    Set wb = Nothing
    Set ws = Nothing
End Sub

Thx
如果唔理NOTES，只係想Export所有有Value 嘅CELL咁又要點做呢?

你d data穿窿?點解要咁做?

Ctrl + G --> Special... --> Constants
之後copy and paste

其實將D data放入notes/comments度已經係唔好既做法
因為普通formula食唔到, 點都要用VBA

其實我覺得係Entry問題....如果入到咁會好睇好多,又容易filter自己想要既野...

英國公司D人好堅持用vlookup
喺度數columns
明明xlookup好用好多

姐係咁已經一目瞭然

無錯, 佢果個係直接入做pivot table

個客掟黎嘅excel，冇計

係，唔係個個格有數
我用chatgpt搞咗了
多謝幫忙

excel 選取範圍內 除左數字 全部del 晒佢 點搞到？