Question about Microsoft Windows Vista Home Basic for PC

1 Answer

Finding the right formula

I want a formula that when a number is placed in a column (say e) on one sheet, it will pick up the text from another column in the same line (say b), and transfer that text into a selected column/line in a different sheet, but within then same book!!

Posted by on

  • 1 more comment 
  • enquiries122 Mar 13, 2009

    Hi botaksetan. The connection is column b on the first spreadsheet is a headings column, i.e it is a describtion of the material/service provided to a customer, column e is the column that i input a figure which relates to the quantity required. What I am hoping to achieve is when I put the qty figure in column e, i want the description in column b to transfer to a specific cell on another spreadsheet within the same workbook. This is to safe me having to constantly retype everything from the one sheet to the other.

  • enquiries122 Mar 13, 2009

    This works brilliantly, thank you very much. You have managed to save me countless hours of duplicate typing when to estimates. FANTASTIC!!

  • Adi Sunardy
    Adi Sunardy May 11, 2010

    Gimme more detail, so i could help...

    What is the connection between Column e and colum b? Can you just give a brief example?


×

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points

    MVP:

    An expert that got 5 achievements.

    Mentor:

    An expert who has written 3 tips or uploaded 2 video tips.

    Governor:

    An expert whose answer got voted for 20 times.

  • Expert
  • 80 Answers

Assumption:

  1. we work on sheet1, cell b1 as description of material and cell e1 as quantity figure.
  2. we will put the transfer the text in cell b1 to sheet2 cell a1 if you have entered some value in sheet1 cell e1. Otherwise sheet2 cell a1 will left empty or display some text as "NONE" or as you wish.
what you should do is make a formula in sheet2 cell a1 like this:

=IF(Sheet1!e1<>"",Sheet1!b1,"")

The formula will check sheet1 cell e1, if there is any number or text put in there, the value of Sheet1 cell b1 will be copied to sheet2 cell a1. If you don't supply any number or text in sheet1 cell e1, there's nothing will appeared in sheet2 cell a1.

If you want some text will display when there's no value in sheet1 cell e1 just change the formula like this:

=IF(Sheet1!e1<>"",Sheet1!b1,"NONE")

You can modify it as your like. Just copy the formula to another row in sheet2 column a.


P.S.: If this information was helpful, please rate this solution.
Finding the right formula - 1ak+f4pj7cbm32cuniyi2gaaaaaelftksuqmcc

Posted on Mar 13, 2009

  • Adi Sunardy
    Adi Sunardy Mar 13, 2009

    Please let me know if you have solved this problem or need further help. Thanks

  • Adi Sunardy
    Adi Sunardy Mar 14, 2009

    It's my pleasure to help :)

×

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

How to write vertically in microsoft word


Do this:

1.Select the text you want in Landscape orientation.
2.Click the Page Layout tab on the Ribbon.
3.Click the arrow in the lower right corner of the Page Setup group.
4. In the Apply To list box, select Selected Text or, if you want the entire document, select Whole Document.
5.Click OK.

Or, if you don't want all the text on the page turned, do this:

1. Insert a table with enough columns to enable you to isolate your vertical text in its own column.
2. Put the text in that column.
3. Right-click in that column and select Text Direction.

Now you can turn that text while the rest of the page stays horizontal. You can turn off the table cell borders so they don't print and no one will know the difference.

Hope that helps.

Jan 16, 2011 | Operating Systems

1 Answer

Describe the each part of microsoft excel 2207


anmolsxn_0.gif
Parts of the Excel 2007 Screen

Active Cell In an Excel 2007 worksheet, the cell with the black outline. Data is always entered into the active cell.
Column Letter Columns run vertically on a worksheet and each one is identified by a letter in the column header.
Formula Bar Located above the worksheet, this area displays the contents of the active cell. It can also be used for entering or editing data and formulas.
Name Box Located next to the formula bar, the Name Box displays the cell reference or the name of the active cell.
Row Number Rows run horizontally in an Excel 2007 worksheet and are identified by a number in therow header.
Sheet Tab Switching between worksheets in an Excel 2007 file is done by clicking on the sheet tab at the bottom of the screen.
Quick Access Toolbar This customizable toolbar allows you to add frequently used commands. Click on the down arrow at the end of the toolbar to display the toolbar's options.
Office Button Clicking on the Office Button displays a drop down menu containing a number of options, such as open, save, and print. The options in the Office Button menu are very similar to those found under the File menu in previous versions of Excel.
Ribbon The Ribbon is the strip of buttons and icons located above the work area in Excel 2007. The Ribbon replaces the menus and toolbars found in earlier versions of Excel.
Here are the main parts of Microsoft Excel 2007. Thank you for using Fixya !!!

Nov 15, 2010 | Microsoft Windows XP Professional

1 Answer

