Link COUNTIFS formula from old workbook to new

[Ofsthun01]

Morning all. Relative Excel novice, so I just don't know where to start with figuring this out.

I have an Excel file that's getting too large and need to make a new one for the year it seems. But I have a COUNTIFS formula in column Q that counts the number a times a street address and city appear together (=COUNTIFS($F:$F,@$F:$F,$G:$G,@$G:$G)) so I know how many times an address shows up my report.

How can I reference this formula from my old workbook, so I keep the running total of every time an address has shown up, while simultaneously counting each time it shows up going forward on the new workbook?

Thanks!

 

[PeteWright]

I'm not quite sure, but will you have the "old" workbook still accessible on the same computer?
Does the below formula work?

= [NAME_OF_YOUR_OLD_WORKBOOK.xlsx]Sheet1!$A$1

replace NAME_OF_YOUR_OLD_WORKBOOK with the corresponding name of your old workbook
Then the above formula would reference cell A1 on the worksheet named "Sheet1".

 

[Ofsthun01]

I'm not quite sure, but will you have the "old" workbook still accessible on the same computer?
Does the below formula work?

= [NAME_OF_YOUR_OLD_WORKBOOK.xlsx]Sheet1!$A$1

replace NAME_OF_YOUR_OLD_WORKBOOK with the corresponding name of your old workbook
Then the above formula would reference cell A1 on the worksheet named "Sheet1".

Sorry, no that's just populating the counts from the previous workbook.

 

[PeteWright]

OK.

I've got another idea, a workaround:

You could copy just the last ROW of your old Worksheet to a NEW Worksheet within the same Workbook and then move that Worksheet out of the Workbook.

Does that work?

 

[Ofsthun01]

OK.

I've got another idea, a workaround:

You could copy just the last ROW of your old Worksheet to a NEW Worksheet within the same Workbook and then move that Worksheet out of the Workbook.

Does that work?

No, doing that no longer retains all the counts of the previous worksheet. The count for that copied row in the new worksheet in column Q is 1, whereas on the original worksheet, that address has appeared 12 times. I don't think just copying the formulas to a new sheet/workbook is going to work. I need something to link all the data in the old workbook and keep counting new data in the new worksbook.

 

[PeteWright]

OK.
Unfortunately I don't know any way to "link" data from another workbook.

You could import data from the Data ribbon in Excel, but I think this would just add the same data as in the original workbook and therefore make the file big as well.

Another option that just popped up in my mind is the equal sign/function.

For example, if you have a value in cell A1 of Worksheet1 in a Workbook you would add the following formula in your new worksheet:

A1 = [Workbook.xlsx]Worksheet1!$A$1

Well, actually that's stupid, but it's all I got.

Maybe someone else has an idea

 

[Asbestos_Jen]

Once you have the counts from the previous workbook (as appeared in post#3) couldn't you just continue the countifs from that point? Such as:

=[NAME_OF_YOUR_OLD_WORKBOOK.xlsx][TOTALS]+COUNTIFS($F:$F,@$F:$F,$G:$G,@$G:$G)

 

[Ofsthun01]

Once you have the counts from the previous workbook (as appeared in post#3) couldn't you just continue the countifs from that point? Such as:

=[NAME_OF_YOUR_OLD_WORKBOOK.xlsx][TOTALS]+COUNTIFS($F:$F,@$F:$F,$G:$G,@$G:$G)

I think you might have nailed it! Still doing some testing, but so far everything is working. In place of [Totals] I have the range of my data in column Q. Thank you!

 

[Asbestos_Jen]

Glad to help, and thanks for the feedback.

 

[Ofsthun01]

Glad to help, and thanks for the feedback.

Actually this is not working. Just coincidence that several of the values i checked happened to line up, but it is not bringing to true values and the running total forward. I really thought you had it.