Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
I have an estimating program file with 30+ worksheets in which I need to be
able to specify a specific range name from four available, then paste the data from a 5 column by 6 row range relating to that named range to a different worksheet to calculate overheads, field supervision, AFDC and profit using the specified range name (the four range names have different values). I think an 'IF' statement would work but I do not know how to tell it to choose the range and then paste it to the specific area on the 1st worksheet and be able to overwrite that data if another range name is used. I originally created this program in Lotus 1-2-3 using a macro. -- Lori |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
Perhaps something like this ..
In the target sheet, Assuming B2 will house the input of the range name, Put in say, the formula bar for B4: =IF($B$2="","",OFFSET(INDIRECT($B$2),ROW(A1)-1,COLUMN(A1)-1)) Array-enter the formula in B4 by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER Copy B4 across to F4, fill down to F9 to populate the range size (5C x 6R) B4:F9 will return the required results* depending on the input in B2 *the contents of the named range For a neater look, suppress display of extraneous zeros in the sheet via clicking Tools Options View tab Uncheck "Zero values" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lori" wrote: I have an estimating program file with 30+ worksheets in which I need to be able to specify a specific range name from four available, then paste the data from a 5 column by 6 row range relating to that named range to a different worksheet to calculate overheads, field supervision, AFDC and profit using the specified range name (the four range names have different values). I think an 'IF' statement would work but I do not know how to tell it to choose the range and then paste it to the specific area on the 1st worksheet and be able to overwrite that data if another range name is used. I originally created this program in Lotus 1-2-3 using a macro. -- Lori |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
Tried your answer and got a partial response. Here is the data that should
appear: 55.0% 55.0% 55.0% 30.0% N/A 30.0% 8.0% N/A N/A N/A N/A The data that prints out is 55% in all cells. What could I be doing wrong? -- Lori "Max" wrote: Perhaps something like this .. In the target sheet, Assuming B2 will house the input of the range name, Put in say, the formula bar for B4: =IF($B$2="","",OFFSET(INDIRECT($B$2),ROW(A1)-1,COLUMN(A1)-1)) Array-enter the formula in B4 by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER Copy B4 across to F4, fill down to F9 to populate the range size (5C x 6R) B4:F9 will return the required results* depending on the input in B2 *the contents of the named range For a neater look, suppress display of extraneous zeros in the sheet via clicking Tools Options View tab Uncheck "Zero values" OK -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lori" wrote: I have an estimating program file with 30+ worksheets in which I need to be able to specify a specific range name from four available, then paste the data from a 5 column by 6 row range relating to that named range to a different worksheet to calculate overheads, field supervision, AFDC and profit using the specified range name (the four range names have different values). I think an 'IF' statement would work but I do not know how to tell it to choose the range and then paste it to the specific area on the 1st worksheet and be able to overwrite that data if another range name is used. I originally created this program in Lotus 1-2-3 using a macro. -- Lori |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
The data that prints out is 55% in all cells...
Is the calc mode accidentally set to Manual? Check Tools Options Calculation tab Pl post the actual formula you are using over there -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lori" wrote in message ... Tried your answer and got a partial response. Here is the data that should appear: 55.0% 55.0% 55.0% 30.0% N/A 30.0% 8.0% N/A N/A N/A N/A The data that prints out is 55% in all cells. What could I be doing wrong? -- Lori |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
The calc mode is set to automatic.
formula: (cell C16 is where BPOH choice is entered) =IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1)) -- Lori "Max" wrote: The data that prints out is 55% in all cells... Is the calc mode accidentally set to Manual? Check Tools Options Calculation tab Pl post the actual formula you are using over there -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lori" wrote in message ... Tried your answer and got a partial response. Here is the data that should appear: 55.0% 55.0% 55.0% 30.0% N/A 30.0% 8.0% N/A N/A N/A N/A The data that prints out is 55% in all cells. What could I be doing wrong? -- Lori |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
=IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1))
The above wasn't consistent with what was suggested earlier =IF($C$16="BPOH",BPOH, ... should read as: =IF($C$16="","", ... Try this working sample file which illustrates the earlier suggestion: http://www.savefile.com/files/387065 Display specified name range.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Lori wrote: The calc mode is set to automatic. formula: (cell C16 is where BPOH choice is entered) =IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1)) -- Lori |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
Thank you, it finally worked this time by rewriting it to match yours and
replacing my source cell of C16. The first few times I tried it exactly as you had it written, all I got was blank cells, so I rewrote it trying to get it to work. The drop down box for the range name selection is really neat. Is it easy to create? -- Lori "Max" wrote: =IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1)) The above wasn't consistent with what was suggested earlier =IF($C$16="BPOH",BPOH, ... should read as: =IF($C$16="","", ... Try this working sample file which illustrates the earlier suggestion: http://www.savefile.com/files/387065 Display specified name range.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Lori wrote: The calc mode is set to automatic. formula: (cell C16 is where BPOH choice is entered) =IF($C$16="BPOH",BPOH,OFFSET(INDIRECT($C$16),ROW(A 1)-1,COLUMN(A1)-1)) -- Lori |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
Glad you got it up there, Lori !
.. The drop down box for the range name selection is really neat. Is it easy to create? Yes, it is, using data validation. In the sample's instance, I just select the cell for the droplist ie B2, then click Data Validation Under Allow: choose "List" Then input the 4 range names within "Source:", viz: MyRange1,MyRange2,MyRange3,MyRange4 Click OK, and we're done For more info, try Debra Dalgleish's marvellous coverage on the steps for data validation (DV) at her: http://www.contextures.com/xlDataVal01.html -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Lori wrote: Thank you, it finally worked this time by rewriting it to match yours and replacing my source cell of C16. The first few times I tried it exactly as you had it written, all I got was blank cells, so I rewrote it trying to get it to work. The drop down box for the range name selection is really neat. Is it easy to create? -- Lori |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
Thank you for the advice on the drop down list, it works great and I am using
it in my estimate program file a number of times. Thanks again for your help. -- Lori "Max" wrote: Glad you got it up there, Lori ! .. The drop down box for the range name selection is really neat. Is it easy to create? Yes, it is, using data validation. In the sample's instance, I just select the cell for the droplist ie B2, then click Data Validation Under Allow: choose "List" Then input the 4 range names within "Source:", viz: MyRange1,MyRange2,MyRange3,MyRange4 Click OK, and we're done For more info, try Debra Dalgleish's marvellous coverage on the steps for data validation (DV) at her: http://www.contextures.com/xlDataVal01.html -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Lori wrote: Thank you, it finally worked this time by rewriting it to match yours and replacing my source cell of C16. The first few times I tried it exactly as you had it written, all I got was blank cells, so I rewrote it trying to get it to work. The drop down box for the range name selection is really neat. Is it easy to create? -- Lori |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
copy a specified name range
Pleasure` Lori.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lori" wrote in message ... Thank you for the advice on the drop down list, it works great and I am using it in my estimate program file a number of times. Thanks again for your help. -- Lori |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user input decides cell copy range | Excel Discussion (Misc queries) | |||
copy named range to powerpoint | Excel Worksheet Functions | |||
CheckBox Code to copy & paste range | Excel Discussion (Misc queries) | |||
help with range copy | Excel Discussion (Misc queries) | |||
reminder notifications in a column | Excel Discussion (Misc queries) |