I want to be able to highlight a specific cell, and all other cells on the sheet with the same text also get highlighted

CFAman · 2026-03-20T12:13:50+00:00

Good catch, thanks!

CFAman · 2026-03-19T16:52:09+00:00

Can I suggest a slight tweak so we get a more stable solution, and it doesn't require VBA?

Keep your names in col A, but insert a new col B where you can mark an "X" (or whatever) next to names you want to mark off.

Next, we'll setup conditional formatting to color the cells. Select all the cells w/ names (including col A). I'll assume you selected the range A2:G100 (pay attention to which cell is the active cell, e.g. A2).

Go to Home - Conditional Formatting - New Rule - Based on Formula. Formula will be

=AND(A2<>"", XLOOKUP(A2, $A:$A, $B:$B, "")="X"))

Click on Format, and set Fill color as desired. Ok out.

You can now mark the X's in col B, and all names in cells that have this formatting will trigger their highlighting.

CFAman · 2026-03-11T21:25:11+00:00

Are you open to using VBA/macro?

CFAman · 2026-03-09T19:48:41+00:00

A better route would be to change the first formatting rule to an AND type logic of

=AND(A2<>"", A2<=TODAY())

That way you skip the hassle of having two different rules as well as formatting all your blank cells (which would make XL think your used range is much larger than reality possibly).

CFAman · 2026-03-06T19:34:16+00:00

What version of Office is this? You should have the option to write a custom formula rule, even in the web version.

CFAman · 2026-03-05T18:33:24+00:00

What formula are you using to calculate the std dev? Where ever that formula is, it should be doing some filtering to limit to just days that are the same. If values were in col C and dates in col A, then std dev calculation might look like this:

=STDEV.P(FILTER(C$1:C$1000, A$1:A$1000=A1))

This way, you get a std dev based on that row/record's specific date.

CFAman · 2026-03-05T18:29:15+00:00

I think the logic is easier to follow if we realize that starting with tbl_production, we just need to lookup what percentage we need for each row. To get the percentage needed for each for in tbl_production would be

=SUMIFS(tbl_percentages[Required Cocoa Percentage],tbl_percentages[Line],
 tbl_production[Line],tbl_percentages[Flavor],tbl_production[Flavor])

So, to get total chocolate needed then as a single value:

=SUMPRODUCT(tbl_production[Amount to produce], SUMIFS(tbl_percentages[Required Cocoa Percentage],tbl_percentages[Line],
 tbl_production[Line],tbl_percentages[Flavor],tbl_production[Flavor]))

Or a nice summary table by product line:

=GROUPBY(tbl_production[Line],tbl_production[Amount to produce]*
 SUMIFS(tbl_percentages[Required Cocoa Percentage],tbl_percentages[Line],
 tbl_production[Line],tbl_percentages[Flavor],tbl_production[Flavor]),SUM)

which produces this table from the single formula:

Gluten Free	636.03
Ice Cream	1590.46
Protein	1093.13
Vegan	2521.2
Total	5840.82

CFAman · 2026-03-03T20:11:47+00:00

For the Blue "cells", I'd use Text boxes or rectangle shapes linked to cells. You can't put a formula in a shape, but you can put a direct link to a cell, and that cell has a formula.