Convert numbers to text in excell


  1. Start Microsoft Excel.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code into the module sheet. Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion " ' String representation of amount. MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none. DecimalPlace = InStr(MyNumber, ".") ' Convert cents and set MyNumber to dollar amount. If DecimalPlace > 0 Then Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2)) MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)) End If Count = 1 Do While MyNumber <> "" Temp = GetHundreds(Right(MyNumber, 3)) If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumber = Dollars & Cents End Function ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber) Dim Result As String If Val(MyNumber) = 0 Then Exit Function MyNumber = Right("000" & MyNumber, 3) ' Convert the hundreds place. If Mid(MyNumber, 1, 1) <> "0" Then Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred " End If ' Convert the tens and ones place. If Mid(MyNumber, 2, 1) <> "0" Then Result = Result & GetTens(Mid(MyNumber, 2)) Else Result = Result & GetDigit(Mid(MyNumber, 3)) End If GetHundreds = Result End Function ' Converts a number from 10 to 99 into text. Function GetTens(TensText) Dim Result As String Result = "" ' Null out the temporary function value. If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19... Select Case Val(TensText) Case 10: Result = "Ten" Case 11: Result = "Eleven" Case 12: Result = "Twelve" Case 13: Result = "Thirteen" Case 14: Result = "Fourteen" Case 15: Result = "Fifteen" Case 16: Result = "Sixteen" Case 17: Result = "Seventeen" Case 18: Result = "Eighteen" Case 19: Result = "Nineteen" Case Else End Select Else ' If value between 20-99... Select Case Val(Left(TensText, 1)) Case 2: Result = "Twenty " Case 3: Result = "Thirty " Case 4: Result = "Forty " Case 5: Result = "Fifty " Case 6: Result = "Sixty " Case 7: Result = "Seventy " Case 8: Result = "Eighty " Case 9: Result = "Ninety " Case Else End Select Result = Result & GetDigit _ (Right(TensText, 1)) ' Retrieve ones place. End If GetTens = Result End Function ' Converts a number from 1 to 9 into text. Function GetDigit(Digit) Select Case Val(Digit) Case 1: GetDigit = "One" Case 2: GetDigit = "Two" Case 3: GetDigit = "Three" Case 4: GetDigit = "Four" Case 5: GetDigit = "Five" Case 6: GetDigit = "Six" Case 7: GetDigit = "Seven" Case 8: GetDigit = "Eight" Case 9: GetDigit = "Nine" Case Else: GetDigit = "" End Select End Function
uparrow.gifBack to the top How to use the SpellNumber sample function loadTOCNode(2, 'moreinformation'); To use the sample functions to change a number to written text, use one of the methods demonstrated in the following examples: uparrow.gifBack to the top Method 1: Direct Entry loadTOCNode(2, 'moreinformation'); You can change 32.50 into "Thirty Two Dollars and Fifty Cents" by entering the following formula into a cell: =SpellNumber(32.50) uparrow.gifBack to the top Method 2: Cell reference loadTOCNode(2, 'moreinformation'); You can refer to other cells in the workbook. For example, enter the number 32.50 into cell A1, and type the following formula into another cell: =SpellNumber(A1) uparrow.gifBack to the top Method 3: Paste Function or Insert Function loadTOCNode(2, 'moreinformation'); To enter a custom function into a worksheet, you can use Paste Function in Excel 2000, or you can use Insert Function in Excel 2002 and in Excel 2003. Excel 2000 loadTOCNode(3, 'moreinformation'); To use Paste Function, follow these steps:
  1. Select the cell that you want.
  2. Click Paste Function on the Standard toolbar.
  3. Under Function category, click User Defined.
  4. Under Function name, click SpellNumber, and then click OK.
  5. Enter the number or cell reference that you want, and then click OK.
Excel 2002 and Excel 2003 loadTOCNode(3, 'moreinformation'); To use Insert Function, follow these steps:
  1. Select the cell that you want.
  2. Click Insert Function on the Standard toolbar.
  3. Under Or select a category, click User Defined.
  4. In the Select a function list, click SpellNumber, and then click OK.
  5. Enter the number or cell reference that you want, and then click OK.

Apr 01, 2010 | Microsoft Windows XP Professional

1 Answer

How do you get the spread sheet to keep the zero when you enter zip code with a leading zero


Spreadsheet software automatically assumes any numerical data entered as a number. What you need to do is to tell the software to consider tsome selected cells entries as a text. To do so
1. Select the cell, column or row in which you right your zip code
2. Right click on your selection and click on format cells...
3. On the coming dialog box, under the Number tab click Text for the category list
4. Click Ok. and you are done.

Nov 03, 2009 | Microsoft Windows Vista Ultimate Edition

2 Answers

Sorting a single column with both first name and last name


are you using mssql then it must be something like this

