Excel question

[wlu_lax6]

Not a question, but this popped into my youtube feed and I found it funny.

My boss at Microsoft was the lead tester for that version of Excel.
Also for those software folks who know "Joel on Software", Trello, Stack Exchange, Stack Overflow...the founder of those companies, Joel was a superstar on that version of Excel.

[Steve of phpBB]

You can autofill multiple columns or rows at the same time?

[Shirley]

Not a question, but this popped into my youtube feed and I found it funny.

My boss at Microsoft was the lead tester for that version of Excel.
Also for those software folks who know "Joel on Software", Trello, Stack Exchange, Stack Overflow...the founder of those companies, Joel was a superstar on that version of Excel.

I'm a big Joel Spolsky fan. I miss his blog. Did you know him at MS?

[sancarlos]

Hey, Shirley, Wlu, and DiS - anybody familiar with a company called MAQ Software?

[wlu_lax6]

Not a question, but this popped into my youtube feed and I found it funny.

My boss at Microsoft was the lead tester for that version of Excel.
Also for those software folks who know "Joel on Software", Trello, Stack Exchange, Stack Overflow...the founder of those companies, Joel was a superstar on that version of Excel.

I'm a big Joel Spolsky fan. I miss his blog. Did you know him at MS?

He was gone by the time I joined but knew folks who knew him and this story was something I heard about before he wrote about it.

https://www.joelonsoftware.com/2006/06/ ... lg-review/

[wlu_lax6]

Hey, Shirley, Wlu, and DiS - anybody familiar with a company called MAQ Software?

Nope

[Shirley]

Hey, Shirley, Wlu, and DiS - anybody familiar with a company called MAQ Software?

nope

[govmentchedda]

[rass]

#REF!

[govmentchedda]

[P.D.X.]

Let's say I have Column A as "Item Inventory" and Columns B, C, D, etc as different retailers and when I want to input an item quantity into one of those retailers I'd like it to subtract from the Column A inventory. How's a person supposed to do that?

[mister d]

If this is like you have inventory but need it to be “claimed” or whatever if it’s going through a 3rd party retailer, I think you need another column. So new column A is base units then Item Inventory (new column B) equals A minus retailers C, D and E?

[EnochRoot]

Let's say I have Column A as "Item Inventory" and Columns B, C, D, etc as different retailers and when I want to input an item quantity into one of those retailers I'd like it to subtract from the Column A inventory. How's a person supposed to do that?

Running Inventory.

SUMIF

[govmentchedda]

Anyone know of a way to color code a cell within a column based on the data in that column? Like, if I had a grid of fantasy stats walking into a draft that had columns for each category and different rows for each player and I wanted to be able to colorize the background of each cell to show how many standard deviations away from the mean said player was in each category? Trying to set up myself visually for a fantasy draft.


- I suppose I could just have some sort of heading that told me what the standard deviations from each were, but I'd love something visual that would make the choice easier when I've only got 60-90 seconds to make my pick.

[A_B]

It's available under conditional formatting. That can get a bit confusing depending on how many different formats you anticipate, but if you use the SD as your flag I think it wouldn't be too burdensome. Might be an extra calculation you have to put in there, but i've never worked with conditional formatting using SDs, usually just ranges.

[mister d]

Yeah, the auto-format one works well unless you have drastic outliers which sets everyone else to a normal color.

[govmentchedda]

Might circle back with you guys in 6 months or so to make sure I'm doing this right. Somebody needs to knock Sancarlos down a few pegs in Oiler's fantasy hockey league!

[Steve of phpBB]

Okay, I hope someone can help me quickly.

Is there any easy way to take a batch of numbers that are now there because of a formula, and instead make them permanent?

In other words, right now a certain box in says 12 because the box above it says 11 and it's using the formula [box above]+1. But if I reorder the entries based on the values in another column, that box will move and the number in it will change. So I would like to now turn the [box above]+1 into "12", so that it will stay 12 when I reorder the entries. And because I have 500 exhibits, I really don't want to do this manually.

[mister d]

Unless I'm misunderstanding, its just "Copy -> Paste Special -> Paste Values".

[Steve of phpBB]

Unless I'm misunderstanding, its just "Copy -> Paste Special -> Paste Values".

Yeah, I was coming back to say I came across this solution. I created a new column right next to the original one, copied things over and pasted as values, and then deleted the original column.

So, no longer an emergency, but I'm wondering if there's a way to do that within a box or multiple boxes, other than copying them into a new column or manually typing everything.

[mister d]

You could have just pasted over the formulas rather than in a new column.

[Johnny Carwash]

If I had a series of codes like this:

ABC.1.2
ABC.2.3
ABC.3.4

And I wanted them to look like this:

ABC.01.002
ABC.02.003
ABC.03.004

Is there an easy way to do so with a formula or other function? Note that the number parts in the original codes could be multiple digits (eg. ABC.10.22), so a simple Find/Replace or Concatenate wouldn't work. Any thoughts?

[mister d]

Its clunky, but it would work if you did text to columns using the . to get columns A, B and C populated and then in column D wrote it as =B1&"."&TEXT(C1,"00")&"."&TEXT(D1,"000") and then if you wanted you could just hardcode that column back into your file.

[Johnny Carwash]

Its clunky, but it would work if you did text to columns using the . to get columns A, B and C populated and then in column D wrote it as =B1&"."&TEXT(C1,"00")&"."&TEXT(D1,"000") and then if you wanted you could just hardcode that column back into your file.

This worked, thanks!