Excel - Multiple values in one cell? 2

[DarrenPower]

Can anyone please tell me if it's possible to split one cell into multiples? BY that I mean if somebody is working on one project for half a day (A½) and on another project for the other half of that day (B½) is it possible to reference these values seperately?

The code used to pull out half days (for A) is:

+(COUNTIF(ALLOCATION!B7:ALLOCATION!IV7,"A½")/2)

But this only works if only A½ is the only entry in the cell, can we split it so that A½ and B½ are treated as seperate entities?

 

[AnotherHiggins]

No. A single cell is the smallest 'unit' in Excel.

Perhaps you could merge A1 and B1 so that it will look like A2 is split in half. I personally hate merging cells, but it has its uses.

To try it, select A1 and B1, then click on the toolbar button that looks like an A with an arrow coming out of each side (usually between the 'right align' and the 'currency' buttons). You can also access the feature by going to Format > Cells then on the Alignment tab clicking the box beside Merge Cells.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[gizmo1973]

or use 2 cells and then concatanate them together in cell 3 perhaps?

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[DarrenPower]

Thanks chaps, I'll go down the "two cells for each day" route and hope that no-one works on more than two sites a day!

Appreciated.

Darren.

 

[SkipVought]

darren,

and hope that no-one works on more than two sites a day!

emphasis mine

But, wishin' don't make it so, Joe!

It is a common table design error to build LIMITS into the data structure.

A better solution for a data structure would be to store the DATE, PROJECT and maybe even an INTERVAL (like how much of the day was spent on the project). This would permit any number of projects (per person, I assume - person being another column) per day.


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[DarrenPower]

The problem is that I'm posting this on behalf of someone else who designed and handles the database. I may well have done it a different way, but when I try to explain a rewrite may be needed I get the glazed-eyes closed-ears response so I slowly step back towards the door...

Thanks anyway though.

 

[SkipVought]

It's not just that it's a DIFFERENT way.

The way that it was done was DOWNRIGHT INCORRECT from the standpoint of accepted industry standards.

The fact is that spreadsheets are easy to use and easy to make drastically bad mistakes. Industry research has documented horror stories of big companies sustaining MILLION DOLLAR losses due to spreadsheet errors.

This kind of mistake will cost your company dollars in trying to recover from this amateur design.

Your glazed-eyed user needs to wake up and face reality.

Give him/her this post to read


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[DarrenPower]

Hi Skip,

I'm a complete novice when it comes to Excel, could you give me an idea as to the correct way of doing things and then I may be able to explain it better or direct him here.

Apologies for my ignorance...

 

[SkipVought]

What are LIST & TABLE PRINCIPLES for Spreadsheet Users faq68-5184

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SilentAiche]

DarrenPower:

The following simple spreadsheet design allows the flexibility Skip rightly says you need. It can handle any number of employees, any number of projects, and any number of hours. There may be a better design, but you see my point:

DATA:

Date Name Project Time
7/15/05 john a 4:00
7/15/05 jack b 2:00
7/15/05 jill b 1:30
7/15/05 john d 3:00
7/16/05 jack c 3:00
7/16/05 jill a 5:45
7/16/05 john e 1:00
7/16/05 jack a 3:00
7/16/05 jill d 0:45

Then, summerize the data with a PivotTable:

Date (All)

Sum of Time Project
Name a b c d e Grand Total
jack 3:00 2:00 3:00 8:00
jill 5:45 1:30 0:45 8:00
john 4:00 3:00 1:00 8:00
Total 12:45 3:30 3:00 3:45 1:00 24:00


You can use Grouping to further refine your summary. In fact, depending on how many employees, and how many projects, the manner in which you Group the PivotTable will influence the design of the Pivot Table. For example, from the same data another Pivot might appear like this:


Sum of Time Date
Name Project 7/15/05 7/16/05 Grand Total
jack a 3:00 3:00
b 2:00 2:00
c 3:00 3:00
jack Total 2:00 6:00 8:00
jill a 5:45 5:45
b 1:30 1:30
d 0:45 0:45
jill Total 1:30 6:30 8:00
john a 4:00 4:00
d 3:00 3:00
e 1:00 1:00
john Total 7:00 1:00 8:00
Grand Total 10:30 13:30 24:00