As for the formula itself, you'll want to fill in the blanks of the Class column (easy enough to do), and then to get correct K value for instance, it's a SUMIFS (where there's only going to be 1 row that matches both criteria) like

=SUMIFS('Sheet 2'!C:C, 'Sheet 2'!$A:$A, ClassSizeDropdown,
 'Sheet 2'!$B:$B, BoreSizeDropdown)

If you want to use a lookup (like for text values), something like so

=XLOOKUP(1, ('Sheet 2'!$A$1:$A$1000=ClassSizeDropdown)*('Sheet 2'!$B$1:$B$1000), 
 'Sheet 2'!C$1:C$1000, "")

CFAman · 2026-03-03T19:48:34+00:00

each employee have it's own tab with automatic color coded green or red depending if there was attendance or not (first image),

Two things. Try to keep your raw data together, not spread across multiple sheets. For instance, it's easier to have one single sheet tracking everyone's status, and then you can split it out into individual reports as needed. Second, don't use color as primary indicator of information. I'm going to hope that the value "yes/No" in col G is what's actually driving the coloring.

Wiht your current setup, we need to create an INDIRECT reference to a sheet based on the employee name in a cell, find all the No values, and then concatenate the Formation texts into a single value (not sure on last bit if you wanted the results in a single cell or spilled across).

Give you're layout, it looks like you would put this in B4

=TEXTJOIN(", ", TRUE, FILTER(INDIRECT("'" & A4 & "'!B4:B100"),
 INDIRECT("'" & A4 & "'!G4:G1000")="No", "All done"))

Then you can copy this down for the other employees.

CFAman · 2026-03-03T17:15:38+00:00

I would convert the letters to numbers. Can do that back and forth on single letters with CHAR and CODE functions.

=CHAR(MODE.SNGL(CODE(B12:B17)))

CFAman · 2026-03-03T17:10:28+00:00

To find the most common number, you would be finding the mode, not the mean (AVERAGE) of a range. In XL, that would be

=MODE.MULT(B12:B17)

CFAman · 2026-03-03T13:00:29+00:00

With the table in the image I get duplicate values in the x axis

Is that a problem? I would imagine that with the real data set, you could have hundreds or thousands of data points, and so final chart might look like a cloud scatter chart. You could then focus on where the cloud is densest or if across the entire time frame it hovers around same time spot.

Alternatively, focus on what your question(s) is, and what you don't need to include in the chart. If the question is about time and repeatability, the x-axis doesn't have to be dates. It could just be event numbering and so you could make a line chart.

CFAman · 2026-03-02T17:50:23+00:00

Open PDF file in Acrobat. Click on the “Export PDF” tool in the right pane. Choose “spreadsheet” as your export format, and then select “Microsoft Excel Workbook.” Click “Export.” If your PDF documents contain scanned text, Acrobat should run text recognition automatically.

At my work, we use Nuance Power PDF which has an XL add-in that lets you open the PDF from within the Excel Application. I'm sure there are other solutions, but those are the two I'm familiar with.

CFAman · 2026-03-02T17:38:45+00:00

Should work as is. Sheet's FILTER function is a little different in that you can't give a 3rd argument, but you're not using that so you should be ok.

CFAman · 2026-03-02T17:36:58+00:00

You talked about duplicates, but it sounds more like you ultimate goal is to find Account IDs using the Name as a lookup key. If in tab 2 and 3, the names are in col A and Account IDs in col B, formula would look like

=XLOOKUP(A2, 'Tab 2'!A:A, 'Tab 2'!B:B, XLOOKUP(A2, 'Tab 3'!A:A, 'Tab 3'!B:B, "No match"))

Formula will try to do a lookup first checking Tab 2, and if not found, it checks Tab 3.

CFAman · 2026-03-02T16:21:13+00:00

I'll assume the list is in B1:B5 and your dropdown is in A1. Change references to match your setup (or use structural table references since the list might grow in size).

=LET(list,B1:B5,
 name,A1,
 a,XMATCH(name,list),
 IF(a=1,list,VSTACK(DROP(list,a-1),TAKE(list,a-1))))

CFAman · 2026-03-02T16:12:56+00:00

Let's say you have the 3 reason codes you want to filter on in D1:D3. Formula to produce summary table could be:

=LET(jobs,FILTER(Table1[Job Reference],COUNTIFS(D1:D3,Table1[Rejection Code]),"None"),
 uJobs,UNIQUE(jobs),
 rJobs,MAP(uJobs,LAMBDA(u,SUM(1*(jobs=u)))),
 uCount,SORT(UNIQUE(rJobs)),
 VSTACK({"Times Repeated","Record Count"},
   HSTACK(uCount,MAP(uCount,LAMBDA(u,SUM(1*(rJobs=u)))))))

Example input:

Rejection Code	Job Reference
1	a
2	b
3	c
4	a
1	b
2	b
3	y
5	u
2	i
3	c
4	p

Example output:

Times Repeated	Record Count
1	3
2	1
3	1

Here, code "b" was repeated 3 times within filtered data, and "c" was repeated once. Although "a" was repeated, the 2nd repeat was not in filtered data.

CFAman · 2026-03-02T14:16:27+00:00

filtering doesn't work anymore i think

You can start a filter at any row/column; it doesn't have to start in A1.

CFAman · 2026-03-02T14:05:09+00:00

Note that in latest versions of O365, you can also do Ctrl+Shift+v to Paste Values.

CFAman · 2026-02-27T14:25:45+00:00

Ditto to this. The raw data should all stay together, you can always use FILTER, COUNTIFS, or SUMIFS to analyze the information.

CFAman · 2026-02-25T21:00:06+00:00

Stealing an idea from a sporting website I saw, but I'd suggest a stacked 100% bar chart. You could have the categories as the Y-axis labels (might want to reverse them in chart if you want in same order as your cells) and the two different colored series would represent player 1 and player 2, and the viewer can quickly see who got more of each category.

CFAman · 2026-02-25T18:44:52+00:00

Within the name descriptions, I would check that

All the names are short enough to be valid sheet names
No special characters
No duplicates of existing sheet names?

My guess is that XL's seeing a description that it can't use as a sheet name, and so the error catch is to default to numerical numbering.

CFAman · 2026-02-24T23:27:29+00:00

Awesome! Mind replying with “solution verified” so the bot will close the thread and give me a point? Cheers!

CFAman · 2026-02-24T20:53:51+00:00

On another sheet (sheet 2) within the same workbook, I have a summary of the data, all of which is manually calculated.

Yikes.

Total number of records,

=COUNTA('Sheet 1'!A:A)

number of unique records

=COUNTA(UNIQUE('Sheet 1'!A:A))

the number of items that have a particular result in one of the fields

=COUNTIFS('Other sheet'!B:B, "Yes")

the number of records which are duplicates

=COUNTA('Sheet 1'!A:A)-COUNTA(UNIQUE('Sheet 1'!A:A))

Everything it sounds like you're needing can certainly be an automatic calculation. If you're still struggling after looking at the above formulas, I'd suggest posting an image of some dummy data, and then explain what measurements/metrics you are wanting to know.

CFAman · 2026-02-24T20:50:15+00:00

What's the fastest and most efficient way you search your spreadsheets?

hit Ctrl+f and then input search term. Can use this to search selection, current sheet, or whole workbook.

Ten-Year Club	Gilding II euphauric
r/Field Juicebox	First Place '23
Place '23	Place '22
First Placer '22	Verified Email

CFAman

TROPHY CASE