iwork – Conditional formatting in numbers with fixed comparation cell

Given the following sheet in numbers: enter image description here

I would like to highlight the maximum value for each column in green, and the minimum value of each column in red. I thought this can be done with conditional formatting. However, it seems not possible to use formulas, so I intruduced a new row at the top, which holds the max value of each column.

Now I can add conditional formatting, like so:

enter image description here

However, the problem is, that this only sets the correct conditional formattign for cell F3. For the remaining cells of the column, the conditional formatting auto-advances the referenced cell in the condition, e.g. :

enter image description here

This means I would need to adjust the formula in every cell, thus beeing the same effort as just coloring the cells myself, without conditional formatting.

So the question is: how can I easily hightlight the min/max values of a column as described above, without the need to do this manually for every cell?
Maybe as side note, I will enter new entries row by row, but once entered, those values will never change.

excel – How can I iterate through a worksheet and export groups of cell ranges to individual CSV files?

I have a large dataset (13292 rows, 96 columns) which contains data for ~100 different groups (group1, group2, etc.).

How can I iterate through the dataset and export each group into their own CSV (including headers)?

For example, every record which has group1 listed against it would be exported to group1.csv and so on.

I would’ve approached it manually, but there is simply far too much to manage comfortably, especially in terms of time.

google sheets – How to solve situation where I need timestamp script to work on cell auto entry, not manual

can someone help me with this problem.
I have timestamp script running in google sheet and it work fine when I manually change value in cell. But I can’t find any solution how to have timestamp working when entry in trigger cell is some auto data from formula or such.
Or maybe if someone have another solution.
What I’m doing now is, from sheet1 with Query transfer data to sheet2 when some change is made.
When new row is created in sheet2 I want to have date of entry for every row that is created in sheet2, and date need to be fix all the time.

sharepoint online – Manually Highlight a SharPoint List Cell

I’m wondering if there is a way to manually highlight a SharePoint list cell, without needing to use conditional formatting.

The reason for this is that there isn’t a strict rule in order to highlight a cell, it needs to be a decision made by the user. For example, if a date is ‘Fixed’ rather than ‘flexible’ they could highlight that date.

In theory I could add an extra column to highlight flexible or fixed but this would become quite messy as there are already numerous date columns and each would require the extra column.

Ideally it would just be a toggle (like in word or excel) when you enter text to have it in bold, italics etc. I don’t believe that exists though!

Any help would be greatly appreciated.

Immediately cross-populating or replicating the value of a data-validated cell to another tab sheet in MS Excel

I seek to perform the following operation in MS Excel:
I have a questionnaire for business partners to complete.
In the questionnaire, with tab Sheet1, I have cell C15.
Cell C15 has data validation placed upon it, using “List” containing LOV values of “Yes”, “No”, and “N/A”.
Upon the business partner clicking on C15 and populating it with, say, “Yes”, I want that same value “Yes” to be immediately written out to, say, tab Sheet2, cell H40.
I have reviewed a slew of very similar Q/As, all with slightly different nuances.
Potential solution themes I’ve looked at include CTL-Enter, hyperlinking, functions, and data validation.
A sincere Thank You for whatever help you can lend.

conditional formatting – How do I highlight a cell based on time?

For example, I want to have a cell highlighted while an employee is on break. So for this I have an “OUT” time and an “IN” time. If the employee goes on break at 14:22 and is back at 14:37, I want 14:37 to be highlighted red until that person is due back

Generating Google Spreadsheet Links from cell contents

You previously answered a part of my question, but I am looking to modify this behavior in multiple rows of column A so I don’t want to manually correct it.

In A2 I have:
/mylist/groceries/index.html

In B2 I use concatenate
=HYPERLINK(CONCATENATE("https//mylife.com",A2),A2)

This creates the link I need, and returns the same text that is in A2, but I want just the first portion without the/index.html to be viewable.

/mylist/groceries

I do not want the "/index.html" to show

Thank you.

graphics – Can a LatticeData image show more than a unit cell?

I have an interactive code for showing the crystallographic in Mathematica.

LatticeData[ Which[Type == "FCC", "FaceCenteredCubic", Type == "BCC", "BodyCenteredCubic", Type == "SC", "SimpleCubic"], "Image"]

However this shows only one unit, which means the planes that don’t fit in the cell won’t be shown. Is there a easy way to show more than one unit cell in the LatticeData image?

google sheets – jump to cell using dropdown list data validation

I’m trying to create a data validation/dropdown list to jump to a specific cell.

I have a large spreadsheet that I need to navigate around, and it is client facing. I’d love to have a dropdown list based off a data validation that could jump to a specific cell so that navigation is a little more straightforward.

I know you can type do “help/go to range” and jump to a specific cell,

But I’m hoping to create a dropdown menu that isn’t hyperlink based. Something that says the content of the cell not just its name.

For instance, I want to jump to cell A9, but would like it to say the text of B9 which in this example is “shot001_001”

So the data validation would be a list of Shot_001_001, Shot_001_002, Shot_001_003 etc. (names will change later) But the function would be the same as “go to range”

is that even possible?

field of view – What is the focal length on your typical cell phone camera?

Focal length does not change with sensor sizes – what actually changes is the field of view:

FOV (°) = 2 * arctan ( d (mm) / (2 * f (mm)) )

FOV is our field of view in degrees, d is one of the dimensions of the sensor (for diagonal, it is d = √(h² + w²) ) and f is the focal length in millimeters.

In very simple terms, a smaller sensors sees a smaller portion of the lens’s projection – e.g. a APS-C sensor does not fill out a 35mm lens’s image circle, so it cannot see the outer portions (which relate to the outer regions of the frame) and therefore, the field of view is narrower than it would be with a full frame camera.

When we talk about crop-factors, what we really are talking about is the change in field of view by a smaller sensor: The reference is a full frame sensor, which is 36*24mm. Therefore, a 10mm lens on an APS-C camera is still a 10mm lens – it just happens that it offers the same FOV as a 15mm lens would on a full frame camera.

The same of course is true for smartphones – unfortunately, the smartphone market is constantly and rapidly changing, and manufacturers usually do not state focal length or sensor size in their marketing texts, so research is needed on a per-phone-basis (or so I think).


A (at this moment) recent iPhone X offers an equivalent-to-28mm lens and an equivalent-to-52mm lens, the Samsung Galaxy S9+ offers 26/52mm, the OnePlus 6 offers 25mm, the Huawei P20 offers 27mm/80mm, the Nokia 6.1 offers 27mm,…

So it seems we can conclude that 26-28mm (equivalent) are a standard in Q3/2018.