Countif after columns have been deleted

[Mr2017]

Hi

Is it possible to have a formula in a sheet that looks at another sheet where columns have been re-arranged and deleted by VBA?

To add some context, let's assume the data below is in column B of Sheet 1

A

B

C

A

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Then Sheet 2 has the following data in columns A and B

A	2
B	1
C	1
A	2

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

The data in column B of Sheet 2 is derived from this formula (in cell B1): =COUNTIF(Sheet1!B:B,Sheet2!A1)

The problem I have is that data in Sheet 1 is obtained from another Sheet using VBA and the code deletes and re-arranges columns everytime it puts data into Sheet 1 so that the columns are in a certain order.

This then throws out the countif formula in Sheet 2 which then has a REF error.

How can I make the formula in Sheet 2 still check the right column in Sheet 1 without getting a REF error after the code is run?

NB Out of approx. 20 columns in Sheet 1, two columns in the middle are cut then pasted at the end, which is what I believe may be causing the REF error.

Thanks in advance.

 

[Eric W]

If you know that the data you're looking for will always end up in column B, you can do something like:

=COUNTIF(INDIRECT("Sheet1!B:B"),Sheet2!A1)

If it may end up somewhere else, but you can identify the right column by a specific heading, there are other options.

 

[Mr2017]

Hi Eric

Apologies for the delay in responding.

But thank you for posting your solution!

 

[Eric W]

You're welcome!

 

[Mr2017]

Hi Eric

Do you know how to amend the formula if Sheet 1 is given a name eg "orig data"?

I tried this, but it didn't work...?

=COUNTIF(INDIRECT("orig data!B:B"),Sheet2!A1)

 

[Eric W]

If the sheet name has spaces in it, you need to put single quotes around the name, like this:

=COUNTIF(INDIRECT("'orig data'!B:B"),Sheet2!A1)

 

[Mr2017]

Great!! Thank you!!