Cell Referencing

B

Bismark

I am working on a workshheet that looks up values from a different worksheet
within same workbook, containing approx 1000 records which is used as a
database.
There is a field which requires updating on the database and I want to be
able to update it from my lookup sheet.
The cell to be updated has a constant column #(Say Column D ) but the row
number is variable based upon which record is being looked up at the time.
My ideal solution is a macro that will pick up the cells were I can manually
type the new value on the lookup sheet and place it into the database in
column "D" Row "...".
Any help will be greatly appreciated.
Regards,
Bismark
 
M

Martin Fishlock

Can you give some daa example it make it a lot easier to understand.

It sounds as if you want multiple vlookups.
 
B

Bismark

A simplified version of what I am doing follows:
Sheet 1 is a database
Column 1 is a unique item no
Column 2 is a date
Column 3 is a monetary value
Column 4 is a monetary value
Column 5 is a Total of 3 & 4
Each Row is a new record identified by the unique item no in in column 1

Sheet 2 is a lookup sheet
When I enter the item no it will look up sheet 1 and provide a "pretty
version" of info.

Column 4 Sheet 1 values require regular updating eg.
Currently reads: Col 3...$100, Col 4...$150, Col 5... $250 (Sum of 3 &4)

I now want to update Col 4 Sheet 1 to $175 by entering this onto a field on
Sheet 2 (Lookup Sheet).

I hope this clarifies.

thanks,


Ron.
 
M

Martin Fishlock

Bismark,

You could use data validiation to get the data and then a button to say
update values and you use a macro to update the values in the database.

The macro could look like this and be activated by a button.

sub updatedb()
dim szIdx as string
dim cValue as currency
dim lRow as long
with worksheets("Sheet 2")
szIdx= .range("B2")
cValue =.range("B3")
end with

lrow = application.worksheetfunctions.match( _
szIdx, worksheets("Sheet1").range("A:E"),0)
worksheets("Sheet1").range("D1").offset(lrow -1) = cValue
cValue = cValue + worksheets("Sheet1").range("C1").offset(lrow -1)
worksheets("Sheet1").range("E1").offset(lrow -1) = cValue

end sub
 
Top