Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Create a range that will update automatically to be used in formula
Hi
I use a button on my toolbar to run the macro below which updates an existing range when new data is added: The range Arg300227data is in a sheet named 'Data Entry' currently goes from cell A1 to M4500 Sub SetArg300227DataRanges() Application.Goto Reference:="Start" ActiveWorkbook.Names("Arg300227Data").Delete Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="Arg300227Data", RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion End Sub The person who created the sheet has since left the company but in another sheet 'Cost Code Summary' used the formula below: =IF(SUMIF('Data Entry'!$D$1:$D$4500,'Cost Code Summary'!A6,'Data Entry'!$O$1:$O$4500)=0,"",SUMIF('Data Entry'!$D$1:$D$4500,'Cost Code Summary'!A6,'Data Entry'!$O$1:$O$4500)) The problem now is the data that has gone past row 4500 is being ignored. I know I could just keep replacing the 4500 with a higher number but would like to automate this by clicking my macro button. I would like to know how to modify the formula above so that I can get rid of the hard coded cell references and replace them with my range name "Arg300227Data" but only reference column D and O. The idea being that when some new data is added the data entry person can just click the macro button not have to worry about updating formulas. Not every entry in columns D & O have data in them, otherwise I could probably solve this with my basic VBA skills Hope it is something simple and many thanks if someone can help Ready |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help - Create a range that will update automatically to be used in formula
Add more names
Sub SetArg300227DataRanges() Application.Goto Reference:="Start" ActiveWorkbook.Names("Arg300227Data").Delete Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="Arg300227Data", _ RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion ActiveWorkbook.Names.Add Name:="Arg300227DataD", _ RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion.Columns(4) ActiveWorkbook.Names.Add Name:="Arg300227DataO", _ RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion.Columns(15) End Sub and then use =IF(SUMIF(Arg300227DataD,'Cost Code Summary'!A6,Arg300227DataO)=0,"", SUMIF(Arg300227DataD,'Cost Code Summary'!A6,Arg300227DataO) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ready" wrote in message oups.com... Hi I use a button on my toolbar to run the macro below which updates an existing range when new data is added: The range Arg300227data is in a sheet named 'Data Entry' currently goes from cell A1 to M4500 Sub SetArg300227DataRanges() Application.Goto Reference:="Start" ActiveWorkbook.Names("Arg300227Data").Delete Selection.CurrentRegion.Select ActiveWorkbook.Names.Add Name:="Arg300227Data", RefersToR1C1:=Sheets("Data Entry").Range("Start").CurrentRegion End Sub The person who created the sheet has since left the company but in another sheet 'Cost Code Summary' used the formula below: =IF(SUMIF('Data Entry'!$D$1:$D$4500,'Cost Code Summary'!A6,'Data Entry'!$O$1:$O$4500)=0,"",SUMIF('Data Entry'!$D$1:$D$4500,'Cost Code Summary'!A6,'Data Entry'!$O$1:$O$4500)) The problem now is the data that has gone past row 4500 is being ignored. I know I could just keep replacing the 4500 with a higher number but would like to automate this by clicking my macro button. I would like to know how to modify the formula above so that I can get rid of the hard coded cell references and replace them with my range name "Arg300227Data" but only reference column D and O. The idea being that when some new data is added the data entry person can just click the macro button not have to worry about updating formulas. Not every entry in columns D & O have data in them, otherwise I could probably solve this with my basic VBA skills Hope it is something simple and many thanks if someone can help Ready |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using weekday formulas to automatically update a date range | Excel Discussion (Misc queries) | |||
automatically update pivot tables when dynamic named range is expa | Excel Discussion (Misc queries) | |||
Create charts that update automatically from data in rows | Charts and Charting in Excel | |||
Macro to Create a Chart and Update it Automatically | Charts and Charting in Excel | |||
How do I automatically update a chart range in Excel | Excel Discussion (Misc queries) |