Hi,
  I'm creating an excel spreadsheet from Access 2002 using VBA to sendout multiple queries to a single spreadsheet (each under a different tab). I then have a master tab that calls the values of each tab to itself. The problem I'm having tho is that certain columns are not always the same, sometimes it's the next one over and when I call for that value, it returns the wrong value becuase it thinks is supposed to bring in that columns value (which would be true if the header would be the right one).

  I have tried if statements and i've come to some kind of limitation of some sort. Seems i can only have 5 if statements nested into a single if statement (total of 6 in 1). Problem with that is that I have 7 columns i need to check values for.

  Is there any other formula or vb code for those 'callings' I'm trying to perform to certain cells ?

Thanks in advance gang!

~Danny

__________________
~Danny

It seem from this page, that 7 is the limit. But maybe you can use the solution.

http://www.cpearson.com/excel/nested.htm

Thank you Birger, I definitly see where/how to go with that. However, I do have one more question on that note. How do I make it reference to a particular cell in my 'master tab'?

For example:
Master tab, cells b2:h2 equal A, D, F, G, H, J, M (respectivly)
other tabs, cells b2:h2 can equal A, F, H, M (not always containing the 7 columns from the master tab, hence this being a variable)

from my master tab i want to check 'other tabs' cells b2:h2 to see if they match the current location in 'master tab'
Example:
if mastertab b2="A", if 'other tab' b2=A, =sum('othertab' b3), 0)
...and so on and so on until i reach h2.

You follow me?

I can send a sample of the spreadsheet if anyone would like to see it.

Thanks again,


~Danny

To be honest, I'm not sure I understand your problem.

I expect Master equivalates an Excel Sheet!

If you would like to check whether a value in one cell corresponds to a value in another cell may use this approach:


Sub x()
    If Sheets("Master").Range("A1").Value = Sheets("Slave").Range("A1").Value Then
    MsgBox "Found"
    Else: MsgBox "Not found"
    End If
End Sub

But if you don't know which colum to compare with, you'll first have to identify the column.

You need to look for a value you know exist or maybe it's enough for you to determine whether a value exists at all.

You may do this by looping rows or columns.

I don't know if this is your problem. I have to leave now, but if you havn't got an answer I'll be glad to try helping tomorrow. I'll look into this site then.

If you're fairly new to Excel, which I'm not sure you are, there might be something interesting here:

http://www.mindspring.com/~tflynn/excelvba.html

Birger