Point of departure...

  • Thread starter Thread starter Tor Tveitane
  • Start date Start date
T

Tor Tveitane

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 said:
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.
Click to expand...

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

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.
Click to expand...

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 ;-)
Click to expand...

That is cos they contain named ranges which make them more readable.
 
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
Click to expand...

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


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?
Click to expand...

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...?
Click to expand...

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.
 
Back
Top