Help me escape Excel hell
September 10, 2015 1:59 PM Subscribe
I need to build a database that's viewable on the web, but for internal use only, among friends. How to do this painlessly? Google spreadsheet is not exactly making it any easier.
Basically it's just a list of keywords on Column A, and then corresponding links on Column B.
I have tried doing it on Google spreadsheet so we can all share the doc, but it's getting too tedious. The data has become a bit overwhelming, that I even tried to make a table of contents (of sorts), but hyperlinking to a cell within the same sheet or other sheets in the same file doesn't seem to work, or maybe I am just an idiot at figuring it out.
I had another idea of just making an HTML page, so that for each keyword people could just click a button of the link, instead of the two-step dance that happens on Excel (click or hover over the cell, then a small link appears for you to click again). But would that entail even more time and effort?
What do you think would be the best way to go about this?
Basically it's just a list of keywords on Column A, and then corresponding links on Column B.
I have tried doing it on Google spreadsheet so we can all share the doc, but it's getting too tedious. The data has become a bit overwhelming, that I even tried to make a table of contents (of sorts), but hyperlinking to a cell within the same sheet or other sheets in the same file doesn't seem to work, or maybe I am just an idiot at figuring it out.
I had another idea of just making an HTML page, so that for each keyword people could just click a button of the link, instead of the two-step dance that happens on Excel (click or hover over the cell, then a small link appears for you to click again). But would that entail even more time and effort?
What do you think would be the best way to go about this?
They can use Ctrl+F to find any word in a spreadsheet, so you shouldn't need a Table of Contents.
The data has become a bit overwhelming
Do you know you can sort in Excel? All you need to do is type the new word and link in the last line and then resort the page. A webpage will be easy if you know a bit of HTML. What is taking so long and making it so tedious?
posted by soelo at 3:16 PM on September 10, 2015
The data has become a bit overwhelming
Do you know you can sort in Excel? All you need to do is type the new word and link in the last line and then resort the page. A webpage will be easy if you know a bit of HTML. What is taking so long and making it so tedious?
posted by soelo at 3:16 PM on September 10, 2015
How many rows do you have? How about a Google doc, or OneDrive doc with a table?
posted by cnc at 4:38 PM on September 10, 2015
posted by cnc at 4:38 PM on September 10, 2015
How many rows of data do you have already? Where do new data come from, and in what format?
posted by flabdablet at 9:20 PM on September 10, 2015
posted by flabdablet at 9:20 PM on September 10, 2015
Response by poster: We are already working on Google Spreadsheet because we need for it to be viewable (and editable) by a lot of us online.
Is it possible to hyperlink to another cell within the same spreadsheet (not just workbook)?
I know we can simply use CTRL+F but that would add another step to the workflow. I know we can use sort but we have merged cells and that's not the solution we are looking for.
The data is this:
COLUMN A: keyword
COLUMN B: hyperlink
There are, give or take, 500 keywords, and probably 2-3 links allotted per keyword.
I found this HTML5 editable table and it looks helpful. Are there other resources like this, like I just input data in a form somewhere and it spits it out all good looking and stuff?
posted by pleasebekind at 12:25 PM on September 11, 2015
Is it possible to hyperlink to another cell within the same spreadsheet (not just workbook)?
I know we can simply use CTRL+F but that would add another step to the workflow. I know we can use sort but we have merged cells and that's not the solution we are looking for.
The data is this:
COLUMN A: keyword
COLUMN B: hyperlink
There are, give or take, 500 keywords, and probably 2-3 links allotted per keyword.
I found this HTML5 editable table and it looks helpful. Are there other resources like this, like I just input data in a form somewhere and it spits it out all good looking and stuff?
posted by pleasebekind at 12:25 PM on September 11, 2015
This: =+HYPERLINK("#B30") would link to cell B30 on the current worksheet in Excel. Doesn't look like it works in Google Sheets, though; HYPERLINK only works for URLs.
posted by Barry B. Palindromer at 3:06 PM on September 11, 2015
posted by Barry B. Palindromer at 3:06 PM on September 11, 2015
An HTML page sounds like a pretty good solution, and the HTML itself can be quite simple. I put together a quick bare-bones example which you can see here. The trick will be to not have to code the entire page by hand for all 500 or so keywords. Exactly how to do that would depend on how your data is kept, particularly the links in column B. Are the links just URLs or is there some description text in there as well? When there are multiple links for a keyword, how are those links separated? (I'm assuming that the links for a given keyword are all in the same cell in your spreadsheet.) A space character separating one link from another?
posted by dashdash at 7:45 PM on September 11, 2015
posted by dashdash at 7:45 PM on September 11, 2015
Response by poster: @dashdashdash
New cell for every link, then we just merge opposite cells in Column A which contains the keyword.
The HTML might be a good idea, but can that be viewable if I put the file in Google Drive?
posted by pleasebekind at 10:29 PM on September 11, 2015
New cell for every link, then we just merge opposite cells in Column A which contains the keyword.
The HTML might be a good idea, but can that be viewable if I put the file in Google Drive?
posted by pleasebekind at 10:29 PM on September 11, 2015
Yes, you can host static webpages on Google Drive, just as I did with my example HTML file on Dropbox, but Google is turning this functionality off next September, unfortunately.
I've used your question as a learning opportunity and figured out, as an alternative, how to use Google App Scripts to publish a web app (in my case, nothing more than a simple web page, really) that pulls data from a Google spreadsheet I created. Issues regarding authorization and accessibility are not particularly intuitive, however, so you'll want to be cautious here, especially if your data is sensitive, because I'm not sure if you can set up a Google web app such that only a certain group of people are able to access it. (The options for who has access to the app seem to be limited to "Only myself", "Anyone", and "Anyone, even anonymous".) I'll keep my sample web app, which you can find here, up for a few days. The keywords and the links (in this case, to Wikipedia articles) come from my Google spreadsheet. If this looks like something you'd like to look into further, let me know and I'll give you the code that I used.
Another option, quite a bit easier than the above, is to perhaps consider the bookmarking service Pinboard. It costs $11/year, allows for private bookmarks either on a case-by-case basis or by default, and lets you tag/keyword the bookmarks. The only major drawback here is that if you decide to keep the bookmarks private, then anyone who you'd want to see those bookmarks would need to be able to log into the account.
posted by dashdash at 2:23 PM on September 15, 2015
I've used your question as a learning opportunity and figured out, as an alternative, how to use Google App Scripts to publish a web app (in my case, nothing more than a simple web page, really) that pulls data from a Google spreadsheet I created. Issues regarding authorization and accessibility are not particularly intuitive, however, so you'll want to be cautious here, especially if your data is sensitive, because I'm not sure if you can set up a Google web app such that only a certain group of people are able to access it. (The options for who has access to the app seem to be limited to "Only myself", "Anyone", and "Anyone, even anonymous".) I'll keep my sample web app, which you can find here, up for a few days. The keywords and the links (in this case, to Wikipedia articles) come from my Google spreadsheet. If this looks like something you'd like to look into further, let me know and I'll give you the code that I used.
Another option, quite a bit easier than the above, is to perhaps consider the bookmarking service Pinboard. It costs $11/year, allows for private bookmarks either on a case-by-case basis or by default, and lets you tag/keyword the bookmarks. The only major drawback here is that if you decide to keep the bookmarks private, then anyone who you'd want to see those bookmarks would need to be able to log into the account.
posted by dashdash at 2:23 PM on September 15, 2015
This thread is closed to new comments.
Example:
=HYPERLINK("[.\]Sheet1!A1","Hyperlink 1")
You just need to make sure you include the [.\] before the sheet name, or the cell name if it's on the same sheet. This works on Excel 2010.
posted by motioncityshakespeare at 2:08 PM on September 10, 2015