It’s no great secret that where Microsoft packages are concerned, Word is my bag whereas Excel frustrates me (ironic because I work in the financial industry and I have to use the latter every day). Is there a way I can overtype 17* into a cell containing the number 10, hit enter/return, and have the value in the cell change to 27? I know that I can have a separate column which can do the addition for me, but can I eliminate that and simply do the sum in the one cell? I’m not sure if I’m explaining what I mean… another sad irony, because I’m a writer! Google has not been my friend on this matter. *numbers used are for example purposes only. Posted by Slipwater
Let's say the column of numbers you want to add to are in Column D.
1. Open your Excel spreadsheet 2. On the sheet with your data in Right Click the sheet name (probably Sheet1) and click View Code 3. In the window that opens paste the following: Option Explicit Dim addValue As Variant
Sub Worksheet_SelectionChange(ByVal Target As Range) If Selection.Count = 1 Then If Not Intersect(Target, Range("D:D")) Is Nothing Then addValue = InputBox("Enter a value to Add:") ActiveCell.Value = ActiveCell.Value + addValue End If End If End Sub
4. Close the Visual Basic window that you just pasted this code into (no need to save it) 5. Click on any value in Column D and it will ask you for a value 6. Enter a value and press enter
7. Magic happens
In the part above where it says Range("D:D") you can alter that to any column you choose.
if this is just a matter of retaining your formatting so you don't have formula column c = col a + col b, it could be done using a macro.
as an example, you could have your existing data in col a and enter the data to be added in col b. the macro would then add the two sets of numbers together, replace the values in column a with the new totals and remove the data from col b.
In Response to Re: Excel Help... : I actually didn't know that, but I tried it and it does work. Is there a way I can add a daily tab and Excel would be able to pull the points across to the running one, including new names etc? Posted by Slipwater
yes to this as well. this would also need a macro to find new player names and to create a new workbook by copying all daily sheets into a new workbook, removing all duplicated entries and then and using vlookup commands to populate historical data.
In Response to Re: Excel Help... : I actually didn't know that, but I tried it and it does work. Is there a way I can add a daily tab and Excel would be able to pull the points across to the running one, including new names etc? Posted by Slipwater
In Response to Re: Excel Help... : yes to this as well. this would also need a macro to find new player names and to create a new workbook by copying all daily sheets into a new workbook, removing all duplicated entries and then and using vlookup commands to populate historical data. Posted by mac24
There are ways of doing this kind of thing using the sort and filter commands, you can also use some of the advance formula stuff to cut out some of the manual work and full automation would probably need a macro but is doable.
I would consider myself an intermediate at Excel - If there is an advanced user/guru out there its probably a fairly simple job for them - I'm in the could probably figure it out but would take a while camp.
You can do pretty much anything with VBA code, I've found it really useful to learn some VBA commands just by googling for certain operations, then adapting the script for my needs.
You can do pretty much anything with VBA code, I've found it really useful to learn some VBA commands just by googling for certain operations, then adapting the script for my needs. Surprised you used to do this all manually! Posted by bbMike
Yeah, well... I have to do a lot of things manually these days.
Didnt get a chance to look at this in work today Brian.
I suggest you use the sheet I sent you or create one similar to it naming each column week 1 to 52 perhaps.
To record a macro select tools, macro, record new macro and name it (ie main, mini or total)......once you do that it records any action you make until you select stop recording.
Once you select record macro then copy the entire sheet 1 into sheet 2.
In sheet 2 add the total points for each player for main then sort their name and total in descending points order , then hide all the cells where the numbers appear (ie the workings) with the exception of the total column.
Then select the option to stop recording macro.
You can have 3 macros if you wish, one for main on sheet 2, one for mini on sheet 3 and one for total on sheet 4.
That way once you enter the points each week on sheet 1 you can run the 3 macros and you will instanty have 3 tables updated. Make sure to clear the contents in sheets 2,3 and 4 before you run the macros.
Didnt get a chance to look at this in work today Brian. I suggest you use the sheet I sent you or create one similar to it naming each column week 1 to 52 perhaps. To record a macro select tools, macro, record new macro and name it (ie main, mini or total)......once you do that it records any action you make until you select stop recording. Once you select record macro then copy the entire sheet 1 into sheet 2. In sheet 2 add the total points for each player for main then sort their name and total in descending points order , then hide all the cells where the numbers appear (ie the workings) with the exception of the total column. Then select the option to stop recording macro. You can have 3 macros if you wish, one for main on sheet 2, one for mini on sheet 3 and one for total on sheet 4. That way once you enter the points each week on sheet 1 you can run the 3 macros and you will instanty have 3 tables updated. Make sure to clear the contents in sheets 2,3 and 4 before you run the macros. Hope that works for you. Ger Posted by gerardirl
Hmmm... sounds complicated, but I'll give it a bash
This forum thread tortured me, ive been trying for a few days trying to get this lol
Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.
In case it helps here's how I made mine:
What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.
For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem
The only concern i have is about the size of the workbooks once it starts filling up with results.
Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that
This forum thread tortured me, ive been trying for a few days trying to get this lol Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one. In case it helps here's how I made mine: What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet. For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem The only concern i have is about the size of the workbooks once it starts filling up with results. Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that GL mate Posted by jdsallstar
Nice work sir.....
C1=+A1+B1 ENTER - Anything more in excel & my brain explodes
This forum thread tortured me, ive been trying for a few days trying to get this lol Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one. In case it helps here's how I made mine: What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet. For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem The only concern i have is about the size of the workbooks once it starts filling up with results. Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that GL mate Posted by jdsallstar
You don't need 365 sheets, just one sheet that holds the new day information then when you run the macro to copy it into the sheet that holds day by day use a user-entry box command so it asks you which day you're on. Use this value to offset the cell you're pasting the data to.
Comments
1. Open your Excel spreadsheet
2. On the sheet with your data in Right Click the sheet name (probably Sheet1) and click View Code
3. In the window that opens paste the following:
Option Explicit
Dim addValue As Variant
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("D:D")) Is Nothing Then
addValue = InputBox("Enter a value to Add:")
ActiveCell.Value = ActiveCell.Value + addValue
End If
End If
End Sub
4. Close the Visual Basic window that you just pasted this code into (no need to save it)
5. Click on any value in Column D and it will ask you for a value
6. Enter a value and press enter
7. Magic happens
In the part above where it says Range("D:D") you can alter that to any column you choose.
if this is just a matter of retaining your formatting so you don't have formula column c = col a + col b, it could be done using a macro.
as an example, you could have your existing data in col a and enter the data to be added in col b. the macro would then add the two sets of numbers together, replace the values in column a with the new totals and remove the data from col b.
Thanks for your help.
Sounded good in theory, but the practice of it...
This forum thread tortured me, ive been trying for a few days trying to get this lol
Think I've finally cracked it though but it'll take me a day or two to set it up and test it so if your still struggling with it i could try finishing this one.
In case it helps here's how I made mine:
What i basically did was create a workbook with 365 sheets on it to paste the results of each main/mini event. So one workbook for main and one for mini. A macro then lifts the player names from each daily sheet, dumps them into another sheet and then removes duplicates to create a unique player list. Using this unique list i then pull the points for each player into a data sheet using vlookups for each day. Using this data sheet i then used a countif formula which counted wins, FT and top 20s. I then used a macro that copied the results from this data sheet (name, points, Wins, FTs and top 20s) and pasted them into a league table sheet and then sorted them by points, then wins, then ft's then top 20s and finally by alphabet.
For the brag board it would be just a case of running a macro that combines the datasheet for the main and the datasheet for the mini and then run the same league table macro described above. Havent got this far yet but cant see it being any problem
The only concern i have is about the size of the workbooks once it starts filling up with results.
Anyway.... lol let me know if you want me to finish it off. No worries if you do or dont I had to figure it for my own sanity anyway lol yes i'm sad like that
GL mate
C1=+A1+B1 ENTER - Anything more in excel & my brain explodes