Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I have an Excel workbook with various tabs that link through to each other. I want to share it with my client via a link, so they can see daily updates on the activity tracker tab.
I don't want them to see some of the other tabs.
One option would be to copy the one sheet daily and send it them, but I don't really want that hassle as minimal as it is. I could hide the tabs and lock the sheet, but a lot of the data inputs are on the tabs I'd want to hide.
So what I really want, is to send them a link to the document where they can only see the updates on the one sheet in a live view as it updates daily.
Is this possible?
Do you need to hide the tabs? Can you not just password protect the other sheets so they can't view them?
You can also reference data from another workbook thus keeping their "view" separate (and password protect your sheet so they can't just open that).
Set every cell in this new spreadsheet to be equal to the same cell in the tab you want to share
This is along the lines what I was thinking - my Excel knowledge is very basic. I generally google stuff. How do I do that for all the cells?
Do you need to hide the tabs? Can you not just password protect the other sheets so they can’t view them?
That's possible - didn't know I could do that to individual tabs
Very Hidden option or something like this to prevent VBA user accessing?
Sub StillHide()
Dim ws As Worksheet
For Each ws In Sheets
ws.Visible = xlSheetHidden <-- change from Visible to this.
Next
End Sub
If you've come this far maybe you're willing to come a little further and use Power BI...
Edit: If you have Power BI in your organisation it might be worth considering as it's a data reporting software by Microsoft. You can send users a link to it and set up a regular refresh to save emailing every day. It connects to data so you'd have to arrange with the user if they need to provide data. You could do this in numerous ways as Power BI connects to loads of data sources. Simple would be for user to fill in an Excel or CSV then email you and set up a flow to save these in a folder where Power BI would extract data from.
Edit 2: If you have Power Apps too then you could create a simple form for user to fill in and in the same screen or app you could insert a link to the Power BI report (embedding sucks).
You might want to google for “publish excel to web” - I mostly use google sheets these days but I think excel has a similar but clunkier way to do the same thing which is designed for exactly this sort of use case.
Very Hidden option or something like this to prevent VBA user accessing?
Sub StillHide()
Dim ws As Worksheet
For Each ws In Sheets
ws.Visible = xlSheetHidden <– change from Visible to this.
Next
End Sub
That may as well be written in Swahili, but thanks lol
You might want to google for “publish excel to web” – I mostly use google sheets these days but I think excel has a similar but clunkier way to do the same thing which is designed for exactly this sort of use case.
I don't have any problem sharing it - it's on my Onedrive, so I just send a link (unless I've misunderstood your answer)
I don’t have any problem sharing it – it’s on my Onedrive, so I just send a link (unless I’ve misunderstood your answer)
Sorry was typed on my phone in a rush. The publish to web stuff in google sheets gives your the options which "tabs" of a spreadsheet are published (and the format of the data - we do it with reports in PDF as well as some raw data in CSV). I think Excel has something similar: https://support.microsoft.com/en-us/office/save-all-or-part-of-a-workbook-to-a-static-web-page-5ad26dee-8739-4d80-b9d9-cf0530ab1968
Excel has something similar:
Yes, sorted it. Many thanks
Sorry for the slow reply . Just in case
in cell A1 type = then click on cell A1 in your other spread sheet then press enter
Then drag that cell to copy it into say 30 columns. Then say drag that down 100 rows
Sorry for the slow reply . Just in case
in cell A1 type = then click on cell A1 in your other spread sheet then press enter
Then drag that cell to copy it into say 30 columns. Then say drag that down 100 rows
I tried this numerous times, but I ended up with a load of cells that did (sort of) match the linked sheet, but every empty cell populated with a zero.
Also, merged cells caused a massive problem and on a pretty big sheet, none of the column or row widths matched, none of the highlighting/fills carried over. It was a huge mess that would have taken ages to sort
Are ok. I have been warned. My shares are clearly simpler on formatting