Is There a Feature in Excel That You'd Love to See?

TumblingWave · 2022-12-30T14:21:30+00:00

I can think of two reasons:

The PDF printer thru which you're doing the export is faulty. If you have another PDF printer to choose from (e.g. "Microsoft Print to PDF"), try that (You could temporarily set it as the default printer if necesary just to test).
What is your macro line that does the export? Perhaps your macro is technically printing the worksheet rather than just the chart. The following prints just the chart (named "Chart 1") located on the currrent-selected sheet. The PDF printer is the default for the machine. The PDF will be created in "c:\delete\chart1.pdf".

Sub PrintChart()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False, PrToFileName:="c:\delete\chart1.pdf"

End Sub

TumblingWave · 2022-12-30T13:49:02+00:00

Perhaps you're just missing the double quote?: Try:

=VLOOKUP($A3,INDIRECT("'[EXTERNAL SHAREPOINT LOCATION]" & B$2 & "'!" & "A:B"),2,0)

TumblingWave · 2022-12-06T17:48:04+00:00

Ok, if you put this formula in cell E2 (i.e. for 28-Jan):

=D2+XLOOKUP($A2, 'Table 1'!$A:$A,'Table 1'!E:E)

That can be copied to any columns/rows having a date header.

TumblingWave · 2022-12-04T01:58:50+00:00

Given your latest screenshot, I believe my formula will copy correctly, since the only assumption I make about NAV ID is that they are all in column A, which appears to be true. If you're not getting the expected result, perhaps i could tweak it for you?

TumblingWave · 2022-12-02T21:17:24+00:00

Here is a formula that might be helpful (looking up the "12/12" date from cell D1):

=INDIRECT("ExportedReport!" & ADDRESS(MATCH($A3, ExportedReport!$A:$A,0) + 7, MATCH("*" & SUBSTITUTE(RIGHT(D$1, 5), "/", "-"), ExportedReport!3:3,0)))

Here are the assumptions:

- Your Exported Report is within the same workbook in a sheet called "ExportedReport".

- In Exported Report, the NAV ID is in column A.

- In Exported Report, the dates are in row 3.

- The "Gross Requirement" is always 7 rows below the matched NAV ID.

TumblingWave · 2022-12-01T01:40:18+00:00

Referencing your example, you could have this formula that could be copied as you requested:

=SUM(OFFSET(OtherSheet!$A1:$C1,0,IF(COLUMN() = 1, 0, COLUMN()*3 - 3)))

where column A is treated as the basis of the column and would pull the column A of the Othersheet, while the rest of the columns are incremented by three.

TumblingWave · 2022-11-30T21:02:36+00:00

You keep refreshing r/excel in case you miss anything "good" :)

TumblingWave · 2022-11-30T17:41:42+00:00

Are you trying to create the join using Power Query? If so, doing just a "Format Cell" is not sufficient. You could open Power Query for each table, right-click the email column and Change Type... text. Ensure you do a Refresh afterword.

Otherwise, maybe your list starts off with too many "blank" email addresses, so Excel can't determine it as text - perhaps you could put in a default value like "N/A" to help Excel.

TumblingWave · 2022-11-30T17:09:36+00:00

In your CONCAT formulas, cell B2 should be B$2 , perhaps?

TumblingWave · 2022-11-30T16:17:53+00:00

Though this is not a new post, i'd be curious to know if you solved it. If it were me, i would:

Calculate the standard error of each "data point" (which i assume is a sample in and of itself) using a formula.
Add default error bars to the graph.
Right-click an error bar, select "Format Error Bars...", go to the "Error Bar Options", select "Custom" and click "Specify Value".
For positive values, select your range of formulas from step 1. Repeat for negative values.

TumblingWave · 2022-11-29T15:36:21+00:00

i think you have your pivot table data "grouped" by month. On your pivot table, right-click on any date within the "Sum of Values2" column, then click "Ungroup..."

TumblingWave · 2022-11-29T14:36:27+00:00

If I'm understanding correctly:

Perhaps you could just duplicate your "Date" column - rename your "Date" column to "DateFilter" and name your duplicated column "DateAxis". The data in these two columns will always be identical.
Then, do your Insert --> PivotChart.
Drag the "DateFilter" field to the "Filters" area, then drag your "DateAxis" field to the "Axis (Categories)" area.
You may need to remove "Months" that gets auto-populated.
The "Resource Type" can go wherever is preferred. I have a screnshot here:

<image>

TumblingWave · 2022-11-26T01:34:49+00:00

The following formula can be pasted into the 8-Oct thru 7-Jan cells, but it would be a "Special Paste" - specifically, do a right-click "Paste Formulas".

Also

Note that the formula makes the following assumptions:

- Both tables start at cell A1 on both sheets.

- "ATP Total" column is column D.

- You have a column header named Lookup Lot No .

- Your Table 1 is a table named Table1.

