Question How can I show information from one sheet on a different one when it changes ?

[ReveurGAM]

I have a spreadsheet (Inv) that lists the items I've purchased, including what type of item.
In a separate sheet (Fan Data), I want to show certain datum (Brand, Model) for each item if it is of type "Fan". Other data in Inv won't be used. the Fan Data tab will contain technical specs on each fan, whereas Inv is just an inventory, showing brand, model, description, price, quantity and type.
What is in the Inv tab will change over time, so I need the Fan Data tab to automatically adjust its list of fans so that I don't have to manually copy-paste or add formulas.
What is the correct formula to achieve that?

Eventually, another sheet will combine data from multiple sheets (Inv, Fan Data, Fan Test, maybe others) from which I can create comparison graphs.
What is the best way to do that?

Thanks in advance!

Oh, this will be using different spreadsheets depending on which computer - Excel, Open/Libre Office, online programs for sharing with patrons only, etc.

 

[hotaru.hino]

Assuming all of these sheets are in a single spreadsheet document, there are ways to references other sheets. See https://www.ablebits.com/office-addins-blog/excel-reference-another-sheet-workbook/

 

[USAFRet]

Oh, this will be using different spreadsheets depending on which computer - Excel, Open/Libre Office, online programs for sharing with patrons only, etc.

Please expand on this.
There is no single solution for multiple platforms.

 

[Eximo]

Sounds like you need a database rather than multiple spreadsheets. You can still use spreadsheets as an interface, but a website is probably better.

You should look into some open source SQL solutions like MySQL for the backend and maybe even some free web templates for that sort of thing.

Or build out something with a cloud provider like Azure/Microsoft or Amazon.

For myself I am more stuck with the Microsoft Ecosystem, so that would likely end up being a Power App or Power BI application.

 

[USAFRet]

Sounds like you need a database rather than multiple spreadsheets.

So much this.

 

[ReveurGAM]

Please expand on this.
There is no single solution for multiple platforms.

What do you need to know? All of the data is in one file but different tabs.

 

[USAFRet]

What do you need to know? All of the data is in one file but different tabs.

I meant the multiple platforms.
Excel, OO, online....

The specific string that works in Excel would not work in OpenOffice.

 

[USAFRet]

In Excel....

On a cell in the FanData sheet, to reference data on the InvSheet
"=InvSheet!A2"

This shows whatever value is in Cell A2 on the InvSheet.

The equivalent in LibreOffice Calc is :
"=$InvSheet.A2"

 

[hotaru.hino]

Rather than rolling out a generic database and web app tailored to their use case, it might be better to find some sort of inventory management software that does all the annoying parts for others. And this is assuming using spreadsheets is getting to cumbersome for them.

Unless OP has a dedicated IT team, better to find software that does the job than make it themselves.

 

[ReveurGAM]

Rather than rolling out a generic database and web app tailored to their use case, it might be better to find some sort of inventory management software that does all the annoying parts for others. And this is assuming using spreadsheets is getting to cumbersome for them.

Unless OP has a dedicated IT team, better to find software that does the job than make it themselves.

I'm a one-man operation on a shoe-string budget. Any suggestions?

 

[ReveurGAM]

In Excel....

On a cell in the FanData sheet, to reference data on the InvSheet
"=InvSheet!A2"

This shows whatever value is in Cell A2 on the InvSheet.

The equivalent in LibreOffice Calc is :
"=$InvSheet.A2"

But when saving the file for the target spreadsheet, those formulas are converted, right?

Currently, I have the spreadsheet in Excel 2010 on my laptop as I use my desktop for testing, and that will likely be the main location for most things, but it's slow and sometimes I will need to use it on my desktop, which doesn't have Office. OpenOffice doesn't work correctly on W11 AFAIK, so I'd either be using Libre or Google Sheets.
Also, I need to have it only take items that match what I'm working in, so I assume I'd need to have that embedded in an if statement?

 

[USAFRet]

But when saving the file for the target spreadsheet, those formulas are converted, right?

Unknown, without extensive testing.

Currently, I have the spreadsheet in Excel 2010 on my laptop as I use my desktop for testing, and that will likely be the main location for most things, but it's slow and sometimes I will need to use it on my desktop, which doesn't have Office. OpenOffice doesn't work correctly on W11 AFAIK, so I'd either be using Libre or Google Sheets.
Also, I need to have it only take items that match what I'm working in, so I assume I'd need to have that embedded in an if statement?

Pick one and go with that.
Google Sheets is almost certainly your best option. You, and others, can access it through a web browser. Not "Office" or "Libre".

Also, I need to have it only take items that match what I'm working in, so I assume I'd need to have that embedded in an if statement?

Without knowing exactly what you're trying to do, a comprehensive design is impossible.

 

[Math Geek]

to be more specific in helping as others have said, we need a lot more detail about what you are doing.

what info is on each sheet, why move some of it to another sheet, EXACTLY what is the purpose of each sheet, how you use it, what you hope to do, why and so on.

you're beyond a simple "how do i ____" and into, "i am trying to do something complicated and need some help getting really deep into it"

even something as easy to use as Access could make this easier. connecting multiple pages of info, using custom forms to pull whatever info you are looking for at the moment and keeping it all organized is exactly what Access is for. not hard to learn really. nothing a quick office course couldn't teach you in a couple weeks or less.

it's not as deep as SQL and the like but for a one man operation it's good enough for some pretty complex work. something to consider anyway if this is really important to you and worth the effort for you to learn