Select firstname + ' ' + 'lastname' as 'name' from tablename order by lastname

Sep 26, 2009 | Operating Systems

1 Answer

How to get all balance sheet entries tally to excel


This is a very handy process when you're totaling or subtotaling columns. On the cell that you want the 'total' in type '=sum(column letter row number),(column letter row number). The first 'column letter row number' is where you want the first cell to be started in the total factor and the second 'column letter row number) is the last cell you want added in the total factor. The help (?) section is good at explaining formulas. Hope this helps, keep this process handy if you use Excel much because it'll be helpfull each time you subtotal or total columns.
Bob

Sep 23, 2009 | Operating Systems

2 Answers

Regarding Excel formulas


Yes, its possible by designing a formula in excel.

Aug 03, 2008 | Microsoft Windows XP Professional

1 Answer

Flex Grid


The most common multidimensional array, the two dimensional array table, is best presented to your users in row and column format. The grid control offers a convenient way for you to display table data to your users. The users can navigate the table's values using scrollbars. Therefore, the grid control does not have to be as large as the table, because the grid control automatically displays scrollbars.
To add the flex grid control to your toolbox, select project and components. Add the flex grid control:
lesson97-1.jpg Once it is added, you will see the yellow flex grid control in your toolbox:
lesson97-2.jpg When you place the grid control on your form, you will have to resize it before the control takes on a tabular appearance. As you expand the size of the control, it does not look to much like a table. The problem is that the table's default number of rows and columns are two. To fix this problem, you must configure the rows and columns in the properties window:
lesson97-3.jpg lesson97-4.jpg lesson97-5.jpg The grid control supports fixed rows and columns. These refer to rows and columns in a grid control that do not scroll when the user clicks the scrollbars. The fixed rows and columns provides labels that describe the data. The fixed rows and columns are often called row and column headers.
lesson97-6.jpg When working with the grid control, much code is needed to provide the grid with functionality. Call statements are used to break the code required by the grid control into smaller, more manageable procedures. This is referred to as modular programming (which refers to the practice of placing code with a single purpose in a general subroutine procedure and then calling the code from a second procedure.)
Here is the code for the form load procedure (this example is based on the computer disk example used in the last lesson)


Private Sub Form_Load()
Call SizeCells
Call CenterCells



grdGrid.Row = 0
grdGrid.Col = 1
grdGrid.Text = "Single Sided; Low Density "
grdGrid.Col = 2
grdGrid.Text = "Double Sided; Low Density"
grdGrid.Col = 3
grdGrid.Text = "Singled Sided; High Density"
grdGrid.Col = 4
grdGrid.Text = "Double Sided; High Density"

grdGrid.Row = 1
grdGrid.Col = 0
grdGrid.Text = "3 1/2 inch"
grdGrid.Col = 1
grdGrid.Text = "$2.30"
grdGrid.Col = 2
grdGrid.Text = "$2.75"
grdGrid.Col = 3
grdGrid.Text = "$3.20"
grdGrid.Col = 4
grdGrid.Text = "$3.50"



grdGrid.Row = 2
grdGrid.Col = 0
grdGrid.Text = "5 1/4 inch"
grdGrid.Col = 1
grdGrid.Text = "$1.75"
grdGrid.Col = 2
grdGrid.Text = "$2.10"
grdGrid.Col = 3
grdGrid.Text = "$2.60"
grdGrid.Col = 4
grdGrid.Text = "$2.95"

End Sub
Notice how the form load procedure is used to populate the cells in the grid control. To control cell size and cell alignment, two smaller procedures are created and each procedure is called by the form load procedure.


Private Sub SizeCells()
Dim intColumn As Integer

grdGrid.ColWidth(0) = 1100

For intColumn = 1 To 4
grdGrid.ColWidth(intColumn) = 2200
Next intColumn

End Sub

Private Sub CenterCells()
Dim intColumn As Integer

For intColumn = 1 To 4
grdGrid.ColAlignment(intColumn) = flexAlignCenterCenter
Next intColumn

End Sub

Notice that the size and alignment procedures are only applied to columns 1 through 4. Column 0, which is a fixed column reserved for labels, is not formatted using either of the above two procedures.
Here is the final result:
lesson97-7.jpg

Mar 26, 2008 | Operating Systems

1 Answer

Works suite 2006 spreadsheet


THERE MAY BE ERROR IN OFFICE INSTALLATION SO PLZ UNINSTALL YOUR OFFICE AND REINSTALL THE OFFICE

Mar 02, 2008 | Microsoft Windows XP Home Edition

Not finding what you are looking for?
Microsoft Windows Vista Home Basic for PC Logo

130 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Operating Systems Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Scott Fryer

Level 2 Expert

80 Answers

Carlos L. Burgos
Carlos L. Burgos

Level 2 Expert

508 Answers

Are you a Microsoft Operating System Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...