Excel assistance

[EagleKeeper]

1) I have an invoie that I use that has 2 fields related to data. - Answered in posts below
One field is a mm/dd/yy field.
The second is the invoice number which is built from the data field as yyyymmdd-01

What I would like to do is enter the date in the mm/dd/yy field and have some algorithrm (simple) to prepare the yyyymmdd

the mm/dd/yy is defined as a Date Formatted cell




2) The invoice template that I grabbed from MS Office has Description fields that seem to be merged cells for columns D->K to generic text entry

I have a need to enter invoice hours also into a second description line. No problem.
I would like to be able to enter those hours into cells EFGHIJK so they can be automatically summed up into a differnet cell where the invoice is calculated.

However, becuase of the "merge" previously described, I can not seem to get to the unique cells, but to the overall merged cell.

Is there a way to unmerge a row of cells wihtout affecting the previous formattting.

In other words
Row #19 should be the merged format
Row #20 should be in the cell format


3) Is there a way to look at the formatting of the spreadsheet so I can enhance it the way I desire?

 

[SilentRunning]

Originally posted by: Common Courtesy
1) I have an invoie that I use that has 2 fields related to data.
One field is a mm/dd/yy field.
The second is the invoice number which is built from the data field as yyyymmdd-01

What I would like to do is enter the date in the mm/dd/yy field and have some algorithrm (simple) to prepare the yyyymmdd

the mm/dd/yy is defined as a Date Formatted cell

If A1 is the reference cell with date:

=TEXT(YEAR(A1),"0000")&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00")&"-01"

 

[mayest]

For question 1, you can easily convert that date into an invoice number. Just use the Text() function. Assume that the date is in A1, then this will do it:

=TEXT(A1,"YYYYMMDD")

If you want to append a dash and a number to that, then do this:

=TEXT(A1,"YYYYMMDD")&"-01"

Depending on your needs, that 01 part may need to be calculated or referenced from another cell. So, say that B1 contains that number, then this will do it:

=TEXT(A1,"YYYYMMDD")&"-"&Text(B1,"00")

Not sure that I understand question 2, so I'll leave that to somebody else.

 

[EagleKeeper]

Question 1 is now answered and works

The -01 is tracking incase multiple invoices are generated on the same date. Only has happened twice - but it makes the invoice number look more impressive😛

 

[JJChicken]

CC, regarding Q2, I'm not sure this would help but look at http://en.allexperts.com/q/Exc...9/un-merge-cells-1.htm

Also, could you be more specific on Q3? And what version of excel do y ou have - 2003 or 2007, because I believe you can preview format changes in 07 (but u can't in 03)

 

[EagleKeeper]

'03 is what I have.

What I was hoping to do with respect to #3 is to see the formatting that was used and then duplicate it when creating a new spreadsheet. Knowing the formatting and what I want to tweak, be able to make those tweaks early on. (column & row infomration adjustments)

 

[JJChicken]

Originally posted by: Common Courtesy
'03 is what I have.

What I was hoping to do with respect to #3 is to see the formatting that was used and then duplicate it when creating a new spreadsheet. Knowing the formatting and what I want to tweak, be able to make those tweaks early on. (column & row infomration adjustments)

Not too sure I can help you, but try copying the original spreadsheet and pasting it into the new spreadsheet using "paste special -->format" (use cntrl+alt+p i think) to copy across the formatting but not the data. To get row and column information I think you point your cursor on the edge of the row/column (in the grey section to the top or to the left of the spreadsheet) to get row/column width.

 

[EagleKeeper]

Thanks - will update with results