Is there a way in excel to get data from the adjacent columns using a command or formula?

[arpit2751]

Hello Everyone,

Every month I prepare a board package that has data from different source files. Different source files has monthly data starting from different columns (for e.g - one file has monthly revenue information starting from column B to M ( Jan - Dec); the expense file has the monthly data starting from column C to N and so on). Currently I use the replace (crtl + F) functions to replace the last month's data to current month data. But since not all source file starts from the same column, the control - replace funtion is not that effective.

Is there a better way to automate this in the file. Basically, any formula or command that will move the data irespective of which column it is in to one column to the right?


Thanks so much for your help.

ADT

 

[RG19]

What version of Excel are using? Do you create a new file for each month or do you work from a master file?

 

[arpit2751]

What version of Excel are using? Do you create a new file for each month or do you work from a master file?

i am using excel 2010.i create new file for each month. hope this helps.
thanks

 

[xladept]

Hi arpit2751,

If you select the cell you want to move 1 column right - you can use this simple routine:

Sub MoveOver()
ActiveCell.Offset(0,1)=ActiveCell
ActiveCell=""
End Sub

You could then put this on a key - Record New Macro - Z_MAcro - Cntrl+Shift+z etc.
But you'd have to have a macro enabled spreadsheet (extension.xlsm)

 

[tusharm]

You need to explain some more about what you are doing. CTRL+f has nothing to do with moving data one column to the right. And, neither of them have any direct value if you are starting with a new file each month.

So, maybe you can explain the steps you take to produce the report.

Hello Everyone,

Every month I prepare a board package that has data from different source files. Different source files has monthly data starting from different columns (for e.g - one file has monthly revenue information starting from column B to M ( Jan - Dec); the expense file has the monthly data starting from column C to N and so on). Currently I use the replace (crtl + F) functions to replace the last month's data to current month data. But since not all source file starts from the same column, the control - replace funtion is not that effective.

Is there a better way to automate this in the file. Basically, any formula or command that will move the data irespective of which column it is in to one column to the right?


Thanks so much for your help.

ADT

 

[arpit2751]

You need to explain some more about what you are doing. CTRL+f has nothing to do with moving data one column to the right. And, neither of them have any direct value if you are starting with a new file each month.

So, maybe you can explain the steps you take to produce the report.

Hi Tushar,

I prepare a Board file that I prepare every month that essentially shows the financials in three columns ( lets say Current YEAR info is in Column B, Prior Year is in Column C & Current Budget is in column D) . The information in Column B is linked to a current year master file that has monthly actuals information starting from column B to M ( Jan - Dec);the information in column C is linked to prior year master file that has last years actuals information starting from column C to N , and the information in Column D in linked to the budget master file that that has monthly budget information starting from column D to O.

Currently , in order to update the board file we use the "find and replace" option to update the links. So let's say if the board file is currenlty linked to show January financials located in the B column of the current year master file, we update the link by using "Find & Replace " funtion and replace $B with $C to get the Feb information on the Board file. This option would work if the information in all the master file started from Column B.

I wanted to see if there's way ;maybe a macro; to update the links on the master file with information from one column to the right of the current linked column? Basically If am preparing a board file for February now, the board file will update the link/data from the currrent year master file from column C (previously linked to data in Column B), from the prior year file from column D (previously linked to data in Column C ) and the budget file from Column E ( previously linked to data in Column D).

Hope this makes sense, Tushar. Thanks again for your help.

Arpit

 

[arpit2751]

Hi arpit2751,

If you select the cell you want to move 1 column right - you can use this simple routine:

Sub MoveOver()
ActiveCell.Offset(0,1)=ActiveCell
ActiveCell=""
End Sub

You could then put this on a key - Record New Macro - Z_MAcro - Cntrl+Shift+z etc.
But you'd have to have a macro enabled spreadsheet (extension.xlsm)

Do this work if the cells are linked to a different master file?

 

[tusharm]

This is pretty straightforward to do and it does not need the overhead of code!

Suppose you have data in a book1.xlsx sheet1 range a1:D1.

Suppose you have a cell, say A2, in your destination worksheet that indicates the month of interest, 1, 2, etc.

Then, use the formula =INDEX('C:\Temp\[Book1.xlsx]Sheet1'!$A$1:$D$1,A2)

So, if you have another book that starts from B rather than A, just use the formula

=INDEX('C:\Temp\[Book2.xlsx]Sheet1'!$B$1:$E$1,A2)

Each month, increase by 1 the value of A2 in the destination workbook and the results will automagically show the values in the 'next' column in the source workbooks.

Hi Tushar,

I prepare a Board file that I prepare every month that essentially shows the financials in three columns ( lets say Current YEAR info is in Column B, Prior Year is in Column C & Current Budget is in column D) . The information in Column B is linked to a current year master file that has monthly actuals information starting from column B to M ( Jan - Dec);the information in column C is linked to prior year master file that has last years actuals information starting from column C to N , and the information in Column D in linked to the budget master file that that has monthly budget information starting from column D to O.

Currently , in order to update the board file we use the "find and replace" option to update the links. So let's say if the board file is currenlty linked to show January financials located in the B column of the current year master file, we update the link by using "Find & Replace " funtion and replace $B with $C to get the Feb information on the Board file. This option would work if the information in all the master file started from Column B.

I wanted to see if there's way ;maybe a macro; to update the links on the master file with information from one column to the right of the current linked column? Basically If am preparing a board file for February now, the board file will update the link/data from the currrent year master file from column C (previously linked to data in Column B), from the prior year file from column D (previously linked to data in Column C ) and the budget file from Column E ( previously linked to data in Column D).

Hope this makes sense, Tushar. Thanks again for your help.

Arpit

 

[arpit2751]

This is pretty straightforward to do and it does not need the overhead of code!

Suppose you have data in a book1.xlsx sheet1 range a1:D1.

Suppose you have a cell, say A2, in your destination worksheet that indicates the month of interest, 1, 2, etc.

Then, use the formula =INDEX('C:\Temp\[Book1.xlsx]Sheet1'!$A$1:$D$1,A2)

So, if you have another book that starts from B rather than A, just use the formula

=INDEX('C:\Temp\[Book2.xlsx]Sheet1'!$B$1:$E$1,A2)

Each month, increase by 1 the value of A2 in the destination workbook and the results will automagically show the values in the 'next' column in the source workbooks.

Thanks Tushar!!! Looks like this should help us make the process more efficient and save us time.

 

[tusharm]

Thanks Tushar!!! Looks like this should help us make the process more efficient and save us time.