"Indirect" references

[Tim Edmonds]

Hi, could someone please tell me if this is possible ...

I have a complex spreadsheet that calculates statistics for my pool
team. Each season is a separate worksheet in the one single
spreadsheet. There are three databases stored in each worksheet.

A typical formula on my Overall Statistics worksheet would look like
this :

GETWINPERCENTAGE(Season01DB1, Season01DB2, Season01DB3, Season02DB1,
Season02DB2, Season02DB3, Season03DB1, Season03DB2, Season03DB3)

However, since I will be adding Season04DB1, Season04DB2 and
Season04DB3 next season, I will then have :

GETWINPERCENTAGE(Season01DB1, Season01DB2, Season01DB3, Season02DB1,
Season02DB2, Season02DB3, Season03DB1, Season03DB2, Season03DB3,
Season04DB1, Season04DB2, Season04DB3)

My GETWINPERCENTAGE function accepts a ParamArray to handle this
variable number of parameters.

Function GETWINPERCENTAGE(ParamArray dbase() As Variant) As Integer

Now, this function is used A LOT. When I add a new season, I have to
add the text Season04DB1, Season04DB2, Season04DB3 etc. in many many
places. What I would like is to NEVER have to change the function, and
for the data to be picked up in a range.

I can easily use the INDIRECT function to do this:

GETWINPERCENTAGE(INDIRECT(A1), INDIRECT(A2), INDIRECT(A3),
INDIRECT(A4), INDIRECT(A5), INDIRECT(A6), INDIRECT(A7), INDIRECT(A8),
INDIRECT(A9))

and then these cells A1-A9 contain the name of the database (for
example SEASON01DB1). This works fine, but it means I'd just be adding
INDIRECT(A10), INDIRECT(A11), INDIRECT(A12) the next season.

What I'd LIKE is to say something like :

GETWINPERCENTAGE({range of where references are stored})

e.g GETWINPERCENTAGE(INDIRECT(A1:A12))

Then I'd just add the names of the three new databases in one place,
and everything gets updated automatically. The above example does not
work. Can I use a more complex version of INDIRECT to this? If not, is
this even possible?

Thanks!
Tim Edmonds

 

[Frank Kabel]

Hi Tim
I'd try the following:
- create a range with all your spreadsheet names
- change your UDF to accept a range and loop within your UDF through
this range to get all sheetnahmes

With that you may use a formula like
=GETWINPERCENTAGE(OFFSET($A$1,0,0,COUNTA($A:$A))
this range will change dynamically if you add a new name to column A.
Now you 'only' habe to change your UDF to something like

Public function GETWINPERCENTAGE(rng as range)
Dim cell as range
Dim wks_name
dim wks as worksheet
....
....
for each cell in rng
if cell.value <>"" then
wks_name = cell.value
set wks = worksheets(wks_name)
'process this worksheet
end if

next
....
end function

 

[Tim Edmonds]

Hi Frank,

Thanks for your help. Using your solution, I've managed to get all the
data into the one (dynamic) range now, so I can begin to change my UDF
to handle it. It's going to take a bit of work, but I can see light at
the end of the tunnel!

Regards,
Tim Edmonds

 

[Tim Edmonds]

The problem is that the range just contains eight strings, and not eight databases

 

[Tim Edmonds]

The problem lies here :

Set wks = Worksheets(wks_name)

I have four worksheets (Season01, Season02, Season03, Season04 ). Each
worksheet contains three databases Season01DB1, Season01DB2,
Season01DB3 etc. so twelve databases(lists) in all.

The call to the function uses the database names, NOT the worksheet
names.

GETWINPERCENTAGE (Season01DB1, Season01DB2 ... )

Using the new OFFSET and COUNTA code, I can pass the names of the
databases dynamically to the function, but since they are databases
and not worksheets, it fails when it tries to link it to a worksheet.

Obviously what I need is something like :

Set dbs= Database(dbs_name)

Currently I pass the whole database to the VBA function. Is there a
way to just pass the name, and then for me to then get all the data
using a similar function to the one I described above? If so, I can
pass the twelve names, then link to the databases in VBA.

Thanks!
Tim

 

[Frank Kabel]

Hi Tim
so you have different workbooks. You may use something like
Public function GETWINPERCENTAGE(rng as range)
Dim cell as range
Dim wbk_name
dim wbk as workbook
....
....
for each cell in rng
if cell.value <>"" then
wbk_name = cell.value
set wbk = workbooks(wbk_name)
'process this worksheet
end if

next
....
end function

The workbook has to be opened to make this work