Dragging Formula across referencing different worksheets?!

Hey guys,

Please can you take a look at the attached, I have a sumifs formula that extracts data based on date criteria. The source data is from multiple sheets labelled '1' '2' '3' etc, there are currently 181 colleagues and what i'm trying to avoid is having to manually change each formula to get the correct sheet ref.

I can find/change but that still involves a lot of hard work and I was wondering if there was any amendments I could make to my existing formula so when I drag across the remaining cells it would change the sheet number

Example.xlsx

Thanks in advance

Your sample WB has only 1 sheet, and contains links to a WB on your HDD or network.

I suggest you do some work on that (or make up a dummy WB) that resembles your situation - with some dummy data and dummy worksheets, and then upload that

Either way, you will probably need to use INDIRECT() to use the headings you have there

However, I would seriously question the need for a sheet per individual? The generally accepted method for data input is to have all data entered into 1 2-D table on one sheet, then run all your summaries from that - on seperate sheet/s if needed. You will find it much easier to manage, navigate, analyse, you name it, that way

Hi

You could use INDIRECT but the drawback to that is the other workbook needs to be open in memory for updates to happen.

However do you really need data spread across many sheets/workbooks. You are really giving yourself a lot of hassle and difficulties when you want to analyse/summarise it.

Ironically this has been a day of many similar questions where the user has data spread in many tables/sheets/workbooks. My response is usually the same, get the data into a normalised flat table of record rows and field headers and all will be simplified.

....Update

There's an Add In called MoreFunc which I understand does allow Indirect to work with a closed workbook. See here
http://en.kioskea.net/download/downl...d-in-for-excel

Richard, I think the OP is talking about sheets, not workbooks, although the description is somewhat confusing. Looks like we both made the same recommendation regarding 1 data sheet though

Thanks all for the replies

Yes I agree having the data in 1 sheet would be much easier to manage than spread across multiple sheets. There is good reason for the solution I have and potentially the indirect function could resolve my issue, if only I knew how to apply it.

Can anyone explain to me how this function works?

The attachment shows the formula i'm trying to fill across I did columns B - G to as an example of what I require, if I drag over from column G - H then I get the same sheet reference in column H

Hope this clears up my query

Regards

Just for the record in my actual Workbook all the sheets are contained within a single workbook, apologies for the confusion.

There is good reason for the solution I have and potentially the indirect function could resolve my issue, if only I knew how to apply it.


1) I am always interested what het good reason for the solution is. So please share the reason with us.

2) You get also better help if you add an excel example without confidential information.

Please also add the desired result.

@Ford. Good point completely missed the point about all in one workbook.

@360Petty

In general the indirect works like this. With say a sheet name in A1

Please Login or Register  to view this content.

basically, you would use this to reference A1 on sheet 1, based on your sample WB...
=INDIRECT("'"&B$1&"'!A"&ROW(A1))

This can then be copied down and across