Formula to populate the next available cell in a column using data populated in another cell.

[Marshkevuk]

Hello, New member here, but from what I've been reading, this place in a mine of great info' and helpful souls, so here goes!

OK, I know just enough about formulas/excel to know when I'm out of my depth and having copied several formulas and trying then to adapt them to fit my needs, I know that it's time to ask some kind soul to throw me a rope! 

So, using the image of my spreadsheet below, let me try to explain what I am trying to achieve.

• I have data saved on another few tabs that can be searched using drop downs in B4:E4 (The data on the other tabs will be locked and hidden).
• The pertinent reference # is then returned in B5:E5.
• You can also enter the reference, if known, directly into F5. This, along with the date cell will hopefully be the only unlocked cells in the spreadsheet.
• What I would like to happen then is to have a formula in A10:A50 that grabs that reference # from B5:F5 and populates the next available empty cell in column A.
• This reference will then, when the return key is pressed, or the following cell is selected, populate specific stored data in the corresponding cells, B10:G10 and so on.

Hopefully this is possible and I'm not just living in dreamland, but if i am please let me know. This spreadsheet will be used by quite a few people who know nothing about excel and I'd like to make it as foolproof as possible, hence the single input cell and drop downs.

Any and all help, assistance and positive comments would be most welcome. I'm sure reading that after reading the above you'll understand where my level of "expertise" is!

Thanks in advance .

 

[KRice]

Welcome to the MrExcel board! A quick comment about your profile: thank you for using the Excel version field...the specific version(s) of interest help others determine an appropriate solution, as newer versions of Excel (365/2021) have enhanced functions that are not available in older versions, and the approaches taken for solutions can be dramatically different. To that end, I would recommend that you revise your profile to show only the version(s) of Excel being used actively.

Regarding your request, how would we know which tabs should be searched for the filtering terms in B4:E4? And what is the Ref #?...is it an indexing number that is unique to each record across all data source worksheets? What is the format/structure of the data sources on the other worksheets?...are they all the same? Could they be combined into a single large table for more convenient referencing? This could be done behind the scenes and not actively maintained, it would serve only as a way to create a single, large source table that could be queried more conveniently, and hidden from view otherwise. Do the source data reside in a range, or are they in an official Excel table? This last question is somewhat nuanced: if you select a cell in the data source table, do you see the "Table Design" menu item appear in the menu bar at the top of the screen, or does nothing happen? You can experiment to see the difference: enter a few bits of information somewhere in touching cells, click on one of them, hit Ctrl-t to open the table dialog box, confirm the range shown covers the data in your table, and click whether your table has headings, then OK...you've made an official table, which can be named. This has advantages for your application, as a reference to the table/table headings will consider all of the data, so we wouldn't need to take extra steps to ensure that any range references covered all relevant data. Here is an example of the same information, table at left, range at right.

Book1
	A	B	C	D	E	F	G
1	Col1	Col2	Col3		Col1	Col2	Col3
2	1	2	3		1	2	3
3	a	b	c		a	b	c
Sheet1

I'm confused by your description of the functionality. You mention returning a result to the next row in column A and then, upon hitting return, the results populate to the right. Do you want a single query driven by the B4:E4 or F5 inputs that generates all desired results for only those inputs, and then another query using the same input cells generates an entirely different set of results?...and those two sets of results are dynamic---they appear only when the appropriate search filter criteria are present in the input cells. Or are you interested in running several different queries and having all of the results appended to each other?...so earlier results are still visible, even though the search filter criteria used to generate them has been overwritten? A formula-based solution can be used for the first scenario, but not the second.

 

[Marshkevuk]

Welcome to the MrExcel board! A quick comment about your profile: thank you for using the Excel version field...the specific version(s) of interest help others determine an appropriate solution, as newer versions of Excel (365/2021) have enhanced functions that are not available in older versions, and the approaches taken for solutions can be dramatically different. To that end, I would recommend that you revise your profile to show only the version(s) of Excel being used actively.

Regarding your request, how would we know which tabs should be searched for the filtering terms in B4:E4? And what is the Ref #?...is it an indexing number that is unique to each record across all data source worksheets? What is the format/structure of the data sources on the other worksheets?...are they all the same? Could they be combined into a single large table for more convenient referencing? This could be done behind the scenes and not actively maintained, it would serve only as a way to create a single, large source table that could be queried more conveniently, and hidden from view otherwise. Do the source data reside in a range, or are they in an official Excel table? This last question is somewhat nuanced: if you select a cell in the data source table, do you see the "Table Design" menu item appear in the menu bar at the top of the screen, or does nothing happen? You can experiment to see the difference: enter a few bits of information somewhere in touching cells, click on one of them, hit Ctrl-t to open the table dialog box, confirm the range shown covers the data in your table, and click whether your table has headings, then OK...you've made an official table, which can be named. This has advantages for your application, as a reference to the table/table headings will consider all of the data, so we wouldn't need to take extra steps to ensure that any range references covered all relevant data. Here is an example of the same information, table at left, range at right.

