I have some data that I need to put into a spreadsheet every month. I am getting this from an external data source (Business Objects query). I am pasting this data into a spreadsheet which I then want to update a 'master' spreadsheet. My problem is that each month this dataset may have varying number of rows.ie

Month 1
Type A 123
Type D 245
Type E 39

Month 2
Type A 345
Type C 218
Type D 78
Type F 56

Each month, I paste over the previous month's data. Therefore, type D, for example, may not be in the same cell position the following month.
My master spreadsheet has rows for Type A, B, C, D, E, F (6 rows). I intend to use a formula to update each row in the master spreadsheet which seems straightforward but he problem is that because some Types aren't included every month, the rows will shift up or down so I can't use a specific cell reference. I've searched the forum for a similar problem and can't find anything. I'm not sure I am explaining this properly but for anyone who understands relational databases, what I need is the equivalent of an 'outer join' which will give me the rows I am missing.

Any pointers in the right direction would be much appreciated.

Can you change the join in your Business Objects report to an outer join so that every month will be consistant?

Unfortunately, not. The version of Business Objects I am using does not allow me to do outer joins between two reporting tables. It will only do equi-joins. I have even considered taking the data into Access, creating the outer join there and then bringing it into excel but it seems crazy to bring in yet another application!

What version of BO are you using

Howdy. If I understand correctly, you can use dynamic named ranges, and then use an INDEX/MATCH combination.

Data:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

Then if column A has the Type, set up this named range:

ColA

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Now, use these in your lookup formulas:

=INDEX(Data,MATCH(Type,ColA,FALSE),MATCH(ColName,S heet1!$A$1:$K$1,FALSE))

Then adjust as needed.

Dear shades

Thank you for taking the time to look at my issue. I am not a terribly experienced Excel user and, on first glance, your suggestion seemed way over my head. However, I've looked in detail at the functions you suggest and I have looked them up in the 'help' on excel and can see what they are doing. I've tried applying your suggestion to my data but am getting a little stuck.

I have a set of data, say A1:E20
I have a lookup table of TYPES (code and description) (25 of them), say G1:H25
I want to create a new dataset (say in J1:J25)that combines my data in column E with each of the TYPES

I understand COUNTA, OFFSET ok but can't seem to apply the INDEX or MATCH properly. Is it possible you could explain what each formula you have specified actually does so that I can then more easily apply it to mine?

Sorry if I'm being a bit stupid - sometimes I can't see the wood for the trees!

It might be easier if you could attach a sample. Include what you have described in the post above, and then 2-3 cells in the J1:J25 range that are filled with what you expect (no need for formulas, just what you want to see). Thanks.


Sorry if I'm being a bit stupid - sometimes I can't see the wood for the trees!

No need to worry about this. We all started at the same point, so I know how you feel. We can work together and both learn even more. :)