Excel Programming

  • Thread starter Thread starter Dan Gorman
  • Start date Start date
D

Dan Gorman

I am trying to automatically move data from one cell to another cell
within the same workbook by inputting only the initial figure. Sheet 1
is the worksheet I would like to move the data to and sheet 2 contains
the column of data.

For example:

"If 8810 is entered into one of cells A1 through A10 on sheet 1, then
..052 and 2.56 from sheet 2 appear in the corresponding cells in columns
G and H, respectively on sheet 1."

Can this be accomplished in Excel? If so, what is the procedure? What
might be an easy to understand, step-by-step reference to achieve this?
 
I think you are looking for the VLookup funcion
See the Excel help for examples
 
sounds like you want to use Vlookup

in G1
=if(A1="","",Vlookup(A1,Sheet2!$A$1:$C$200,2,False))

in H1
=if(A1="","",Vlookup(A1,Sheet2!$A$1:$C$200,3,False))



then drag fill down the column,


Assumes the values being lookup are in column A in sheet2, and the value to
be returned are in B for G and C for H
 
Vlookup worked great. I was able to retrieve the correct data.
However, in the cells next to the column of cells that contain the
returned data, I have a simple multiplication formula. When these cells
are empty, #VALUE! appears. The problem is that I can not add the cells
that contain the multiplication formula together when one or more cells
contain #VALUE!.

Is there something in the vlookup formula that may be causing this?
There is no text in the data being retrieved, only numbers. I used the
vlookup formula exactly as you gave it to me in your example.

Thanks for your help.
 
You could return a 0 rather than an empty string

=if(A1="",0,Vlookup(A1,Sheet2!$A$1:$C$200,2,False))

or in your multiplication formula check for the empty string

=if(G1="","",Multiplicationformula)
 
That did the trick. One last problem: I want to search for a number
from a range of numbers in column 1 (or columns 1 and 2 of the data to
be retrieved and return the corresponding data in columns 2 and 3 (or 3
and 4).

How should the data to be retrieved be arranged on sheet 2? Should the
range (0 - 20,000) be in a single cell/column (column 1) or in separate
columns (0 in column 1 and 20,000 in column 2)? How then would the
vlookup formula be input on sheet 1? As before, the returned data will
be used in a mathematical formula from another cell.

Thank you.
 
for ordered data in Sheet2, vlookup works with the row that contains a value
no higher than the searched for value. So if you want to look for a value
between 0 and 20000, you would put 0 in Cell A1, for 20001 to 40000, then
put 2001 in A2

=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,1)


from help:

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the
largest value that is less than or equal to lookup_value.
 
Tom:

I am trying to add a column of cells which show only round numbers (no
decimal places). For some reason, the result (also a round number)
shows the sum as if the numbers in the cells being added were not
rounded.

How can I configure things so that the sum total equals the correct
total of the rounded figures showing in the cells being added?

Thank you.

Dan Gorman
 
Back
Top