Hi,

I'm a newbie to Excel macros. If someone has a few minutes to show me how
to achieve the following I would have a nice point of departure. I have a
workbook with a few sheets. Thanks *a bunch* if there is a kind soul.

Sheet1 looks like this
101 Gas
104 Water
105 Tar
106 Paint

Sheet2 looks like
101 23
104 0.5
106 89

Sheet3 looks like
104 0.7
105 34

What I need the macro to do:
I click somewhere on Sheet1's Col3 to make it the column of the active cell.
An inputbox should pop up and ask me for a sheet name. I enter Sheet3 as
the name and click OK
The macro walks through Sheet3's Col1 numbers, finds the corresponding
number in Sheet1's Col1 and copies Sheet3's Col2 into the correct row in
Sheet1's active column (which had the active cell when starting the macro).
Result after having run the macro:

Sheet1 becomes like this
101 Gas
104 Water 0.7
105 Tar 34
106 Paint

Resulting Sheet1 after having run the macro from Sheet1's Col4 and entered
Sheet2 in the inputbox:
101 Gas 23
104 Water 0.7 0.5
105 Tar 34
106 Paint 89

regards

Tor

Are you sure you want a VBA solution? Would a solution in
formulas be acceptable?

Look at www.home.gen.nz/Excel/LookupExample.xls
Change the sheets names in row 1 and the data will be taken from
the sheet with that name.

Chrissy

Tor Tveitane wrote
Hi,

I'm a newbie to Excel macros. If someone has a few minutes to show me how
to achieve the following I would have a nice point of departure. I have a
workbook with a few sheets. Thanks *a bunch* if there is a kind soul.

Sheet1 looks like this
101 Gas
104 Water
105 Tar
106 Paint

Sheet2 looks like
101 23
104 0.5
106 89

Sheet3 looks like
104 0.7
105 34

What I need the macro to do:
I click somewhere on Sheet1's Col3 to make it the column of the active cell.
An inputbox should pop up and ask me for a sheet name. I enter Sheet3 as
the name and click OK
The macro walks through Sheet3's Col1 numbers, finds the corresponding
number in Sheet1's Col1 and copies Sheet3's Col2 into the correct row in
Sheet1's active column (which had the active cell when starting the macro).
Result after having run the macro:

Sheet1 becomes like this
101 Gas
104 Water 0.7
105 Tar 34
106 Paint

Resulting Sheet1 after having run the macro from Sheet1's Col4 and entered
Sheet2 in the inputbox:
101 Gas 23
104 Water 0.7 0.5
105 Tar 34
106 Paint 89

regards

Tor

"Chrissy" wrote in message
...
Are you sure you want a VBA solution? Would a solution in
formulas be acceptable?

Look at www.home.gen.nz/Excel/LookupExample.xls
Change the sheets names in row 1 and the data will be taken from
the sheet with that name.

Thanks Chrissy, I looked at the example before breakfast now ;-)

How do you reference the cell/row with the sheetname in the formula? I did
not find any reference to row1 where you have the sheetnames. Actually I
need the sheetnames on another row in sheet1, say row13 and I did not see
immediately where to change that.

Your formula was very elegant though ;-)

Thanks Tor

Tor Tveitane wrote
Chrissy wrote
Are you sure you want a VBA solution? Would a solution in
formulas be acceptable?

Look at www.home.gen.nz/Excel/LookupExample.xls
Change the sheets names in row 1 and the data will be taken from
the sheet with that name.

Thanks Chrissy, I looked at the example before breakfast now ;-)

How do you reference the cell/row with the sheetname in the formula? I did
not find any reference to row1 where you have the sheetnames. Actually I
need the sheetnames on another row in sheet1, say row13 and I did not see
immediately where to change that.

The named range called SheetName (I think that is what I called it) is the
reference to row 1. To confirm this go to menu Insert -- Name -- Define
and then select each name in the large box and see what they reference on
the worksheet by looking at the bottom text box on that dialog.

Your formula was very elegant though ;-)

That is cos they contain named ranges which make them more readable.

"Chrissy" wrote in message
...

The named range called SheetName (I think that is what I called it) is the
reference to row 1. To confirm this go to menu Insert -- Name -- Define

Hi Crissy,

I played with you demo and tried to change some parameters to get familiar
with this. It worked fine in your test app, but when I apply it in our
'real-world' workbook I get #value errors.

Instead of naming a complete column as you did, I name a cell range, and
instead of naming a complete row as you did, I name a set of cells in the
same row. Would that be a difference?

This workbook is small, would it be possible that you could have a look at
it to point out what's the problem...?

Thanks anyway

greetings

Tor

Tory Tveitane wrote
Chrissy wrote

The named range called SheetName (I think that is what I called it) is the
reference to row 1. To confirm this go to menu Insert -- Name -- Define

Hi Crissy,

I played with you demo and tried to change some parameters to get familiar
with this. It worked fine in your test app, but when I apply it in our
'real-world' workbook I get #value errors.

Instead of naming a complete column as you did, I name a cell range, and
instead of naming a complete row as you did, I name a set of cells in the
same row. Would that be a difference?

No it wont - using complete rows and columns makes formulas easier to
check and, according to GOOD QUALITY research, 86% of spreadsheets
have errors in them.

This workbook is small, would it be possible that you could have a look at
it to point out what's the problem...?

Yeah - send it to me. Just reply to me and it should get there but this is the
last post I will EVER make to a news group with a valid e-mail addy as this
is a new addy and I have 100 spam, 50 of them viruses, this morning to this
addy.

Send me your attempt and enough info for me to get you the results you
want.

Chrissy.