copy/paste or dragging cells using INDIRECT in formula

so I have a formula...

HTML Code: 

that I would like to copy/paste in different areas of my spreadsheet.

The only problem is the cell references stay the same when I do so. Any idea how to allow excel to automatically change these cell references?


btw: I am finding an average of a column and skipping any values of zero. I need INDIRECT because the amount of cells that need averaged is constantly changing and I need to dynamically change the range size (S2).

Thanks

I am not sure that indirect is required. If you are concerned about the range of data you need to consider then either a named dynamic range will do, or an Excel table column.
Here is information on dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
Here is information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

As for getting an average but ignoring zeros, you can either use helper columns or an array formula: http://www.utteraccess.com/wiki/inde...inIf_AverageIf

Originally Posted by gmr4evr1

Can you attach a sample workbook with your formula in it? I copied and pasted your formula into a blank workbook and dragged it down and the cell reference (the only one I see in the formula is S2) changed as it should. I also copied and pasted and they changed.

Book2.xlsx

there is a small portion of the worksheet

that snipit is of one NBA player. I need to do this for every NBA player (350+ players)

here is a screenshot of the actual sheet zoomed way out for reference
nba.PNG


basically I need to copy and paste these formulas for every player

Originally Posted by dflak

I am not sure that indirect is required. If you are concerned about the range of data you need to consider then either a named dynamic range will do, or an Excel table column.
Here is information on dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
Here is information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel

As for getting an average but ignoring zeros, you can either use helper columns or an array formula: http://www.utteraccess.com/wiki/inde...inIf_AverageIf

I tried using named dynamic ranges, but with 3+ ranges per player and 350+ players my named ranges would be out of control. Plus I dont know how easy that would be to copy/paste.

I am currently using array formulas to ignore zero values when calculating values. see attached sheet in post above

Please attach a sample workbook.

Originally Posted by dflak

Please attach a sample workbook.

I did, it's two posts above

Is it not working correctly?

Here is my attempt at it. I have one sheet. It has 12 ranges. Each of the ranges is local to the sheet it is on. If you copy the sheet, it will produce an identical set of ranges local to the new sheet.

I also employed some helper cells to help set up the ranges. They depend upon counting the number of entries in column P so don't add any additional titling to this column.

I left the name definitions on the page.

Originally Posted by gmr4evr1

What is this _xlfn.STDEV.P in your array formula? I remove it and it seems to work ok...I think.

The version I have just has the formulas as follows (for std deviation)

Please Login or Register  to view this content.

I dont know where the _xlfn portion is coming from???

Originally Posted by dflak

Here is my attempt at it. I have one sheet. It has 12 ranges. Each of the ranges is local to the sheet it is on. If you copy the sheet, it will produce an identical set of ranges local to the new sheet.

I also employed some helper cells to help set up the ranges. They depend upon counting the number of entries in column P so don't add any additional titling to this column.

I left the name definitions on the page.

I see what you did there. But where would I place all those helper cells when I am setting this up for every player in the NBA? Plus, I would need unique named ranges for each player as there are different game totals for each. One player might have played 38 games whereas another one might have only played 34.

Originally Posted by dflak

Right click on the tab. You get a menu. One of the options on the menu is move or copy. Select this option and another dialog box comes up. Click on the box Create a copy and select where you want the copy to appear. Click OK.

You now have a duplicate of that page including all formulas and named ranges. The named ranges are limited to the page itself. So if you have 350+ players you will have 350+ instances of each named range, each one of them pertaining to its own page.

The named ranges are defined using the offset command. These ranges "know" how many rows of data there are in the data set. In other words, they know how many games a player has played.

I thought of a entirely different approach to the issue. Instead of duplicating all this work 350 times. How about having just the data for a player on the page. Then on a dashboard sheet you have a dropdown by player (and if you want you can have two dropdowns: one for team which will get you a list of players on that team) and the other to select the specific player on the team. Somewhere on the spreadsheet, you'll need to identify teams and who is on each team.

On the dashboard sheet, you select the player, and the stats will appear automatically. You will not have to navigate off-page except to update data fro the player.

In fact, you could select a team, and the macro will present the players for that team across the columns with the stats in the rows and a team total at the end.

Give me about 10 sheets of data for two teams and I can demonstrate this. Make sure you tell me who is on what team. I don't follow basketball at all and would be hard pressed to even name all the teams, much less know their rosters.

This approach will cut down the number for formulas from the thousands into a couple dozen making the spreadsheet more responsive. Also it is easier to maintain. You don't have to set up formulas for every player. That will happen when his data is imported to the dashboard sheet.

Interesting idea! Would the source data page be setup the same? (When I say source data page I am referring to the screen shot above). The reason I ask is, my import data macro is setup to take a .csv file (that I download after every days games) and imports them into that source data sheet. The .csv file is in no peticular player order and requires the macro to search out the players name and import the data below. If the appearance of that page changes, I would need to alter that macro.

I should also add that this data that is being calculated (std deviations & averages) all gets added into a table and is then sorted. I then use that table to come up with players who might have big games for any given day

I will upload a sample later today when I get home from work.

Guru_Data.txt

here is an example of the CSV file that I use for uploading each days data

Obviously its in a TXT format. I import that data into a separate sheet within my workbook. My macro then takes that data, sorts, and adds it into the source data sheet.

Originally Posted by dflak

I just had a look at the source data.

I need your help. You will have to explain how you manipulate the data from the CSV format to the format in the workbook. You mentioned something about a macro, but I didn't see it in the workbook (which is XLSX anyway).

I omitted the macro to keep the file size down.

This is my process... (If there is an easier way to accomplish this task, I am all ears)

1.)Download the TXT file from the website. Data in the TXT is comma separated values
2.)Within excel, go to Get External Data -> From Text and point to the TXT file. Basically move the values from TXT file to blank sheet. The data is imported exactly in the same manner that it is setup in the TXT file.
3.)Click on a command button that initiates a macro. This macro uses two arrays. One is loaded with all of the newly imported data, and another with the list of player names on the source data sheet (Screen shot above, A1:EBV1). The macro then loops through and compares the player names from both arrays. When it finds a match it then dumps the associated data into a new line below the players name on the source data sheet.

That's basically it. That was the best idea I could come up with given my limited excel knowledge.

I gotcha... I may have uploaded the wrong TXT file. This one is the one that I use for the daily data...

jan8.txt

the mapping is as follows...

TXT FILE	SOURCE DATA SHEET
FD Points	FDP
FD Salary	Salary
H/A	H/A
Team Score	Score
Minutes	Mins

You can see how I derived the values for Made Val?, Upside?, and Dud? with the formulas in those cells.

Sorry for the confusion

check my last post, I attached a new TXT file.

the header line should be as follows
Date;GID;Pos;Name;Starter;FD Pts;FD Salary;Team;H/A;Oppt;Team Score;Oppt Score;Minutes;Stat line

I agree. I think it makes more sense to only show the games where the minutes played > 0. I want the stats to be calculated based on games that the player actually plays in.

Originally Posted by dflak

As I was crafting my reply, I was adding features. I forgot to save the file when I attached it. So use this file instead.

That is freaking fantastic!

Those calculated statistics... (Averages & Std Deviations). Would it be possible to have those calculated for every player so that I can use a lookup function to send them to another table of all NBA players, so that I can sort them to see the highest and lowest?