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

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

It sounds as if you want multiple vlookups.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Bismark" wrote:

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

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.


"Martin Fishlock" wrote:

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

It sounds as if you want multiple vlookups.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Bismark" wrote:

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

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

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Bismark" wrote:

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.


"Martin Fishlock" wrote:

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

It sounds as if you want multiple vlookups.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Bismark" wrote:

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

Martin,

sorry it has taken so long to get back to you.

I am not able to get this macro to work. I seem to get a compile error in
the worksheet function match(_sxldx,....

The macro does not like the underscore.

I will appreciate any additional help you or others can provide.

Thanks,

Ron (Bismark)

"Martin Fishlock" wrote:

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

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Bismark" wrote:

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.


"Martin Fishlock" wrote:

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

It sounds as if you want multiple vlookups.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Bismark" wrote:

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