Question description:
This user has given permission to use the problem statement for this
blog.
How can I reference a cell in 1 sheet to another sheet and then drag the vertical source horizontally without the Column changing
Solved by S. H. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
17/09/2017 - 06:29
Hello
User
17/09/2017 - 06:29
Hello
Excelchat Expert
17/09/2017 - 06:29
What do you want to do?
Excelchat Expert
17/09/2017 - 06:29
Can you explain a bit?
User
17/09/2017 - 06:31
I have an excel budget which I unfortunately cannot upload. But, need help. So here is the issue. I have Sheet 2017 which Has data in H100-H110. I want to reference H100 on a different sheet then drag it horizontally
User
17/09/2017 - 06:31
When I reference the cell in the sheet i need it in like ='OperatingBudget'!H100
User
17/09/2017 - 06:32
That works but when I drag it horizontally it then goes to I100, J100, Etc
User
17/09/2017 - 06:32
I need it to be H100, H101, H102 etc
User
17/09/2017 - 06:32
The source info is Verticle I am trying to Drag horiztonally keeping the same column
User
17/09/2017 - 06:33
Does this make sense?
Excelchat Expert
17/09/2017 - 06:33
You men you are copying Horizantle data to vertical
Excelchat Expert
17/09/2017 - 06:33
can You type raw data in the sheet
Excelchat Expert
17/09/2017 - 06:34
okay
Excelchat Expert
17/09/2017 - 06:34
Now how you want that to Copy to Sheet2
User
17/09/2017 - 06:34
Yes, but I want to reference those cells should data change etc
Excelchat Expert
17/09/2017 - 06:35
Okay Can You write manually these data in sheet2
Excelchat Expert
17/09/2017 - 06:35
so I can get better idea how you want it
User
17/09/2017 - 06:35
I wrote it in sheet 2
User
17/09/2017 - 06:35
and tried to drag the formula
User
17/09/2017 - 06:35
Can you see?
User
17/09/2017 - 06:36
sheet2 C4 formula
Excelchat Expert
17/09/2017 - 06:37
Yes
Excelchat Expert
17/09/2017 - 06:37
You want that data to C4, D4 etc
User
17/09/2017 - 06:37
Yes exactly
User
17/09/2017 - 06:38
Otherwise I have to manually change the column letter in each cell
Excelchat Expert
17/09/2017 - 06:38
Okay
User
17/09/2017 - 06:40
Do you know how to help with it?
Excelchat Expert
17/09/2017 - 06:41
Yes
Excelchat Expert
17/09/2017 - 06:41
Give me a Sec
User
17/09/2017 - 06:41
Ok thank you
Excelchat Expert
17/09/2017 - 06:46
See
Excelchat Expert
17/09/2017 - 06:46
When you will change the values
Excelchat Expert
17/09/2017 - 06:47
in Sheet1
Excelchat Expert
17/09/2017 - 06:47
it will automatically change in Sheet2
User
17/09/2017 - 06:48
Ok I see how you did it. So there is no way to just reference a cell on workbook and then drag it basically?
Excelchat Expert
17/09/2017 - 06:48
No
Excelchat Expert
17/09/2017 - 06:48
you will write this formula
Excelchat Expert
17/09/2017 - 06:49
and reference your Column There
Excelchat Expert
17/09/2017 - 06:49
when you will reference the cells
User
17/09/2017 - 06:49
What does the -2 mean?
User
17/09/2017 - 06:49
I am trying it now on my workbook
Excelchat Expert
17/09/2017 - 06:50
If you want to copy the data from first cell of the Column you have to use -2
User
17/09/2017 - 06:51
=INDIRECT('Operating Budget Detail'!H173&COLUMN(h)-2)
User
17/09/2017 - 06:51
Gives me a value error
Excelchat Expert
17/09/2017 - 06:52
use H instead of H173
User
17/09/2017 - 06:52
How would it know which cell I want the data from then if I remove the H173?
Excelchat Expert
17/09/2017 - 06:53
if your data is in the Fifth row
Excelchat Expert
17/09/2017 - 06:53
then you have to use +2
Excelchat Expert
17/09/2017 - 06:53
in the formula
User
17/09/2017 - 06:55
Ok I think I get it
Excelchat Expert
17/09/2017 - 06:56
Okay Cool
User
17/09/2017 - 06:56
Thank you for your help. Greatly appreciated
Excelchat Expert
17/09/2017 - 06:56
You welcome
Excelchat Expert
17/09/2017 - 06:56
Sir
Excelchat Expert
17/09/2017 - 06:56
Dont forget to Rate my effort
Excelchat Expert
17/09/2017 - 06:58
You there?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
