Referring to column without cell range

[meg9696]

I want to automate a spreadsheet data such that:
1. I cut and paste into first sheet some date
2. The second sheet has formulas that execute on the dataset in the first sheet.

The problem I have is that I want to refer to columns in the first sheet without providing a start and finish range, since this could be different depending on the amount of data in first sheet.


One of the formulas in the she second sheet is : =COUNTIFS(Y2:Y47,">=0",Y2:Y47,"<=50")
I would not know the range of column Y, is there a way to refer to that column in another way. Also how can the end of the dataset be known to the formula?

Appreciate any help and pointers. Thanks!

 

[Aladin Akyurek]

Maybe...

=COUNTIFS(Y:Y,">=0",Y:Y,"<=50")

 

[Peter_SSs]

Are there headings with the data that could be used to identify the appropriate column(s)?
Seems like there might be as your COUNTIFS starts at row 2.
So, suppose the sheet where you paste the data is called 'Data Sheet' and you are interested in such a count in the column with heading "Budget", then try a formula like this in the other sheet.

=COUNTIFS(INDEX('Data Sheet'!$A$2:$AB$100,0,MATCH("Budget",'Data Sheet'!$A$1:$AB$1,0)),">=0",INDEX('Data Sheet'!$A$2:$AB$100,0,MATCH("Budget",'Data Sheet'!$A$1:$AB$1,0)),"<=50")

Just make sure where I have used row 100 that you make it bigger than any data you are likely to have, without going overboard.
Same with going out to column AB. Extend or reduce that depending on the largest number of columns you might get.


Edit: Hmm, on re-reading I may have mis-interpreted?

 

[B___P]

define a name, may be DataY.
Its reference should be
=OFFSET($Y$2,0,0,COUNTA($Y:$Y))

Now you have a self adjusting range...

 

[Aladin Akyurek]

define a name, may be DataY.
Its reference should be
=OFFSET($Y$2,0,0,COUNTA($Y:$Y))

Now you have a self adjusting range...

Does that work correctly with in-between empty/untouched cells?

 

[B___P]

Hi alan,
you are right, it works correctly if there are no empty cells but a well formed database should not have holes expecially in a column used to do calculations.

 

[Aladin Akyurek]

Hi alan,
you are right, it works correctly if there are no empty cells but a well formed database should not have holes expecially in a column used to do calculations.

So we need test for holes? All that brings in additional costs...

If you want a dynamic range and since Y is numeric, try:

=Data!$Y$2:INDEX(Data!$Y:$Y,MATCH(9.99999999999999E+307,Data!$Y:$Y,1))

Adjust the sheet name to suit.

By the way, a whole column reference would suffice in COUNTIFS.

A side note. It's unclear what the OP wants. Perhaps column Y reference is changing, not the rows of column Y.

 

[meg9696]

Thanks everyone for the replies.

Yes I do have a heading row on sheet 1 which does not change in the names used for the first row.
Sheet 2 is supposed to execute this formula on sheet 1 information.

I am having success by writing my formula on Sheet 2 like this:
=COUNTIFS(TPM_Q3!Y:Y,">=0",TPM_Q3!Y:Y,"<=50")

where the Sheet 1 name is "TPM_Q3"

Thanks everyone you rock!

 

[meg9696]

Now for the second part of my report automation.

Once I get the data, I want to automate a pie chart plotting. Is it possible to define a pie chart appearing without me manually inserting it and highlighting the dataset to use for the pie chart?

 

[meg9696]

Please see below the pie chart that I want to automate the generation of: