[SOLVED] Google sheets/excel help

[Jackmcintyre]

Hi guys! I was just wondering if anyone knew a formula that I could use to get my spreadsheets on excel/or docs Better,

on sheet 1 I have a list of tasks with codes, descriptions and prices, on Sheet 2 I have a job sheet where I would like to be able to put in the job code and then it automatically transfer over the prices and description,

in sheet one I have hundreds of codes and prices.

any help would be muchly appreciate.

cheers

jack

 

[Ralston18]

Excel:

VLOOKUP function perhaps?

Try/test on a copy of the original spreadsheets.

 

[sizzling]

Harder to write but I prefer Index and Match over VLOOKUP and HLOOKUP. Google them and you will find good guides on how to use them. Might also benefit using tables if you have large sets of data.

 

[USAFRet]

This is specifically what a database is for, vs a spreadsheet.

This is trivially done on Access.

 

[USAFRet]

I'd be surprised if there weren't a 95% solution for this in one of the templates included with MS Access.

 

[sizzling]

Agree in Access this would be very easy. If you want to use Excel another option I just thought of is under ‘Data’ tab and then Get & Transform Data select From Table/Range. This opens Power Query Editor. This method is very good if you want to build a model that can be reused and refreshed easily. Again Google is a big help with learning how to use this tool.

 

[Ralston18]

With respect to the preceding context/posts, if it was up to me I would migrate to Access.

Overall, you can set up the Excel spreadsheets as a RO back end data source into Access.

And at some later time import the Excel data into Access.

Just set up a separate computer and copy the Excel spreadsheets and data over. Depending on details it is very likely that the Access wizards will do most of the work.

You will need to run parallel systems (Excel and Access) for awhile but that is not at all unusual. Maintain all backups as normal.

Once everything is functional in Access you will be able to "retire" Excel.

And have much more flexibility with respect to current and future requirements for job codes and pricing.

With queries and reports galore. Ad hoc or otherwise.