Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

**SteynfaardtD** · 01-04-2019, 09:07 AM

Hi Everyone

I hope that someone can help me, I know there is Excel masters here and I know that Excel is capable of this.

I have two sheets that i work on, but both on one workbook. I Need to use my Sheet one as a input generator ( Not sure if I'm calling it the right thing ).

Basically I need to have on my sheet one A1 as a reference to where the data must be placed on Sheet 2. I thought of IFERROR and VLOOKUP, But cant seem to get the right things together.

I will attach two type of files, One being my template sheet for what I want to do and another One where i saw the idee, "Team Review Template" Is where i saw the generate review button that generates data of an entered employee name.

One " Template " I would like to use Sheet 1 A1 as a reference to add data to Sheet 2 in different cells. I Hope my explanation is well put together.
On " Template " Sheet1 The blue cells is the data i want to use for Lookup reference in Sheet2. Then when I insert data in sheet 1 it should populate it to sheet 2 as values so that it can be edited over and over.

Then when I " Submit Data " On sheet one it should paste the values over. But only when I use the " Button Control "
So if I add data in Sheet 1 A1 it should Lookup on Sheet 2 For the Line of data, Then if I enter data into Sheet 1 B1 it should be able to go D2 as value only. and Sheet1 C1 to Sheet 2 E1 and so on..

But I want to be able to over right the data every time.

Basically I run Maintenance on different sites and need to add the remarks to the list weekly, but want it to be a single input generator to prevent the list of getting messed up.

Hope That this is possible as i can not make my main list visible to my operators..

**Winon** · 01-04-2019, 01:19 PM

Goeie naand SteynfaardtD,

I may have an easier solution for you, if you don't mind employing a third hidden Sheet, if that is o.k. with you?

Regards.

**Winon** · 01-04-2019, 03:45 PM

Slaap jy al, SteynfaardtD?,

Since your Data Headers are the same for the two sheets, and Columns B and C are Hidden, We can do it quick with the Code below.

Please Login or Register  to view this content.

Check out the attached sample Workbook.

Lekker slaap!

**SteynfaardtD** · 01-06-2019, 09:59 AM

I Wont mind no, I can always just hide it.

**SteynfaardtD** · 01-06-2019, 10:07 AM

Ek kry nie jou file oop gemaak nie.?

**SteynfaardtD** · 01-06-2019, 10:16 AM

Brilliant Thanks, But this now only works if both sheets contain all the data. This basically only copies the sheet over.

Lets say i have 9000 sites in total which is my Sheet 2, but for the day we attended to 15 sites. I would like to make my sheet one Column A a reference cell so that i can only insert those 15 sites in sheet 1 and then it will lookup the sites in main list and only update those sites accordant to what i have entered in Sheet 1.

So basically sheet one will never contain all data, it will only be used to submit data into sheet 2

**SteynfaardtD** · 01-06-2019, 12:28 PM

Okay So let me retry this.

Attached you will find a full sample of what I would like to do.
My previous request was not really explained correctly and therefore I need something else.

I Need to have a Workbook that will work as a submit and retrieve form.

So basically I hope it can work as follow

Sheet 1 will be the working platform which is linked to Sheet 2,

On Both Sheets my Site ID will always be my Reference, So on Sheet 1 I will have a "Drop Down" or Input Cell that will determine what Data
must be shown or CREATED. Once I enter a Site ID I want to click the Retrieve Data Button and it must Find the DATA in Sheet 2, After all Data is
retrieved I want to edit it and Submit it by clicking the " SUBMIT DATA " button.

If no Data was found after Clicking the " RETRIEVE DATA " Button, Then it must add a new line to Sheet 2.

The Retrieving Data bit I can do with Vlookup, But the Submitting part is VBA and I don't understand VBA.

Please if anyone can assist me in that I would really appreciate it.

**Winon** · 01-06-2019, 01:35 PM

Please also attach your "Maintenance Progress Update" Workbook as well.
Should your Site ID Dropdown not refer to the above Workbook, instead of Sheet 2 of the Template Workbook?
The Retrieve Data Button looks at the "Maintenance Progress Update" Workbook, am I correct?

**SteynfaardtD** · 01-06-2019, 03:29 PM

Unfortunately i can not share that file, All I need is the formulas to submit one cell to another.
Attached is the file that I managed to get the Vlookup to work.

If my Template file works then I can implement it to my real file.

**Winon** · 01-06-2019, 04:24 PM

Unfortunately i can not share that file, All I need is the formulas to submit one cell to another.

Without that file, I am afraid nobody would be able to assist. Create a Backup File of that Workbook, and replace all confidential info with any Bla-Bla, and attached it here. Once we have resolved your issue, you can replace the Bla-Bla from your Backup File.

Attach a sample workbook (not a picture or pasted copy).
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**SteynfaardtD** · 01-06-2019, 04:29 PM

Lets say my template file is my only file.. I need to get whatever i retrieved and changed on sheet one to be submitted to sheet 2.

I don't need to do anything from workbook to workbook. It all stays in between sheets.
I have tried google on how to do copy and paste values if criteria is met in vba and all just shows .values but nothing that confirms to which line it must paste values

**Winon** · 01-06-2019, 04:43 PM

Lets say my template file is my only file.. I need to get whatever i retrieved and changed on sheet one to be submitted to sheet 2.

The statement above is somewhat contradictory as it will remove all your formulae on Sheet2. Without the other Workbook, I shall unfortunately not be able to further assist you. There is also no indication on Sheet1 of how the Cells are populated.

Edit: I notice that you have code to populate Sheet 1 from values in Sheet 2. Then you want to Populate Sheet 2 with the Values from Sheet 1.

To me, it does not make sense.

**SteynfaardtD** · 01-06-2019, 05:27 PM

My Main goal for this workbook is productive maintenance. So on each site visit the previous detail is important.

I've used

Please Login or Register  to view this content.

. This works only to specified cells,

All i need is a formula to first look for the site ID in sheet 2 before submitting the data.

I have a master list of 11300 site and that is why I need to get a way to have a single input page that will populate my full list on daily basis.
The reason for retrieving data before submit is to keep previous data and just add on to it like +1 or change values ext

Please Use attached file for sample. there will be no Formulas on sheet 2 or one. I have a different macro that pulls data from other workbooks if index in not met.

All i Need is a formula to take my Site ID in sheet1 as reference for vlookup to sheet to, Basically reverse the normal Vlookup Formula

**SteynfaardtD** · 01-06-2019, 05:30 PM

Originally Posted by SteynfaardtD

My Main goal for this workbook is productive maintenance. So on each site visit the previous detail is important.

I've used

Sheet2.Range("D2").Value = Sheet1.Range("C8").Value to get data from sheet1 to sheet 2. This works only to specified cells,

All i need is a formula to first look for the site ID in sheet 2 before submitting the data.

I have a master list of 11300 site and that is why I need to get a way to have a single input page that will populate my full list on daily basis.
The reason for retrieving data before submit is to keep previous data and just add on to it like +1 or change values ext

Please Use attached file for sample. there will be no Formulas on sheet 2 or one. I have a different macro that pulls data from other workbooks if index in not met.

All i Need is a formula to take my Site ID in sheet1 as reference for vlookup to sheet to, Basically reverse the normal Vlookup Formula

I Use this to get my data from Sheet 2

Please Login or Register  to view this content.

**SteynfaardtD** · 01-07-2019, 02:28 AM

Guys I have managed to fiqure out how to do what I wanted to do..... I am soooooo Super hyped about it..

I used the following to determine where to place data

Please Login or Register  to view this content.

Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

LinkBack

Thread Tools

Rate This Thread

Display

Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Re: Using One sheet as a fill in table to populate data in Sheet 2 using A1 as Reference

Thread Information

Users Browsing this Thread

Similar Threads

need to fill a table from a data sheet by a reference no

How to fill a table with another sheet data

VBA - Search Sheet 1 for text, reference corresponding cell, populate Sheet 2?

[SOLVED] Need to populate a table with aggregated reference data from other sheet

[SOLVED] Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet data

As user fills out data sheet, then fill out a results sheet based on data sheet entry

Populate table with data based on another sheet

Bookmarks

Bookmarks

Posting Permissions