| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells equal to another cell a certain # over
Good evening~
I am trying to get a row of cells to equal another row, but an allotted number over. Say for instance that I had a values in row 1 and I want row 2 to be equal to row 1 except 7 columns to the right. I.e. the value in A1 would appear in H2. The problem I have is how do I do this is the number of columns is variable and depends on a number in A3. The number in A3 ranges from 1-15. How do I write a formula to account for a variable number of columns such as this? I can do this if the number of columns is static, but I'm at a loss on how if it is variable. Can somebody please help? Thanks, Jaclyn |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cells equal to another cell a certain # over
In A2 put =IF(COLUMN()<=$A$3,"",IF(OFFSET(A1,0,-$A3)="","",OFFSET(A1,0,-$A3))) and formula-drag that to the right -- Fin Analyst Wrote: Good evening~ I am trying to get a row of cells to equal another row, but an allotted number over. Say for instance that I had a values in row 1 and I want row 2 to be equal to row 1 except 7 columns to the right. I.e. the value in A1 would appear in H2. The problem I have is how do I do this is the number of columns is variable and depends on a number in A3. The number in A3 ranges from 1-15. How do I write a formula to account for a variable number of columns such as this? I can do this if the number of columns is static, but I'm at a loss on how if it is variable. Can somebody please help? Thanks, Jaclyn -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531737 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cells equal to another cell a certain # over
The formula for, say, cell E6 is =OFFSET(E5,0,A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531737 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cells equal to another cell a certain # over
I tried both of these formulas suggested and the result is labeled "volatile"
and not showing any numbers in the cells. Any other suggestions? "John James" wrote: The formula for, say, cell E6 is =OFFSET(E5,0,A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531737 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cells equal to another cell a certain # over
I'm sorry, I forgot the file is on manual calculate. Once I hit F9 the
formula Bryan gave me works great. I do have one probelem though. The information I want actually starts in H1. There is information in the columns in front including a row description and a total. The cells A1 through E1 are blank. I don't want this information in F1 and G1 showing up. Is there any way to disinclude them from the offset formula result? Thanks for the help thus far! Jaclyn "Fin Analyst" wrote: I tried both of these formulas suggested and the result is labeled "volatile" and not showing any numbers in the cells. Any other suggestions? "John James" wrote: The formula for, say, cell E6 is =OFFSET(E5,0,A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531737 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cells equal to another cell a certain # over
Nevermind, I figured it out. Bryan, thank you so much for the formula. All
I had to do was add 7 to the column number, so it doesn't pick up the row description or total amount. So simplistic, but I didn't have time to analyze it earlier. Here is my final formula in case anybody encounters a similar problem: IF(COLUMN()<=($C$111+7),"",IF(OFFSET(I8,0,-$C111)="","",OFFSET(I8,0,-$C111))) C111 is the number of columns I need to offset by row 8 is the information I am pulling from Thanks again for all the help!! Jaclyn "Fin Analyst" wrote: I'm sorry, I forgot the file is on manual calculate. Once I hit F9 the formula Bryan gave me works great. I do have one probelem though. The information I want actually starts in H1. There is information in the columns in front including a row description and a total. The cells A1 through E1 are blank. I don't want this information in F1 and G1 showing up. Is there any way to disinclude them from the offset formula result? Thanks for the help thus far! Jaclyn "Fin Analyst" wrote: I tried both of these formulas suggested and the result is labeled "volatile" and not showing any numbers in the cells. Any other suggestions? "John James" wrote: The formula for, say, cell E6 is =OFFSET(E5,0,A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531737 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cells equal to another cell a certain # over
Good to see, and thanks for the response -- Fin Analyst Wrote: Nevermind, I figured it out. Bryan, thank you so much for the formula. All I had to do was add 7 to the column number, so it doesn't pick up the row description or total amount. So simplistic, but I didn't have time to analyze it earlier. Here is my final formula in case anybody encounters a similar problem: IF(COLUMN()<=($C$111+7),"",IF(OFFSET(I8,0,-$C111)="","",OFFSET(I8,0,-$C111))) C111 is the number of columns I need to offset by row 8 is the information I am pulling from Thanks again for all the help!! Jaclyn "Fin Analyst" wrote: I'm sorry, I forgot the file is on manual calculate. Once I hit F9 the formula Bryan gave me works great. I do have one probelem though. The information I want actually starts in H1. There is information in the columns in front including a row description and a total. The cells A1 through E1 are blank. I don't want this information in F1 and G1 showing up. Is there any way to disinclude them from the offset formula result? Thanks for the help thus far! Jaclyn "Fin Analyst" wrote: I tried both of these formulas suggested and the result is labeled "volatile" and not showing any numbers in the cells. Any other suggestions? "John James" wrote: The formula for, say, cell E6 is =OFFSET(E5,0,A3) -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531737 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531737 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
| Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
| Value of one cell a certain number of cells away from another cell | Excel Discussion (Misc queries) | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| How do I make a cell equal to another cells value and not it's fo. | Excel Worksheet Functions | |||
Linear Mode
