What's new
By:
USCHO Fan Forum

This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • The USCHO Fan Forum has migrated to a new plaform, xenForo. Most of the function of the forum should work in familiar ways. Please note that you can switch between light and dark modes by clicking on the gear icon in the upper right of the main menu bar. We are hoping that this new platform will prove to be faster and more reliable. Please feel free to explore its features.

Excel Help v. 1,000,000

Re: Excel Help v. 1,000,000

bigblue_dl said:
Resurrecting this....

This might be solved, somehow, using the hint that FreshFish posted a few posts early, but I can't quite figure out how.

I want to have 2 different spreadsheets, one containing several tabs, each containing data, and the other with a form that I want to populate with the data. The second sheet is a template for a datasheet that I need to create and publish, and first will contain all of the data for all of the variations of the datasheet that I'll need. What I want to be able to do is to go into the second sheet, and select, from a drop down menu, a tab from the first sheet, and have the data from the selected tab populate the template. I am not sure if this is even possible, but I need to give it a try,or else I'll end up with 30-40 spreadsheets that I need to change every time there is a template change.

Any help is greatly appreciated!
Click to expand...
There is probably a simpler way, like using tables, but you could use a drop down and formulas to achieve this.

Each field you want filled in on the output page would have an INDIRECT formula in it that points to the data it needs.

If you had a worksheet called DATA1 and DATA2 listed in a drop down menu. Selecting either of those values would fill in the INDIRECT formula for each field that needs to be populated and find the correct value from that worksheet.

Example: =INDIRECT(CONCATENATE(A3,"!","A1"))

A3 is the drop down menu choice: DATA1
"!" makes it a worksheet reference when indirect uses it.
"A1" is the data you want to take from that worksheet.

If the field on DATA1!A1 had the value "Hi", that is what would appear on the output page.
 
Last edited:
Re: Excel Help v. 1,000,000

burgie12 said:
Would/do the values in the template point to the same location in each of the data tabs or would it have to point to a different location depending on which tab you were pulling information from?

Even if you're pulling from different locations, you could use named ranges so that it pulls from a consistent place (instead of having it switch from cell A5 on one tab and A10 on another, set up A5 to be "VALUE1" on the first tab and have A10 be "VALUE1" on the second tab and have the form pull its information from VALUE1 on the relevant tab).

Data validation is definitely the way to go on the dropdown box. And then just making sure that the values in the template point to the correct place is probably helped by the INDIRECT method mentioned earlier or the INDEX(MATCH()) type stuff.
Click to expand...
I am already using data validation for the dropdown menus, that is working as I would expect.
My data will be pulling from the same cells on each tab, for example, if "horsepower" is in cell E4 on the Engine1 tab, it will also be in E4 on the Engine2, Engine3, Engine4, etc. tabs.
 
Re: Excel Help v. 1,000,000

JF_Gophers said:
There is probably a simpler way, like using tables, but you could use a drop down and formulas to achieve this.

Each field you want filled in on the output page would have an INDIRECT formula in it that points to the data it needs.

If you had a worksheet called DATA1 and DATA2 listed in a drop down menu. Selecting either of those values would fill in the INDIRECT formula for each field that needs to be populated and find the correct value from that worksheet.

Example: =INDIRECT(CONCATENATE(A3,"!","A1"))

A3 is the drop down menu choice: DATA1
"!" makes it a worksheet reference when indirect uses it.
"A1" is the data you want to take from that worksheet.

If the field on DATA1!A1 had the value "Hi", that is what would appear on the output page.
Click to expand...
Ok, this seems like what I need to do. I'm going to give it a try and see if I can get it to work.

edit: Alright, I got the basics of this method to work. I'm hoping I'll be able to make this work now. Finally I have a shimmer of hope! Thanks guys!
 
Last edited:
Re: Excel Help v. 1,000,000

bigblue_dl said:
Ok, this seems like what I need to do. I'm going to give it a try and see if I can get it to work.
Click to expand...
Remember, if you are using 2 separate workbooks, than the workbook reference will also have to be included before the worksheets tab name.

Also, in that case, both workbooks would have to be open for it to populate.
 
Re: Excel Help v. 1,000,000

JF_Gophers said:
Remember, if you are using 2 separate workbooks, than the workbook reference will also have to be included before the worksheets tab name.

Also, in that case, both workbooks would have to be open for it to populate.
Click to expand...
Thanks. I did this. Ran into one hiccup, apparently the file name cannot have a space in it for it to work. But other than that, it seems to be working like I wanted it to. Now I have a whole bunch of formulas to type into this spreadsheet. On the bright side, once this project is done, it will save a **** ton of time going forward.

Thanks for the help.
 
Re: Excel Help v. 1,000,000

