Macro question

H

Harold

Hi,

Let me appoligise in advance for the lame question but I have to ask,


I am redesigning a spreadsheet which comes down to a data page (dat
grabbed of various other worksheets) and a reports page.

One thing I would like to do is enter a number in a "control cell" an
that would be the week I report. for that to occur you now I fin
(replace) eg find $8 replace with $9 manually which is fine for me bu
I want to distrobute this to a few people at the end of the season wh
are even less excell literate than me. {which is saying something}

can this be done with a macro?
if so is the macro writer in excell worth trying?
is there a web page with a few examples of how to do this?


Thankyo
 
F

Frank Kabel

Hi
maybe you can explain with some more detail how your data is organized.
e.g.
- what values would you enter in this control cell
- how is the master data structured
- etc.
 
H

Harold

The data will be organised in two banks of 3 colums followed by a tota
eg. c,d,e, total f,g,h, total this data at the moment gets taken of
other worksheets (completely different files)

I get the data through the following formlua which works perfectly fo
the "week sheet" ='F:\Bowling 2004\Summery Sheets\[Yada.xls]John'!$B$9
Obviously different formulas for different people but in the sam
format.

this week sheet is a summery of the whole league (44 bowlers) and th
stat sheet draws min, max ave etc etc etc (thankyou to everyone o
this forum who helped me when I got stuck) and it works perfectly bu
as it refers to different directories is immovable (hindsight would pu
it on c: and all in one directory but everything is easy in hindsight


What I do now is find $9 replace with $10 which would give me the nex
weeks data but I want to give this to people who are less excel
litterate than me.

the data in the control box would be a single number between 1 and 5
(at worst) and this would put the week you type in in the results an
hence the report.

ie you enter 10 in the control box and it puts up week 10, you enter
it puts up week 5 ect

another way of writing what I am trying to achieve is
='F:\Bowling 2004\Summery Sheets\[Yada.xls]John'!$B$[control box +1]

(the +1 is because of headings, week 1 starts on row 2
 
H

Harold

Hi Frank,

I have tried that formlua and it comes up with the #name# error

I have tried it both as normal and as an array enter formula

what Have I done wron
 
Top