Search results

Another: =ISODD(ROWS(UNIQUE($B$3:$B3)))

F2: =DATE(MID([@[Time Out]],7,4),LEFT([@[Time Out]],2),MID([@[Time Out]],4,2))+TIMEVALUE(MID([@[Time Out]],12,5))-DATE(MID([@[Time in]],7,4),LEFT([@[Time in]],2),MID([@[Time in]],4,2))-TIMEVALUE(MID([@[Time...

In the attached (a separate workbook which interrorgates your attached file) you'll find: 1. Cell A1, a named range (called FileNameAndPath) containing the full path and file name of the workbook to be interrogated which you should adjust. 2. A copy of your combinations table. This is used to...

In the attached: Your Table1 with an added row (S No. 4) Your Table2; with this table it's important to retain the trailing space in the first column header Durations . The rest of the step names can be anything and any number of steps. Note that these tables are now proper Excel tables. After...

Cross posted: https://forums.excelguru.ca/threads/excel-vba-reference-worksheets-using-variables-in-a-table.12153/ https://www.mrexcel.com/board/threads/reference-worksheets-using-variables-in-a-table.1273631/...

Your source data doesn't have any columns named like "121 to 150 days past due (Home Currency)", "151 to 180 days past due (Home Currency)", only "Amt 1", "Amt2", "Amt3" etc. 2 Things: If your data did contain those column headers it would work as it is (tested here), with the proviso that...

More convoluted than I thought. See attached.

See conditional formatting in the attached.

One thing to be aware of when concatenating values to use in VLOOKUP or MATCH is a possible incorrect match. The following 2 rows are different, yet the helper column is the same: To help reduce the possibilities of such incorrect matches you can put an unusual character between the 2 values...

Also pivot table. See attached at cell D2.

Small update to the attached workbook (Date columns in the Source table with no data are preserved in the output table).

In the attached workbook there's your source data on the sheet Source and a Power Query query output on sheet Result. The source data needs to be as follows: The first 4 columns must be "Area", "Position", "Code", "Company" but those 4 can be in any order. The rest of the columns are what the...

In the attached, a udf called blah. This formula needs to be array-entered (commit the formula to multiple cells at once using Ctrl+Shift+Enter rather than the usual plain Enter). The area you need to select needs to be 5 cells wide, but the number of rows will vary according to your data; if...

Select a blank cell, copy it, select the cells with text you want to convert to numbers, right-click, paste special, choose Add, OK.

Try the likes of =AVERAGE(VALUE(K2:K5))

In the attached, pivot chart at cell M22 of pivot table at cell B22. In the background is a Power Query query which (a) unpivots your source data, (b) outputs to a pivot table! Options in the pivot table allow hiding/showing items with no data. Pivot tables need refreshing after the source data...

What's wrong with copy/pasting?:

Sparklines?: maybe: Atached.

Two ways: 1. Paste the data into Table1 (cell B7 of the BUILD INFO sheet) overwriting what's already there and ensuring the new data is completely covered by the table extents and that all the old data is gone. Resizing of the table mostly happens automatically, but if not, you can adjust the...

So you think that 13:58:40 with 1 min 20 secs before 14:00 is closer to 14:00 than 13:58:57 with 1 min 03 secs before 14:00? I'm sure there's a world somewhere where you're right.

Raw data: Which is the closest before 14:00?: I say: You say:

In the attached: On sheet SCHEDULE, a table at cell M7 which you can adjust. It contains the start times of breaks and handover times in the first column and the duration of such events in the next column. A handover has 0 duration. On sheet BUILD INFO: A table at cell B6 which is your raw data...

Not tested, change .Range to .Databodyrange

See attached, result table at cell N2. No attempt made to make it 'elegant'!

Various solutions from other responders in columns B:E. This should translate to Italian formulae when you open it.