Excel help

[Jessie]

I have a template in a workbook that I need to copy like 30 times.  Is there a way to copy the sheet multiple times in the same workbook without doing each copy individually?

Anyone know?

[Gamplayerx]

Not that I know of.  Move or Copy Sheet seems to only let you do it one at a time.

[Gamplayerx]

Ooh.  It might be faster to hold control and drag the sheet over.  But you'd still have to do it 30 times.

[Jessie]

Well, I guess I'll do it how I've done it in the past, and how alice suggested, which is make a copy, then copy both copies, then all four copies, rinse, repeat til I have enough.

[Gamplayerx]

Oh, I misunderstood.  I thought you wanted to copy a template into a new sheet.  If you're just copying them into the same spreadsheet, just select what you want to copy, hit copy, then hold the control key and choose all the cells where you want to start the pasting, and then hit paste.  It'll paste it everywhere you've selected.

[Jessie]

You're still misunderstanding, but I'm already done, so it's ok!

Thanks though.

[Jessie]

Ok, here's another question. 

I'm basically setting up a database of all my work comp injuries for the year, with a sheet for each injured employee.

The last sheet in the book will show the total paid by insurance, and the total paid inhouse.

I'd also like for it to make a list of the injured employees, and their date of injury.

I can't for the life of me figure out how to do this, save for going in each cell that would comprise the list, and do the function ='sheetname'!cell

That's way too much work, and I'd rather do something else.  I am guessing this will require writing a macro or something, which I know nothing about.

Anyone know of an easy way to do this or am I SOL?

[Jessie]

It'd be ok if I could do that function and then drag to copy, but it just grabs the next cell in the same worksheet, and I need it to grab the same cell in the next worksheet.

Does this even make sense?

[Gamplayerx]

Name each file the persons's name and date of injury and then print your file list.

[Jessie]

They each have a worksheet within a single workbook.  Each sheet is named 'last name first initial'. 

[Gamplayerx]

I'm not helping you anymore.  Ingrate.

Can't you query your workbook?

[BigDun]

Is what you want to do is drill to drill though all the worksheets in the book and sum all the cells at the same reference?

[Jessie]

Not exactly.  There's nothing to sum, as it's text and dates.  I want the cells B4 and B5 in each sheet to list on the final sheet in column a and column b.

Does that make more sense?

[BigDun]

Not exactly.  There's nothing to sum, as it's text and dates.  I want the cells B4 and B5 in each sheet to list on the final sheet in column a and column b.

Does that make more sense?

So:

Sheet1 B4 would be in Sheet31 A1
Sheet1 B5 would be in Sheet31 B1
Sheet2 B4 would be in Sheet 31 A2
Sheet2 B5 would be in Sheet 31 A2
.
.
.
Sheet30 B4 would be in Sheet31 A30
Sheet30 B5 would be in Sheet31 B30

Or something similar to that?

[Jessie]

yes, that's the idea.

[BigDun]

I would suggest a completely different approach.

