**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.*