Hello @healthcarebizbrokers, Thanks for your feedback and appreciation. It means a lot to us. Glad to hear that the first method is on point for your needs. Keep learning Excel with ExcelDemy! Regards ExcelDemy
Thank you for nice explanation. There is case I have linked two sheets in share folder working fine. Once I download local drive offline it’s not working. Can you share some insight on how to connect in this case.
Hello @kishoresairam, You're welcome! The issue arises because links between Excel files work only when the file paths are consistent. The link paths break when files are moved from a shared folder to a local drive. To resolve this: 1. Ensure files use relative paths instead of absolute ones if they stay within the same folder structure. 2. After moving the files, update the links in Excel by navigating to the Data tab >> Edit Links >> Change Source. 3. Use cloud services (e.g., OneDrive) to maintain seamless links online and offline. Let me know if further clarification is needed! Regards ExcelDemy
Hello @blenkomulti-services8790, You are most welcome. Glad to hear that the first method worked perfectly for you. Your appreciation means a lot to us. Keep learning Excel with ExcelDemy! Regards ExcelDemy
Hello @mobeenalam3356, You are most welcome. Thanks for your appreciation. Glad to hear that our video made it easy for you. Keep learning Excel with ExcelDemy! Regards Shamima Sultana ExcelDemy
hello good sir. I am wondering is there a way to change the format (like colour or font) of a cell and then u can see that to the linked cell? or the paste link command is only to update the value ? Thank you !
Hello @314Tesla, You are most welcome. The 'paste link' command in Excel only updates values between linked cells and doesn’t transfer formatting such as font, color, or borders. If you want to reflect formatting changes in linked cells, you'll need to apply the same formatting manually or use conditional formatting to automate some styling based on the values. Unfortunately, Excel doesn't natively support automatic formatting updates via links. You can use this VBA code to copy the formatting from one cell to another whenever the source cell changes. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A1")) Is Nothing Then Dim wsSource As Worksheet Dim wsTarget As Worksheet Set wsSource = ThisWorkbook.Sheets("Sheet1") 'Source sheet Set wsTarget = ThisWorkbook.Sheets("Sheet2") 'Target sheet wsSource.Range("A1").Copy wsTarget.Range("A1").PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End If End Sub Regards ExcelDemy
Dear @donnapearson, Thank you for your question. You asked an interesting question, if there is a way to automatically update multiple workbooks? In fact, there are several ways and the process is similar as shown in this video. You can follow the article linked below to update the workbooks automatically. Article link: www.exceldemy.com/link-excel-workbooks-for-automatic-update/ Hopefully, this answers your query. Make sure to stay connected with Exceldemy! 🎉❤. Have a good day. Regards, Exceldemy
Hello @AhsanShakil, Removing data from Sheet 1 won't automatically update Sheet 2 unless specific connections are established. The article explains that using formulas can link data, but if you delete entries, the linked cells might return errors. Alternatively, using Power Query allows for dynamic updates, ensuring data stays current even with changes. Excel tables also help maintain structure and connectivity. Regards ExcelDemy
Hello @jeannedarc1278, If you close the referenced Excel spreadsheet, the formulas that link to it will still work, but the data will not update until you open the source file again. Excel stores the last retrieved values from the linked spreadsheet, so while the data might appear static when the file is closed, it updates the next time the file is opened. For continuous updates, the source spreadsheet must remain open or be refreshed upon reopening the file. Regards ExcelDemy
Dear, thanks for your questions. Yes, you can automatically update one datasheet to another using the VLOOKUP function. Please check this: www.exceldemy.com/wp-content/uploads/2024/03/automatically-updating-one-to-another-using-the-VLOOKUP-function.gif To achieve this, you can use the following formula: =VLOOKUP($B$3, 'Employee Records'!$B$5:$D$13, 3, FALSE)
@@exceldemy2006 if we add the new columns or rows where ever in a worksheet it should be automatically updated in the other worksheet based on our column or row names requirement . Is that possible . Please help me
Hello @brandonyan8107, Yes, we can update data from one tab to another within the same worksheet in Excel. Follow the steps to do so: 1. Select the cell in the destination tab where you want the data to appear. 2. Enter the formula: =Sheet1!A1 If you want to pull data from cell A1 on "Sheet1" 3. Press Enter, and the destination tab will automatically update whenever the source cell changes. This method works similarly to updating from another worksheet. Regards ExcelDemy
Hello @VarshithaK-xc5sf, You can use the AND function within the IF statement if you have more than one criterion. For example, if you want to check if the value in Employees!D5 is greater than 4000 and if the value in Employees!E5 is greater than 1000, the formula would look like this: =IF(AND(Employees!D5>4000, Employees!E5>1000), Employees!D5, "") If you want an exact formula you can share your dataset and criteria. Regards ExcelDemy
Hello @carlozabat5737, To contact with us you can mail us at: admin@exceldemy.com Or, you can use ExcelDemy Forum: exceldemy.com/forum/ Here, you can post and share your problem also you can start conversation too. Regards ExcelDemy
Dear, Thanks for such an exciting question. In this case, you can use an Excel VBA Event Procedure. Don't worry! We have demonstrated your situation in a workbook and developed a sub-procedure that will be triggered within the Worksheet_Deactive event. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Filter-in-one-sheet-and-multiple-sheet-gets-filter.gif You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Yukta-Bitla-SOLVED.xlsm Excel VBA Code: Sub HideRowsOnOtherSheets() Dim ws As Worksheet Dim wsMST As Worksheet Dim lastRow As Long Dim i As Long Dim rng As Range Set wsMST = ThisWorkbook.Sheets("Sheet1") lastRow = 11 For i = 2 To lastRow If wsMST.Rows(i).Hidden Then For Each ws In ThisWorkbook.Worksheets If ws.Name wsMST.Name Then ws.Rows(i).Hidden = True End If Next ws Else For Each ws In ThisWorkbook.Worksheets If ws.Name wsMST.Name Then ws.Rows(i).Hidden = False End If Next ws End If Next i End Sub Private Sub Worksheet_Deactivate() Call HideRowsOnOtherSheets End Sub
Hello @pronobkumar2807, It seems like the issue might be related to the sheet name rather than the function itself. Please double-check if the sheet name is correctly set to "Exclamation" in your workbook. If the sheet name is different, you may need to update the sheet name accordingly. =Exclamation!D5 =Your Sheet Name!Cell Reference Let me know if that resolves the issue or if you need further assistance! Regards ExcelDemy
Simple and to the point! For my needs, the first one was absolutely on point! TY!
Hello @healthcarebizbrokers,
Thanks for your feedback and appreciation. It means a lot to us. Glad to hear that the first method is on point for your needs. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Thank you for nice explanation. There is case I have linked two sheets in share folder working fine. Once I download local drive offline it’s not working. Can you share some insight on how to connect in this case.
Hello @kishoresairam,
You're welcome! The issue arises because links between Excel files work only when the file paths are consistent. The link paths break when files are moved from a shared folder to a local drive.
To resolve this:
1. Ensure files use relative paths instead of absolute ones if they stay within the same folder structure.
2. After moving the files, update the links in Excel by navigating to the Data tab >> Edit Links >> Change Source.
3. Use cloud services (e.g., OneDrive) to maintain seamless links online and offline.
Let me know if further clarification is needed!
Regards
ExcelDemy
First method worked perfectly for me. Thank You so much for the info ❤❤❤
Hello @blenkomulti-services8790,
You are most welcome. Glad to hear that the first method worked perfectly for you. Your appreciation means a lot to us.
Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Bravo!!!
Hello @lucatintor4896,
Thanks for your appreciation. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Yar makes it easy for me thanks
Hello @mobeenalam3356,
You are most welcome. Thanks for your appreciation. Glad to hear that our video made it easy for you. Keep learning Excel with ExcelDemy!
Regards
Shamima Sultana
ExcelDemy
hello good sir. I am wondering is there a way to change the format (like colour or font) of a cell and then u can see that to the linked cell? or the paste link command is only to update the value ? Thank you !
Hello @314Tesla,
You are most welcome. The 'paste link' command in Excel only updates values between linked cells and doesn’t transfer formatting such as font, color, or borders.
If you want to reflect formatting changes in linked cells, you'll need to apply the same formatting manually or use conditional formatting to automate some styling based on the values. Unfortunately, Excel doesn't natively support automatic formatting updates via links.
You can use this VBA code to copy the formatting from one cell to another whenever the source cell changes.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Set wsSource = ThisWorkbook.Sheets("Sheet1") 'Source sheet
Set wsTarget = ThisWorkbook.Sheets("Sheet2") 'Target sheet
wsSource.Range("A1").Copy
wsTarget.Range("A1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End If
End Sub
Regards
ExcelDemy
Is there a way to do this across multiple workbooks, rather than worksheets within just one workbook?
Dear @donnapearson,
Thank you for your question. You asked an interesting question, if there is a way to automatically update multiple workbooks? In fact, there are several ways and the process is similar as shown in this video. You can follow the article linked below to update the workbooks automatically.
Article link: www.exceldemy.com/link-excel-workbooks-for-automatic-update/
Hopefully, this answers your query. Make sure to stay connected with Exceldemy! 🎉❤. Have a good day.
Regards,
Exceldemy
If i remove any data from sheet 1 so it can automatically update or not
Hello @AhsanShakil,
Removing data from Sheet 1 won't automatically update Sheet 2 unless specific connections are established. The article explains that using formulas can link data, but if you delete entries, the linked cells might return errors. Alternatively, using Power Query allows for dynamic updates, ensuring data stays current even with changes. Excel tables also help maintain structure and connectivity.
Regards
ExcelDemy
What if I close the other excel spreadsheet will it still work?
Hello @jeannedarc1278,
If you close the referenced Excel spreadsheet, the formulas that link to it will still work, but the data will not update until you open the source file again. Excel stores the last retrieved values from the linked spreadsheet, so while the data might appear static when the file is closed, it updates the next time the file is opened. For continuous updates, the source spreadsheet must remain open or be refreshed upon reopening the file.
Regards
ExcelDemy
is this possible to change one sheet data automatically changed to another sheet using vlookup function?
Dear, thanks for your questions. Yes, you can automatically update one datasheet to another using the VLOOKUP function.
Please check this: www.exceldemy.com/wp-content/uploads/2024/03/automatically-updating-one-to-another-using-the-VLOOKUP-function.gif
To achieve this, you can use the following formula: =VLOOKUP($B$3, 'Employee Records'!$B$5:$D$13, 3, FALSE)
Thanks a lots for your reply... I will try it
Sir its bit work for me kindly guide me
Dear, Of course! It would be great if you could share your difficulties within the ExcelDemy Forum.
ExcelDemy Forum: exceldemy.com/forum/
@@exceldemy2006 if we add the new columns or rows where ever in a worksheet it should be automatically updated in the other worksheet based on our column or row names requirement .
Is that possible . Please help me
can you do this from one tab to another? not another worksheet
Hello @brandonyan8107,
Yes, we can update data from one tab to another within the same worksheet in Excel.
Follow the steps to do so:
1. Select the cell in the destination tab where you want the data to appear.
2. Enter the formula: =Sheet1!A1
If you want to pull data from cell A1 on "Sheet1"
3. Press Enter, and the destination tab will automatically update whenever the source cell changes.
This method works similarly to updating from another worksheet.
Regards
ExcelDemy
What if I have more than one criteria, then what is the way
Hello @VarshithaK-xc5sf,
You can use the AND function within the IF statement if you have more than one criterion. For example, if you want to check if the value in Employees!D5 is greater than 4000 and if the value in Employees!E5 is greater than 1000, the formula would look like this:
=IF(AND(Employees!D5>4000, Employees!E5>1000), Employees!D5, "")
If you want an exact formula you can share your dataset and criteria.
Regards
ExcelDemy
How can i DM you? I want to ask something.
Hello @carlozabat5737,
To contact with us you can mail us at: admin@exceldemy.com
Or, you can use ExcelDemy Forum: exceldemy.com/forum/
Here, you can post and share your problem also you can start conversation too.
Regards
ExcelDemy
How to filter in one sheet and multiple sheet gets filter?
Dear, Thanks for such an exciting question. In this case, you can use an Excel VBA Event Procedure.
Don't worry! We have demonstrated your situation in a workbook and developed a sub-procedure that will be triggered within the Worksheet_Deactive event.
Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/Filter-in-one-sheet-and-multiple-sheet-gets-filter.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Yukta-Bitla-SOLVED.xlsm
Excel VBA Code:
Sub HideRowsOnOtherSheets()
Dim ws As Worksheet
Dim wsMST As Worksheet
Dim lastRow As Long
Dim i As Long
Dim rng As Range
Set wsMST = ThisWorkbook.Sheets("Sheet1")
lastRow = 11
For i = 2 To lastRow
If wsMST.Rows(i).Hidden Then
For Each ws In ThisWorkbook.Worksheets
If ws.Name wsMST.Name Then
ws.Rows(i).Hidden = True
End If
Next ws
Else
For Each ws In ThisWorkbook.Worksheets
If ws.Name wsMST.Name Then
ws.Rows(i).Hidden = False
End If
Next ws
End If
Next i
End Sub
Private Sub Worksheet_Deactivate()
Call HideRowsOnOtherSheets
End Sub
Exclamation function is not working
Hello @pronobkumar2807,
It seems like the issue might be related to the sheet name rather than the function itself. Please double-check if the sheet name is correctly set to "Exclamation" in your workbook. If the sheet name is different, you may need to update the sheet name accordingly.
=Exclamation!D5
=Your Sheet Name!Cell Reference
Let me know if that resolves the issue or if you need further assistance!
Regards
ExcelDemy