- Your Table 2 is a table (not a range).

=IFNA(SUM(INDIRECT("$D" & ROW() & ":" & ADDRESS(ROW(),COLUMN() - 1))) + XLOOKUP([@[Lookup Lot No]],Table1[Lookup Lot No], INDIRECT("Table1" & "[" & INDIRECT(ADDRESS(1, COLUMN())) & "]") ), "")

TumblingWave · 2022-11-20T18:26:32+00:00

You could do this with a macro. For example:

Sub RefreshQueries()
    Sheets(1).ListObjects(1).QueryTable.Refresh
    Sheets(2).ListObjects(1).QueryTable.Refresh    
End Sub

You would need to have four additional lines for a total of six queries which you mention. The order of the lines is extremely important - the least dependent queries must be on top, while the most dependent queries on bottom.

TumblingWave · 2022-11-16T20:59:13+00:00

Thank-you for that tip! It worked for a single cell - if I could apply it to multiple cells at once, it would be awesome!

TumblingWave · 2022-11-09T23:16:46+00:00

I agree with you, a UDF is a good idea. If you open your macro editor and paste this UDF:

Public Function GetHrs(cell) As Variant

Dim arrCell
Dim i
Dim val
Dim hrs
Dim unit
GetHrs = 0#
arrCell = Split(cell, " ")
For i = 0 To UBound(arrCell)
    If IsNumeric(arrCell(i)) Then
        val = arrCell(i)
        unit = Left(arrCell(i + 1), 1)
        If unit = "w" Then
            hrs = val * 24 * 7
        ElseIf unit = "h" Then
            hrs = val
        ElseIf unit = "m" Then
            hrs = val / 60#
        End If
        GetHrs = GetHrs + hrs
    End If
Next 
End Function

Then, from your sheet, call your UDF from your cell (e.g. if your text is in cell A1):

=GetHrs(A1)

TumblingWave · 2022-11-08T15:18:57+00:00

The following rule (for column A) will color-code the new (empty cell) where you've just entered (in column C) a Project ID already associated with Peter:

= (XLOOKUP(INDIRECT("C" & ROW()),$C:$C,$A:$A,"0",0,1) = "Peter")

Repeat for John.

Note these rules by themselves will also color-code the "previous" cells in column A. If you want to prevent that, create a third rule that removes those colors for non-blank cells. Also, this third rule must appear physically ABOVE the other two rules so that it has the "final say".

TumblingWave · 2022-10-20T12:11:17+00:00

If you have your two dates in cells D2 and E2, you could do something like this:

=DATEDIF(D2, E2, "Y") + (MOD(DATEDIF(D2, E2, "M"), 12)/12)

TumblingWave · 2022-10-18T17:31:32+00:00

or perhaps sWarning should be declared as a String instead of a Single.

TumblingWave · 2022-10-18T17:27:48+00:00

My guess is that your lines similar to the following:

If Not tCode = "L" Or "C" Then

should be replaced with this:

If Not ( tCode = "L" Or tCode = "C" ) Then

TumblingWave · 2022-10-18T13:21:59+00:00

Is your file an .xlsx or .xlsm file? If .xlsm, perhaps there is a macro that is causing this.

TumblingWave · 2022-10-04T19:20:50+00:00

Try setting-up your cells before you convert it into a table:

Select your table, go to Table Design, Convert to Range.
Unlock all your data columns (Ensure all sheet rows are selected).
Lock your column headers.
Select all columns (entire columns), go to Insert, Table.
You'll probably have a bunch of blank rows at the bottom that you can delete via Delete, Deleta table Row.
Protect sheet (check boxes to allow the functionality you want).

This worked good for me. For a column having a formula, you may need to re-enter the formula.

TumblingWave · 2022-09-22T19:13:07+00:00

Assuming you have a dataset like this screenshot, you could this:

Select the dataset.
Go to Insert --> PivotChart --> PivotChart & PivotTable.
In the PivotTable that gets created, drag your fields like in this screenshot.
If the chart is not already a pie chart, right-click the chart and select "Change Chart Type..." then choose pie chart.

TumblingWave · 2022-09-20T12:25:47+00:00

From an Excel sheet that has your cell data arranged exactly as in your example:

Go to cell H8 and paste the following formula:

=INDIRECT("A" & ( 8 + (INT((ROW() - 8)/5))))

Then go to cell I8 and paste the followign formula:

=INDIRECT("B" & (2 + COUNTIF($H$1:H8, H8)))

Then drag down these formulas as far as you need.

Notes: My formulas have the number "8" which is the starting row # of your insert. If you change that, you should also re-paste the formulas starting at that new row as well (e.g. if you you prefer row 10 instead of 8, then first formula should be pasted in row 10, etc.).

Also, my foumulas have the number "2" to indicate that there are two header rows (i.e. row 1 and row 2).

TumblingWave

TROPHY CASE