took me a couple weeks to learn Access and set up a database for a non profit to keep data on thousands of people. couple of custom input and output forms/reports and anyone can sit in front of the pc and input data to be used later or pull a list of names to make contact with easy enough.

 

[ReveurGAM]

to be more specific in helping as others have said, we need a lot more detail about what you are doing.

what info is on each sheet, why move some of it to another sheet, EXACTLY what is the purpose of each sheet, how you use it, what you hope to do, why and so on.

you're beyond a simple "how do i ____" and into, "i am trying to do something complicated and need some help getting really deep into it"

even something as easy to use as Access could make this easier. connecting multiple pages of info, using custom forms to pull whatever info you are looking for at the moment and keeping it all organized is exactly what Access is for. not hard to learn really. nothing a quick office course couldn't teach you in a couple weeks or less.

it's not as deep as SQL and the like but for a one man operation it's good enough for some pretty complex work. something to consider anyway if this is really important to you and worth the effort for you to learn

took me a couple weeks to learn Access and set up a database for a non profit to keep data on thousands of people. couple of custom input and output forms/reports and anyone can sit in front of the pc and input data to be used later or pull a list of names to make contact with easy enough.

I understand. I am happy to answer any questions because even if I use Access, I'll still want to use Excel and I like to learn.

I learned Access decades ago and have forgotten, so I'll have to learn again. Can you recommend a good, free course that will teach me how to make custom forms, set up the relational database, etc?

I'm reviewing fans and, later, other things. Currently, I'm trying to find a great anemometer and manometer that I can afford, which is not that easy since the cheap ones tend to be inaccurate at the least, and the great ones are around$1,000+.

At this point, I've got a tab for my inventory, which is already over 100 items, another for detailed specs for each, and a third for fan test results. I want to be able to copy (not move) the make and model from inv to specs and tests in a way that it'll auto-populate as my inventory increases.

Inv has make, model, description, price, quantity and type.

 

[USAFRet]

MS Access, the current and recent versions, has an included template for Inventory.

 

[grantorinounforgiven]

I created a google sheet, like a master spreadsheet, and used software (Unito) to sync excel to google sheets. Haven't had any problems keeping Inventory up to date. You can set the flow the way you need it to be (1-way or 2-way) and use rules to filter out the rows you don't want to sync. I also synced the master sheet with google calendar, so it creates an event when stock replenishment is needed.

 

[Corwin65]

Have you looked at free inventory management software?

 

[ReveurGAM]

I created a google sheet, like a master spreadsheet, and used software (Unito) to sync excel to google sheets. Haven't had any problems keeping Inventory up to date. You can set the flow the way you need it to be (1-way or 2-way) and use rules to filter out the rows you don't want to sync. I also synced the master sheet with google calendar, so it creates an event when stock replenishment is needed.

Sounds like you have a good system. Is Unito expensive?

 

[ReveurGAM]

Have you looked at free inventory management software?

Not yet. I'm trying to do everything in one app or suite because I analyze data and make graphs, too.

 

[ReveurGAM]

Have you looked at free inventory management software?

Did you have a suggested program?

 

[Corwin65]

Did you have a suggested program?

I just googled some, see if you find some that meet your needs.

 

[grantorinounforgiven]

Sounds like you have a good system. Is Unito expensive?

Not the plan that works for me, it's $24 per month (sync up to 150 items, enough for me). I think the next one with 350 items in sync is $45 or something like that.

 

[ReveurGAM]

Not the plan that works for me, it's $24 per month (sync up to 150 items, enough for me). I think the next one with 350 items in sync is $45 or something like that.

When you say items, I assume that is literally that, and not files... Correct? I've already got over 100 fans, with many more planned, and will eventually add CPU coolers.

 

[grantorinounforgiven]

When you say items, I assume that is literally that, and not files... Correct? I've already got over 100 fans, with many more planned, and will eventually add CPU coolers.

For example, one spreadsheet row counts as one item, or one task/card/issue - depending on the tool you're syncing, so yes, I believe you got it right. Probably 350 items would work better for you. Anyway, there's a free trial, you can test it and see if it makes sense. You could also check out this guide about the whole process, how to sync excel to google sheets, just to get an idea. I didn't find it particularly difficult to set things up, just followed the steps. However, there are some features that you might find super useful, for example, and they didn't really matter to me. Plus, there's a video to watch, you could look into it when you find time. I hope this helps. I mean, you said you're a one-man team and, in my case, we're a small team. So more than enough things to keep track of at the moment, and forgetting to do just one of them could cause problems. I can honestly say it's a relief I found a system that works, and I wish you the same.

 

[ReveurGAM]

For example, one spreadsheet row counts as one item, or one task/card/issue - depending on the tool you're syncing, so yes, I believe you got it right. Probably 350 items would work better for you. Anyway, there's a free trial, you can test it and see if it makes sense. You could also check out this guide about the whole process, how to sync excel to google sheets, just to get an idea. I didn't find it particularly difficult to set things up, just followed the steps. However, there are some features that you might find super useful, for example, and they didn't really matter to me. Plus, there's a video to watch, you could look into it when you find time. I hope this helps. I mean, you said you're a one-man team and, in my case, we're a small team. So more than enough things to keep track of at the moment, and forgetting to do just one of them could cause problems. I can honestly say it's a relief I found a system that works, and I wish you the same.

Thank you so much! I wish you success!