Create a master sheet that is nothing but one row for each person and as many columns as you need to capture fields. No blank rows. No "Section" rows that identify the next person. Just one big spreadsheet with row 1 the column headers and column A the unique identifier (person's name probably).

After you all the data in the data sheet, make one spreadsheet that will display the details of whoever you pick from a drop downlist in cell A1.

Give me a minute and I'll email you a sample.

[Jessie]

I'm not sure if that'll work, as I'll be entering a different number of bills for each individual, and need to show the date of service, provider, date payment was sent, etc on each bill, and those have to be broken down by how we pay (insurance or in house) so that I can total those on the final page.

If I have one row for each person, that'd be really really hard to do, as some of these people will have 50+ bills.

Unless I'm not understanding you, of course.

[BigDun]

What you are looking for is something that Excel isn't designed to do. That is what Access is designed to do. You need one sheet (table in Access) that lists all the employees static information (one record for each employee), and one sheet that lists all the claims and the pertinent info on each claim (one row for each claim). You then tie the two tables together using the employee name as the common element using a Query and display the combined data on a Form.

You can do this in Excel, but it is a lot easier in Access.

Using Access you could have unlimited number of claims per employee, but not have to duplicate the employee information for each claim.

Of course when you are finished you have a mini-app that would have to be supported. And if you left the organization and they became reliant on it, they could be in a world of hurt.

Maybe using multiple Excel sheets in a workbook is best.

[Jessie]

Well, I've got it to a point where it's doing everything I need it to do, I was just trying to do a little more.  Basically I've set up a mini-database.  I'm not really skilled in Access at all, and don't really have the time or inclination to become skilled in it right now.  Maybe one day I'll transfer all the data over.  Maybe not. 

What I have set up is a hell of a lot better than the big bunch of nothing they had set up before.

[Jessie]

Thanks for your attempts though.  I appreciate it!

[Jessie]

Anyone around who can help me with this issue?

I need a formula that says, "If b2 = #, then x, but if it = a different #, then y"

I can't quite figure it out.  Help?

[Gamplayerx]

[I COPIED THIS FROM THE EXCEL HELP FILE - I'M TOO LAZY TO MAKE IT LOOK PRETTY OR WEED OUT THE UNNECESSARY INFORMATION]
IF   
Show All
Hide All
Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on values and formulas.

Syntax

IF(logical_test,value_if_true,value_if_false)

Logical_test   is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.

Value_if_true   is the value that is returned if logical_test is TRUE. For example, if this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, then the IF function displays the text "Within budget". If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false   is the value that is returned if logical_test is FALSE. For example, if this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, then the IF function displays the text "Over budget". If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

Remarks

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (See Example 3 for a sample of nested IF functions.) If you want to test more than seven conditions, consider using the LOOKUP, VLOOKUP, or HLOOKUP function. (See Example 4 for a sample of the LOOKUP function.)
When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
If any of the arguments to IF are arrays (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), every element of the array is evaluated when the IF statement is carried out.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF worksheet function. To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. Learn about calculating a value based on a condition.
Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note   Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
   
1
2
A
Data
50
Formula Description (Result)
=IF(A2<=100,"Within budget","Over budget") If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)
=IF(A2=100,SUM(B5:B15),"") If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()


Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note   Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
   
1
2
3
4
A B
Actual Expenses Predicted Expenses
1500 900
500 900
500 925
Formula Description (Result)
=IF(A2>B2,"Over Budget","OK") Checks whether the first row is over budget (Over Budget)
=IF(A3>B3,"Over Budget","OK") Checks whether the second row is over budget (OK)


Example 3

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note   Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
   
1
2
3
4
A
Score
45
90
78
Formula Description (Result)
=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F")))) Assigns a letter grade to the first score (F)
=IF(A3>89,"A",IF(A3>79,"B", IF(A3>69,"C",IF(A3>59,"D","F")))) Assigns a letter grade to the second score (A)
=IF(A4>89,"A",IF(A4>79,"B", IF(A4>69,"C",IF(A4>59,"D","F")))) Assigns a letter grade to the third score (C)


In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

The letter grades are assigned to numbers using the following key.

If Score is Then return
Greater than 89 A
From 80 to 89 B
From 70 to 79 C
From 60 to 69 D
Less than 60 F

Example 4

In this example, the LOOKUP function is used instead of the IF function because there are thirteen conditions to test.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note   Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
   
1
2
3
4
A
Score
45
90
78
Formula Description (Result)
=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) Assigns a letter grade to the first score (F)
=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) Assigns a letter grade to the second score (A-)
=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) Assigns a letter grade to the third score (C+)

[Jessie]

I figured out an easier and less wordy way than that.  Thanks though!

For the record, this is the formula:  =IF(C16=85.43, 373.09,IF(C16=66.21, 331.46))

[Jessie]

Well, I guess it's the same thing, only the way I found it was easier to read than that.

[Jessie]

Ok, here's a question for you.  Why does this formula stop working at the 188.59 then 596.61 point?  I don't get any error messages, it's just stopping with the false there.  Everything that's not one of the earlier numbers becomes 596.61. 

=IF(C2=85.43,373.09,IF(C2=66.21,331.46,IF(C2=234.49,696.06,IF(C2=274.84,783.49,IF(188.59,596.61,IF(C2=223.18,671.56,IF(C2=402.77,1060.67,IF(C2=464.25,1193.88))))))))

I'm sure there's a simple error, but damn if I can find it.  Help?