Sally has attended:
Excel Introduction course
Excel Intermediate course
PowerPoint Intermediate Advanced course
Excel - 3D formulas
I cant understand the instructions in the intermediate manual
In Sheet one called IF Function I have a list of commission amounts (column G)
In Sheet two called Conditional, I have a list of totals (column F)
I want to take the commission amounts from sheet one and add them to the totals in sheet two creating new figures in column G - ie totals with commission added.
THe manual tells me to select the cell where the formula is to be entered: this is col G in sheet two. It then tells me to click on sheet tab of the first worksheet to be entered into the formula (sheet two)). I am then told to hold shift key and click on the sheet tab for the last worksheet to be entered into the formula - sheet one. At this point, the new formula in sheet two, col g reads: =SUM('IF function:Conditional'!
I am then told to highlight the cell on the sheet I have just selected - sheet one. I then go back to sheet two and the formula just says =SUM(Conditional!G6. The IF function name has disappeared. G6 is the cell ref in sheet one but G6 in sheet two is where the formula is. I then completed the formula to read =SUM(Conditional!G6+F6) but it didnt work as the IF function (sheet one) no longer appeared in the formula.
I assume the formular should read: =Sum('IF Function:Conditional'!G6+F6). If I enter manually, an error message of REF comes up in the cell.
RE: Excel - 3D formulas
Hello Sally
Thank you for your question.
Can you please let me know which page the exercise is on that you are referring to? I gather that this is for a practice activity in the handbook you are given on the course?
Kind regards
Amanda
RE: Excel - 3D formulas
H iAmanda
The exercise for 3D formulas is on page 14of themanual we used on the training day
RE: Excel - 3D formulas
Hi Sally
A 3D formula is used to refer to values that are in the same cells in multiple sheets.
In this case because the totals in one sheet are in a different column to the commission amounts in the second sheet, a 3D formula wouldn't work to add the amounts together, since the totals and the commission amounts are not in the same cells on each sheet.
Also when you are putting together a formula which refers to cells on different sheets to where the formula is in the workbook, always use Enter to finish inputting the formula, otherwise you tend to run into problems.
You could add the two amounts together by:
1. Entering the Sum function where you want the formula result to go =sum()
2. Position your cursor inside the brackets ()
3. Go to the first sheet and select the first cell to be included in the addition.
4. Type in a comma after selecting the cell
5. Go to the second sheet and select the next cell you'd like to include in the addition.
6. Press Enter to complete the formula and return to the sheet where you started.
Kind regards
Amanda
