Automatic Enter

[l flipboi l]

Hello,

I wanna dedicate one cell for user input and copy that information to another cell...

Is there any way to point to another cell after the item is stored?

I've included the picture just to be clear

Thanks!

[ChemistB]

If your value is in C2 and you want it in H2, H3 and H4
in those cells enter =C2

Or in H2, =$C$2 and drag or copy it downward.
Does that work for you?

[l flipboi l]

If your value is in C2 and you want it in H2, H3 and H4
in those cells enter =C2

Or in H2, =$C$2 and drag or copy it downward.
Does that work for you?

Actually, I want it to display different numbers...

for instance, the value will be entered in C2

then it will be stored in H1

when I enter another (different) value in C2

then it will be stored in H2

[ChemistB]

Try this code in the worksheet (right click on the worksheet tab and "View code" and paste )

Please Login or Register  to view this content.

Does that work for you?

[l flipboi l]

Thank you!

[l flipboi l]

I was wondering, I'm trying to use 2 input fields...

how would i include another one? I want to use both C6 & C7 as the input field...

C6 will store values in H1,H2,H3 etc...
C7 will store values in I1, I2, I3 etc...

Thanks!

[teylyn]

try this

Please Login or Register  to view this content.

[l flipboi l]

Excellent! Thanks Teylyn

[l flipboi l]

Hello again!

Everything seems to be working fine, but I would like to know...is it possible to store only the first 2 numbers in H1 and H2

then loop back and overwrite H1 and H2.... so basically, all i want to do is have 2 cells dedicated to store user inputs.

C5 will be the input

and the number entered in C5 will be stored in H1 and H2 and once H1 and H2 is filled up, it will loop again and store that values in H1 and H2.

Thanks!

[teylyn]

Yes it's possible. But I'm not convinced it makes sense to do it. This does not sound like good spreadsheet design.

Can you explain what the purpose of this is?

[l flipboi l]

What i'm trying to do is displays the difference of H1 and H2, in lets say cell C1, the problem lies with the current code is that I have to manually keep changing the location to reference different areas each week. With the current code, for instance, week 1 I want to display the difference of H1 and H2. Now the following week, which is week 2, I would have to change it manually like so =(H2-H3)

Basically, every week we have to report numbers during our staff meeting and if we can someone just enter numbers every week in excelt, it would calculate the difference of last week from this week automatically.

The reason why I want to only decidate H1 and H2 is so that no matter what data I have there, it would always SUBTRACT from that location...

If you have any easier idea, please let me know... the only problem i'm running into with my idea is that the subtraction will not be consistent....

say H1 = 5 and H2 = 1

C1 = (H1 - H2) <--- static
C1 = 4

Now when I enter another number in C6 <-- User Input
The number will be stored like so...(assuming the loop)

H1= 3 and H2= 1

now instead of (last weeks-current week) i end up having (current week - last weeks) (H1-H2)

which is not what I want,

Can you recommend a better solution?

[l flipboi l]

I actually just thought of something and I don't know if it's possible...

What if we push the current data to another cell when another input is made....is that possible?

For example:

C5 = 1 Now 1 will be saved in H1
C5 = 2 Now 2 will be saved in H1 and H1's previous data will be saved in I1
C5 = 3 Now 3 will be saved in H1 and 2 will be pushed to I1
C5 = 4 Now 4 will be saved in H1 and 3 will be pushed to I1

So we are always overwriting I1

Let me know if this is possible. Thanks!

[teylyn]

If you have any easier idea, please let me know...

Indeed.

Create a sheet purely for data entry.

Label the colums according to your field values. Use one row for each data item.

This is your raw data.

If you want to report on that raw data, you can use any kind of Excel reporting tools, from formulas to pivot tables. You can filter by week, by day, by quarter. You can sort by hair color and shoe size. You can do almost anything.

The problem you are facing arises because you want to combine data entry and data reporting. This is bad spreadsheet design.

Follow the golden rule: Data entry on one sheet only. Reporting (adding, subtracting, averaging, whatever) on any sheet other than the Data Entry Sheet. Then (almost) anything is possible.

Re-design your spreadsheet. Any advice and solution involving macros to work around your spreadsheet design flaws will not solve your issue. With every twist and turn in the road you will face new problems.

Re-design your spreadsheet. Then you can do almost limitless reporting.

/Ends