(I wish these things pasted better to the forum!)

You mentioned being an Excel novice, so I recommend learning about PivotTables through Help and through simply playing around with them. They really aren't difficult (as evidenced by the fact that I can do it) and they are very powerful and flexible.

Good luck!
Tim

 

[AlaskanDad]

Darren,

Could you post some sample code for me to take a look at? What do you have and what do you want it to look like?

This patient is bleeding. We can't just tell them they shouldn't be. Let's stop the bleeding AND teach them how to do it the right way.

I don't believe in telling someone that it can't be done just because the problem wasn't set up properly.

Rob

 

[AnotherHiggins]

DarrenPower,

Skip is right. For what you want to do, you should definitely set up a table. Go with what he and SilentAiche suggest.

[a bit off topic]
SilentAiche (Can I call you (h)?),

to help things line up with pasting a table, use [ignore][tt][/tt] around and text you want to be MonoSpaced. Using [tt][/ignore], it is easy to make what you posted look like this:
[tt]
Date Name Project Time
7/15/05 john a 4:00
7/15/05 jack b 2:00
7/15/05 jill b 1:30
7/15/05 john d 3:00
7/16/05 jack c 3:00
7/16/05 jill a 5:45
7/16/05 john e 1:00
7/16/05 jack a 3:00
7/16/05 jill d 0:45
[/tt]

For a full listing of TGML options, click on the 'Process TGML' link below the reply box.
[/a bit off topic]

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SkipVought]

Rob,

If you read the thread, he already got a "solution".

I added my suggestions because he had stated

and hope that no-one works on more than two sites a day!

pointing out that data struture limitations were and endemic problem with unsophisticated users (in so many words) It will take him a while to get informed.

We don't have very much else to work with, currently.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SilentAiche]

anotherhiggins:

Thanks for the tip on copying tables so they are suitable for the human eye- I suspected there was a way (although I expected a box, like Codes or Quotes). I was just getting ready to post a general question on the subject since I've seen others post tables as squirrelly as mine.

Next- is there a spell check option? I nede it bad...

And you may certainly call me H, but given my H's nature, no one will hear you!.

Tim

 

[AnotherHiggins]

hmm.... Maybe we should take this to another thread.

Anyway, there is no spell check (sad). I often copy my post and paste it into Word before submitting.

And I wasn't proposing to call you H, I was going to call you (h) since parentheticals are sometimes not read aloud. Dumb joke, I know.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[DaleWatson123321]

SilentAiche,

Again off-topic, but perhaps worthy of note…

I too have had my share of typos and “mis-spelled” words. I’ve recently decided it’s easier, and MUCH more effective, to do my “crafting” of Tek-Tips postings in M$ Word – and then copy/paste into Tek-Tips.

This also serves as a “back-up” in case of potential internet-related problems.

My default setting is to red-line any incorrect words as I type – and I get the “mis” in “mis-spelled” red-lined for example ;-). Word tells me it likes either: “misspelled”, or “miss-spelled”.

Hope this helps. Dale Watson

 

[SilentAiche]

Dale,

Thanks for the advice- I think I'll take it.

Perhaps by composing in Word I won't use PivotTables to summ[red]e[/red]rize data in the future.

As an aside, apparently I'm not fully understanding how to use [tt], which I tested by previewing posts. Should it be

[ tt ]
Date Name Project Time
7/15/05 john a 4:00
[ /tt ]

or

[ tt ]Date Name Project Time[ /tt ]
[ tt ]7/15/05 john a 4:00[ /tt ]

I put the extra spaces there on purpose to make the codes visible. I know there are other placement combinations for the codes, but you see my point. None of the combinations I tried fixed the PivotTables that I pasted.

Tim

 

[AnotherHiggins]

If you don't mind, please start a thread for this. Our little off topic discussion is almost as long as the relevant parts of the thread! I'll meet you in the new thread.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[SilentAiche]

SkipVought:

I don't mean to be thin-skinned, but what was it about my suggestion that earned the quotation marks? The alignment problem was already acknowledged.

If you read the thread, he already got a "solution".

Just curious.

Tim

 

[SkipVought]

Tim,

Rob seemed to imply that he had NOT received a solution when you had given him one.

The next step, is going to take him educating himself and asking for some specific help.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}