Spreadsheet/Database question - not sure where or how to ask it

Factual Questions
1

Hi-

So back in the day, I used to have software called “Microsoft Works”. It was the free OEM version of Office back in the '90s.

Anyway, it had a simple word processor (stripped down Word), spreadsheet (stripped down Excel), database (stripped down Access), etc…

I created a “database” on there, it was really just a spreadsheet, but I could also access it full page, one row per page.

Does that make sense? So A1 is Name, B1 is Address, C1 is phone, D1 is details, etc… so it’s a regular spreadsheet.

But I can also access it via “database” and see all and only the records for one row, formatted to look nice on one page. The top left would have name, address, phone, etc… the top right maybe a pic, then full width a a full text box but it’s really all just in the spreadsheet and I’m just viewing it from another “angle”.

I want that capability again as in my current business i deal with a lot spreadsheets and would like to consolidate in this manner.

Also, and I know I’m crazy for even asking, but my new company uses Google Apps for Work (we pay for it) and our spreadsheets are there… With everybody using different platforms (MS, Android, IOS, etc) it’s essential we collaborate “in the cloud”.

Anybody have any suggestions? I really just want to be able to view my google sheets one record at a time, full page, with unique formatting for each database.

Thanks

2

I think what you want is a pivot table report based on your main sheet.

I don’t know much about them (I’ve managed to create one or two, but very brute force/trial-and-error). Try reading from here and see if it meets your needs - Create & use pivot tables - Computer - Google Docs Editors Help

3

I used to use Microsoft Works extensively. The “database” is really just a glorified spreadsheet, not even a stripped-down Access.

What you are talking about is creating a form to view one record at a time from the spreadsheet data. (Folacin, this is different than what a pivot table will do for you.) Excel has an obscure built-in feature to provide a simple form to do this. You could do something fancier with VBA. But you not using Excel, so that answer is useless.

I have a Google account but have never tried what you are describing. I’ll take a look and see if I can offer any help.

4

Google Forms.

5

Thanks for the reply.

I can use Excel if necessary to accomplish my goals.

Please just show me how to do that the old way with Office 2013?

Thanks

6

The really cheap way is just to shrink the Excel window to a height large enough for the tool bars and one row of data. Why wouldn’t this work for you?

7

You can link an excel spreadhsheet into Access as a database

8

Create a new sheet in google sheets, with cells in the layout you want.

Create a cell (CELL1) with a text reference to the sheet containing your raw data.
Create a second cell (CELL2)
In the layout cells you created entered formula:

=indirect(CELL1)&[column containing info]&(CELL2).

repeat as necessary.

Change number in CELL2 to reflect row containing info you want to display

9

There is an obscure command called Form that is not available from the Ribbon but you can make a shortcut button for it. This will not work for you if you have to distribute the file to other people, since it is Excel configuration specific to your machine, not the file.

Right click on the Quick Access Toolbar and select Customize The Quick Access Toolbar. On the left change the dropdown to Commands Not On The Ribbon. Then find Form…, click, then Add.

Now from an Excel sheet, click on your new Form button, and it will show you a list of field names taken from row 1, and all the corresponding values for one row. You can view and edit values, add and delete rows, scroll through records, and do simple searches.