I got it figured out completely now. I have functions that have =CONCATENATE(INDIRECT(CONCATENATE.....and =ROUND(INDIRECT(CONCATENATE..... all embedded within each other, with several functions per cell, but it is working.
 
Re: Excel Help v. 1,000,000

Slap Shot said:
Been trying to get the powers that be to implement Access, but I don't think enough people know how to use it to make it universal enough.
Click to expand...
I don't know how to use it, I was considering it, but I don't think I could do what I need to with Access. I need some complicated graphs/charts in this, and as far as I could tell, I couldn't use Access for that.
 
Re: Excel Help v. 1,000,000

bigblue_dl said:
I don't know how to use it, I was considering it, but I don't think I could do what I need to with Access. I need some complicated graphs/charts in this, and as far as I could tell, I couldn't use Access for that.
Click to expand...

Charts and graphing in Access are not nearly as good as Excel, but it does have some capability. It would be much better for the rest of what it sounds like you are doing. It can export to Excel for charts and graphs, and you can automate pretty much anything with macros. Learning Access can take a bit and classes may not be that helpful, hands on building and breaking things was what taught me the most.
 
Re: Excel Help v. 1,000,000

I would recommend not using Access in a company that's medium to large size. It becomes untenable pretty fast. You will want to go to real a data warehouse solution. Access is fine for smallish companies tho.
 
Re: Excel Help v. 1,000,000

JF_Gophers said:
I would recommend not using Access in a company that's medium to large size. It becomes untenable pretty fast. You will want to go to real a data warehouse solution. Access is fine for smallish companies tho.
Click to expand...

As the primary database yes, but Access can still be useful for reporting from data warehouses that have horiffic reporting features.
 
Re: Excel Help v. 1,000,000

jerphisch said:
As the primary database yes, but Access can still be useful for reporting from data warehouses that have horiffic reporting features.
Click to expand...
We do this all the time for ad hoc queries, link to SQL tables but write the queries in Access. Our in-house development apps using SQL queries, and for their ad hoc queries. While that'd be nice for we BAs, the bosses are not willing to pay the licensing fees for us to have SQL access.
 
Re: Excel Help v. 1,000,000

JF_Gophers said:
I would recommend not using Access in a company that's medium to large size. It becomes untenable pretty fast. You will want to go to real a data warehouse solution. Access is fine for smallish companies tho.
Click to expand...
Well, I work for a fairly large company. The long-term solution to the problem I'm solving is, hopefully, going to be a database like you're talking about, but the people that would do that work (I'm definitely not qualified), aren't doing anything with it. This is why I'm doing this excel project, to get us by in the meantime until the final project is launched and completed.
 
Re: Excel Help v. 1,000,000

Let's suppose I have two columns of data, and each column either has an X or doesn't:

ColA ColB
1. X
2. X X
3.
4. X
5. X X


I want to count how many of my five rows have an X in either column. If I use COUNTIF(range,"X"), I'll return the total number of Xs I see in the entire range. If I use COUNTIFS(ColA,"X",ColB,"X"), then I'm going to return the number of rows that have an X in both columns. Is there a function similar to COUNTIFS that I can use that would have an OR function embedded into it?

I'd prefer to not have to create a third column with IF(OR(ColA="X",ColB="X"),"X","") and run COUNT(ColC,"X").
 
Re: Excel Help v. 1,000,000

ExileOnDaytonStreet said:
Let's suppose I have two columns of data, and each column either has an X or doesn't:

ColA ColB
1. X
2. X X
3.
4. X
5. X X


I want to count how many of my five rows have an X in either column. If I use COUNTIF(range,"X"), I'll return the total number of Xs I see in the entire range. If I use COUNTIFS(ColA,"X",ColB,"X"), then I'm going to return the number of rows that have an X in both columns. Is there a function similar to COUNTIFS that I can use that would have an OR function embedded into it?

I'd prefer to not have to create a third column with IF(OR(ColA="X",ColB="X"),"X","") and run COUNT(ColC,"X").
Click to expand...

What about:
SUM(COUNTIFS(ColA,"X"),COUNTIFS(ColB,"X"),(-1*COUNTIFS(ColA,"X",ColB,"X")))
 
Re: Excel Help v. 1,000,000

FlagDUDE08 said:
What about:
SUM(COUNTIFS(ColA,"X"),COUNTIFS(ColB,"X"),(-1*COUNTIFS(ColA,"X",ColB,"X")))
Click to expand...
Ya, the quick and dirty 1 cell solution is that or this:

=SUM(COUNTIF(A1:B1,"x"),COUNTIF(A2:B2,"x"),COUNTIF(A3:B3,"x"),COUNTIF(A4:B4,"x"),COUNTIF(A5:B5,"x"))
 
You must log in or register to reply here.
Back
Top