Book1
	A	B	C	D	E	F	G
1	Col1	Col2	Col3		Col1	Col2	Col3
2	1	2	3		1	2	3
3	a	b	c		a	b	c
Sheet1

I'm confused by your description of the functionality. You mention returning a result to the next row in column A and then, upon hitting return, the results populate to the right. Do you want a single query driven by the B4:E4 or F5 inputs that generates all desired results for only those inputs, and then another query using the same input cells generates an entirely different set of results?...and those two sets of results are dynamic---they appear only when the appropriate search filter criteria are present in the input cells. Or are you interested in running several different queries and having all of the results appended to each other?...so earlier results are still visible, even though the search filter criteria used to generate them has been overwritten? A formula-based solution can be used for the first scenario, but not the second.

Hi KRice,

Thanks for your reply and welcome. I'll try to explain things better:

• I selected all versions of Excel because I need a formula that will work across all Excel platforms, as I have no idea what version users might be using. I have altered that now to 2011 because i assume that if it works with that then the formula will work with newer platforms and i would assume there's not people on older versions now?
• No tabs need to be searched. the indexing ref' number is unique to each record. Selecting the pertinent information from any of the drop downs contained in B4:E4 will return the same reference # in B5:E5.
• The information from the individual tabs are ranges rather than a table and cannot be combined into one tab, or more accurately, it doesn't need to be and would be problematic for various reasons to say the least.
• My comment with regards to cell population are purely based on what I do at present which is to enter the reference #, when known, into each cell (A10:A50) separately and when I either select the next cell down or hit return and the relevant information populates in the adjacent cells using a version of this formula that I cobbled together some help: (cell B10 example) =IF([@[Ref]]<>"",IFERROR(VLOOKUP([@[Ref]],Names_DOB,3,FALSE),VLOOKUP([@[Ref]],Town_County,3,FALSE)),"").
• So, really all I am looking for is a formula that I can put into A10:A50 that will grab the reference # from B5:E5 or F5 and put it in the next available empty cell in column A.
• In my head the process goes something like:
  • Perform search, reference number populates in pertinent cell B5:E5 (or enter ref' # in F5).
  • Hit enter, ref' # populates in next available cell (A10:A50)
  • Hit enter, relevant info' populates across B10:G10.
  • Then repeat the process Which I believe is the first scenario you mentioned.

I apologise for my lack or expertise and I realise that this is probably frustrating to you professionals, but i hope you can make some sense of my ramblings.

Cheers MK.

 

[KRice]

In your first post, you mentioned the input cells were needed to look up a reference number, yet your last post says that no tabs need to be searched. Where do the reference numbers come from? There must be some type of search to return the reference number, as well as another search to return the information in B10:G10 (and down). The formula you've described appears to do the latter.

Without knowing more about the source data sheets (their names, structures, contents, etc.), I would only be guessing...and I'm not sure a formula-based solution will suit your needs. Based on your last description of the process desired, you want prior results to remain in the results table, and you want new queries/search results appended to it. You'll need a different approach to do that: either Power Query or a VBA solution, either of which could retain the current state of your results table while adding new results to it. It would be very helpful to show two or three iterations of the process you've described, even if you manually annotate the desired results from the 1st query, then the 2nd, then the 3rd. The problem with Power Query is that other users would need to learn how to refresh the query, so you might be better off with a VBA solution that automatically runs when your filter inputs are changed.

 

[Marshkevuk]

In your first post, you mentioned the input cells were needed to look up a reference number, yet your last post says that no tabs need to be searched. Where do the reference numbers come from? There must be some type of search to return the reference number, as well as another search to return the information in B10:G10 (and down). The formula you've described appears to do the latter.

Without knowing more about the source data sheets (their names, structures, contents, etc.), I would only be guessing...and I'm not sure a formula-based solution will suit your needs. Based on your last description of the process desired, you want prior results to remain in the results table, and you want new queries/search results appended to it. You'll need a different approach to do that: either Power Query or a VBA solution, either of which could retain the current state of your results table while adding new results to it. It would be very helpful to show two or three iterations of the process you've described, even if you manually annotate the desired results from the 1st query, then the 2nd, then the 3rd. The problem with Power Query is that other users would need to learn how to refresh the query, so you might be better off with a VBA solution that automatically runs when your filter inputs are changed.

Thanks Kirk, I appreciate the feedback, I'm sorry that I can't explain it any clearer.

Thanks for taking the time anyway

MK.