Automating a Column 2

[dik]

Attached is a part spreadsheet.

In column A, I increment the value and copy the results. This automatically increases the number in the cell.

In the next column, I increment the date. Is there a means of automatically doing this as I've done in the first column?

In the last column, I increment the day of the week. Is there a means of automatically doing this?

thanks, Dik

 

[SkipVought]

Hi,

Why the empty rows? Not a particularly good spreadsheet design.

Maybe you need to explain what the purpose of this sheet is.

"In column A, I increment the value and copy the results. This automatically increases the number in the cell."

How are the values automatically incremented?

"In the next column, I [highlight #FCE94F]increment[/highlight] the date"
[pre]
No. Date
2001 23/Apr/18
2002 24/Apr/18
2003 25/Apr/18
2004 26/Apr/18
2005 [highlight #FCE94F]27[/highlight]/Apr/18
2006 [highlight #FCE94F]27[/highlight]/Apr/18

2007 [highlight #FCE94F]28[/highlight]/Apr/18
2008 [highlight #FCE94F]28[/highlight]/Apr/18
'...
[/pre]
[highlight #FCE94F]???[/highlight]

"In the last column, I increment the day of the week. Is there a means of automatically doing this?"

Yes, If you 1) get rid of empty rows and 2) use a formula [tt]=TEXT(B2,"ddd")[/tt], convert your table to a Structured Table (Insert > Tables > Table) which will "automatically" propagate formulas to new rows.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

Thanks, Skip... wasn't intended to be a great spreadsheet... just a quick knockoff...

Dik

 

[SkipVought]

Here's an example of how I'd see the "automation"
Link

Enable Macros to run

Simply select the next cell in column A

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

thanks, Skip

Dik

 

[SkipVought]

I just realized that you sent a .xls (Excel ‘97-2003) and Structured Tables are Excel 2007[sup]+[/sup]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

Thanks Skip... I'll check it out next time I'm at the office.

Dik

 

[IDS]

Structured tables are great, but there are plenty of times you might not want to use them.

To increment the date, say starting in cell B2, enter the date as a date number (i.e. just type it in, in a recognised format), then in cell B3 and below enter =B2+1. To display the day of week for that date in column C, enter =B2 and apply a custom format of "ddd":

Press Ctrl-1
Select the Number tab, and custom category
Enter ddd under Type.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Of course, you can manually add a =B2+1 or propagate each formula in the row manually, but the OP wants “automatic” stuff to happen when he/she decides to add a row. That’s the beauty of a Structured Table.

But then, in any design analysis, there are tradeoffs. No design can be optimal for every single design element. You could say that for Excel, for comparing Excel to Access or some other DB application for a project, some bridge or a missile defence system.

That’s why for me, a contributor, its often helpful to know the overall objective before giving a suggestion.

There’s more than one way to skin a cat.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

There’s more than one way to skin a cat.

Absolutely

The main reason I posted was that I didn't know/had forgotten that you can get the day of the week displayed as text by formatting a date number as ddd, rather than using the Weekday function with a list of days.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dik]

IDS and Skip... I don't think I ever knew that...

Dik

 

[SkipVought]

FYI, in many systems, date/time values are just numbers.

In Excel today, Sunday May 13, 2018, is 43233: that is 43233 days since December 31, 1899.

What you see displayed is not what Excel stores. Excel "sees" you enter 2018/5/13 and makes the assumption based on the characters that you have entered, that you intended the value to be a date and converts what you entered to 43233, but displays it as 5/13/2018 in the USA or 13/5/2018 across the pond. In fact, when I enter 5/13 here in Texas, Excel assumes I want to enter May 13 of the current year and converts accordingly.

There are all kinds of ways that you can display dates using various Number Formats. Some are canned and others are left to your imagination for displaying days as d, dd, ddd, dddd months as m, mm, mmm, mmmm or years as yy, yyyy or any combination thereof.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

Thanks Skip for the date number reference... decided to format col B as a date and everything works well for my simple sheet. For the total line, kept the same cell reference without incrementing it and coloured the font white. Attached.

https://files.engineering.com/getfi...b5f9-4104-999a-939685ea0708&file=eng-tips.xls

Dik

 

[SkipVought]

There's a better, easier way to subtotal as in your sheet. Just need another column based on Date.

Inserted a WK (week) column in column A. Formula
[tt]
A2: =INT(C2/7)
[/tt]

Deleted your subototal columns.

so here's the starting point for using the Subtotal outline feature.
[pre]
WK No. Date Project Number Client Project Time S Details Day
6176 2020 2018/05/12 Sat
6176 2021 2018/05/13 Sun
6176 2022 2018/05/14 Mon
6176 2023 2018/05/15 Tue
6176 2024 2018/05/16 Wed
6176 2025 2018/05/17 Thu
6176 2026 2018/05/18 Fri
6177 2020 2018/05/19 Sat
6177 2021 2018/05/20 Sun
6177 2022 2018/05/21 Mon
6177 2023 2018/05/22 Tue
6177 2024 2018/05/23 Wed
6177 2025 2018/05/24 Thu
6177 2026 2018/05/25 Fri
[/pre]

Added the Subtotal Feature
Data > Outline > Subtotal ...
At each change in WK
Use function SUM
Add Subtotal to S

See your workbook modified uploaded.

BTW, have no idea what field No. does.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

just a sequential number... no other purpose.

Thanks, gentlemen...

Dik

 

[SkipVought]

The sequential number repeats each week?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dik]

shouldn't unless I screwed up... will check. thanks

Dik

 

[SkipVought]

The beauty of using the Subtotal feature is that your table design can be contiguous, with initial values in the first data row of the table and the incriment formulae entered once per formula and copied straight down, since there are no gaps. The Subtotal feature creates the subtotal gaps in the table columns without any extra effort.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]